7

My company wants to have approximately 100 of their sales people (distributed around the country) to be able to run stored procedures from excel and return the data onto the spreadsheet.

We have sql server 2008. i need to figure out a safe way to do this.

i will create a form in excel where the user can push a command button to refresh the data based on the parameters that they choose.

  1. how can i ensure that the connection from excel to the sql server is secure?
  2. how do i run a stored procedure from excel?

i found this to be very good information: http://office.microsoft.com/en-us/excel-help/connect-to-import-sql-server-data-HA010217956.aspx

Windows Authentication Select this option to use the Windows user name and password of the current user. This is the most secure method, but it can affect performance when many users are connected to the server.

however, i would like your input on this.

yes, the sales reps do have windows logins, but can i use this solution if they will actually be entering specifying the data criteria, then sending the criteria over into the stored procedure and then getting the data from the server?

Improve this question

2 Answers 2

Reset to default
13

The words "securely", "exposing", "outside" and "database" do not belong together.

Under no circumstance should you ever expose a SQL server to the internet. It's a very bad idea.

So your question really should be: How do we give access to our SQL server to remote locations?

The answer: VPN. The remote users should establish a VPN connection to your network. From there you can give them the appropriate access to the database.

Improve this answer
9
  • thank you so much for your response. my boss wants me to expose the IP with a port. is this not the right thing to do? can we somehow get the user into the server through windows authentication and NOT exposing the IP?
    – Alex Gordon
    Commented Jan 27, 2012 at 17:44
  • 3
    No. Again, bad idea. Port scanners will locate the server then you'll have a fairly large number of scripts attacking that machine day and night. Never mind the fact that worms like SQL Slammer spread because companies engaged in this practice.
    – ChrisLively
    Commented Jan 27, 2012 at 17:46
  • 2
    SSL only encrypts the connection between the client and the server. It makes no representation that the client is in fact allowed to connect to your server at all. Regarding websites, people basically pray that the authentication mechanism is good enough to prevent brute force attacks. However, with database servers people rarely turn on the feature to audit account logins and even when they do they rarely bother reading the logs until after a problem has occurred. Even if you read the logs, you'll just spend days trying to block most of the internet from getting to it. Trust me: VPN.
    – ChrisLively
    Commented Jan 27, 2012 at 17:48
  • 4
    The exact method of connection that your clients would use is immaterial. Just because you speak to the database using an ODBC connection doesn't mean someone else will. Also, from the database servers point of view it doesn't care if the client used ODBC, .Net providers, or even got down to the bare metal by hand constructing the tcp packets. The problem isn't how the Client connects, the problem is purely limiting who can even see the machine to begin with.
    – ChrisLively
    Commented Jan 27, 2012 at 17:53
  • 2
    A web app is typically better for the simple reason that if they do get in, they are limited to perform only the actions you allow them to. Whereas going straight to a database you have to be a lot more controlling on how you setup security.. which most people don't know how to do.
    – ChrisLively
    Commented Jan 27, 2012 at 19:19
7

It sounds from your description like this Excel sheet will be used as a sort of interactive report. If that's the case, I encourage you to try out Reporting Services. It's included with every edition of SQL Server, ever since 2005. This way, accessing the report only requires connecting to the report server with a web browser, rather than going directly to the database server. You can also do fairly sophisticated reports without having to do all the crazy VBA coding you'd need to with Excel (trust me, I've been there). You can export the reports to Excel files, and it usually does a pretty good job with the formatting.

I'd still recommend keeping the report server behind a VPN, however.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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