1

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).

Improve this question
1
  • You could create CSV files of your tables, which can be imported into Excel for your clients to use. Commented Apr 16, 2013 at 19:32

2 Answers 2

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks Yes, I was suspecting this would be the way (the Web Services). I do prefer a real-time solution. The only small problem is I haven't coded any web services yet. Oh well, time to hit Google. If you have any startup resources for me here, I'd appreciate it. One question: Why REST and not SOAP? (I'm vaguely aware that there are two types of WSs).
Depending on your relationship to clients, i.e., if they are highly trusted and a specific group that you have connections to, you could try a read-only database with remote Access. It depends on what level of surface area you want to provide to remote users. If it were me, I'd feel more comfortable with a web service since I can control what records they see and what backend database it actually connects to, etc.
@Lukasz: I recommended REST only because it would likely be easier to integrate with MS Excel.
Thanks everyone. REST it is.
0

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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.