CODEWITHSUNDEEP
sql-serverweb-servicessql-server-2008-r2
Lukasz
Lukasz

Reputation: 599

Exposing SQL Data to clients

we have an internal SQL Server 2008R2 db that we'd like to expose (partially - only some tables) to our clients via Internet, so they can feed their Excel reports. What are our best options? How should we provide security (ie. Should we create another, staging DB server on DMZ for this?). As far as quantity to transfer, it's very small (< 100 recs).

Upvotes: 1

Views: 1504

Answers (2)

Andy Cheng
Andy Cheng

Reputation: 1167

If data is not needed real-time, the other alternative is use SSIS or SSRS to export excel file, and email to your clients.

Upvotes: 0

mellamokb
mellamokb

Reputation: 56779

Here would be one simple way to start with if they need live, real-time access:

  1. Create a custom SQL user account for web access, locked down with read-only access to the relevant tables or stored procedures.
  2. Create a REST web service that connects to the database using the SQL Account above. Expose methods for each set of data that can be retrieved.
  3. Make sure the web service runs over SSL (HTTPS) and requires username/password authentication - for example via BASIC auth with custom hard-coded account per client.

Then when the clients need to retrieve data, they can access a specific URL and receive data in CSV format or whatever is convenient for their reports. Also, REST web services are easily accessed via XMLHTTPObject if you have clients that are technically-savvy and can write VBA macros.

If the data is not needed real-time - for instance, if once a day is often enough, you could probably just generate .csv output files and host them somewhere the clients can download manually through their web browser. For instance, host on an FTP site or simple IIS website with BASIC authentication.

Upvotes: 2

Related Questions