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.
- how can i ensure that the connection from excel to the sql server is secure?
- 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?
Best Answer
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.