1

We have a SaaS application that generates a lot of operational data that our customers want to report on. We have a built-in business intelligence tool, but some of our customers want to use their existing tools, like Tableau or Cognos.

Some of them have asked us to let them connect to their SQL database directly over a VPN tunnel. That works, but it will be difficult to manage if we have more than a few.

Is there any standard way to let outside reporting tools access data without opening a direct network connection to the database? By "standard" I mean something that we can implement that will be usable by a lot of common BI and reporting tools.

2
  • So, you're essentially asking if your customers' reporting tools can connect to your database(s) "without opening a direct network connection to the database". I think the answer is kinda obvious.
    – mustaccio
    Aug 17 '16 at 12:33
  • I'm asking for a way to let them connect to the database securely without exposing it directly to the Internet. A VPN would work but is complex to manage. Is there a simpler way to tunnel SQL connections? Aug 17 '16 at 20:21
0

Do not permit ad hoc querying against the operational database, no matter if it is internally generated or external. The potential to lock data or saturate resources is too large. Client B is not going to forgive the SLA breach caused by client A submitting a malformed query then going for lunch.

Separating each customer's data from the others' is important, too. Even if the data isn't "sensitive" it is not professional and can lead to awkward conversations when A realises their outcomes are so much worse than B's. The question doesn't make clear if the source is multi-tenant.

One solution is to replicate / mirror / copy data from the operational DB to a second, separate, isolated DB. Use whatever tool your product supports. This may be an opportunity to do some transformation if required and supported by the tool. The lag between primary and secondary is to be negotiated with the customer in the light of license, set-up and support costs. Grant read-only access on this secondary to the client. Ideally the primary and secondary would be on separate boxes with a firewall between since the latter will be externally accessible. Be kind to yourself on the SLA you agree for the secondary.

If the client can accept a reporting lag on the order of hours you could simply ftp the data to them on a schedule.

Is there a better option than VPNs? I'm not a networking expert, but I'd say that unless you have a specific problem with using a VPN, go with it.

1
  • Yeah, that's pretty much what we're planning for the backend. But how do we let them connect to it? Is there a better option than VPNs? Aug 17 '16 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.