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.
我的公司希望有大约100名销售人员(分布在全国各地)能够运行excel中的存储过程并将数据返回到电子表格中。
We have sql server 2008. i need to figure out a safe way to do this.
我们有sql server 2008。我需要找到一个安全的方法。
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.
我将在excel中创建一个表单，用户可以在其中按下命令按钮，根据他们选择的参数刷新数据。
- how can i ensure that the connection from excel to the sql server is secure?
- 如何确保从excel到sql服务器的连接是安全的?
- how do i run a stored procedure from excel?
- 如何从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
我发现这是一个非常好的信息: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.
Windows身份验证选择此选项以使用当前用户的Windows用户名和密码。这是最安全的方法，但是当许多用户连接到服务器时，它会影响性能。
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?
是的，销售代表确实有windows登录，但如果他们实际上要输入指定数据标准，然后将标准发送到存储过程，然后从服务器获取数据，我是否可以使用这个解决方案?
2 个解决方案
#1
3
Allowing users direct connections to your database is tricky. First off, you expose yourself to attack from without, as user accounts are compromised more frequently than well-isolated admin and service accounts. Having said that, the user account does need to be compromised to allow an attacker into the system, and you have good granularity of control built into SQL Server if every user has their own credentials.
让用户直接连接到数据库是很困难的。首先，您将自己暴露在来自外部的攻击中，因为与完全隔离的管理和服务帐户相比，用户帐户更容易受到攻击。话虽如此，为了让攻击者进入系统，用户帐户确实需要受到保护，而且如果每个用户都有自己的凭证，那么您在SQL Server中构建了良好的控制粒度。
Using the Excel-native interfaces isn't that different from doing it via VBA or VSTA, which is how most developers did it for the last decade or so. Those methods are about as secure as your network. I believe the Excel-native functionality works without extraneous references, as well, which is particularly nice for maintenance purposes. The main difference seems to be in the ability to do arbitrary queries. For security and data integrity purposes, this is probably for the best.
使用优秀的本地接口与通过VBA或VSTA来实现它并没有多大区别，这是大多数开发人员过去10年左右的做法。这些方法与您的网络一样安全。我认为优秀的本机功能可以在不需要额外引用的情况下工作，这对于维护来说是非常好的。主要的区别似乎在于执行任意查询的能力。出于安全性和数据完整性的目的，这可能是最好的。
Running a stored procedure is probably not a good idea, as you can get into massive support requirements if your users start needing(wanting) tweaks frequently. Can you make do with a view? Excel's inbuilt filtering and sorting abilities are pretty powerful. That would be my first approach.
运行一个存储过程可能不是一个好主意，因为如果您的用户开始频繁地需要调整，您可能会遇到大量的支持需求。你能将就一下风景吗?Excel的内置过滤和排序功能非常强大。这将是我的第一个方法。
There are several approaches depending on your needs:
根据您的需要，有几种方法:
1 - modify your schema to allow the database to tie data to individual users
1 -修改您的模式，允许数据库将数据绑定到单个用户
2 - move the access code into a VBA macro associated to the workbook. This is not recommended, but it will allow you to use ADO directly. Be SURE you have a solid security configuration on the database side if you do this, as an attacker who gains access to a user's account will be able to do anything that user can do.
2 -将访问代码移动到与工作簿关联的VBA宏中。这是不推荐的，但它将允许您直接使用ADO。如果这样做，请确保在数据库端有一个可靠的安全配置，因为获得用户帐户访问权的攻击者将能够做用户可以做的任何事情。
To go the VBA route, in the VBA environment Tools->References to find the latest Microsoft ADO version. The VBA code looks something like this:
要走VBA路线，在VBA环境工具—>中查找最新的Microsoft ADO版本。VBA代码如下所示:
Dim Connection as ADODB.Connection
Set Connection = new ADODB.Connection
Connection.Open"Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;"
Dim command As ADODB.command
command.CommandText = "exec sp_something"
Dim Parameters(2) As ADODB.Parameter
Set Parameters(1) = New ADODB.Parameter
Parameters(1).Name = "field_name"
Parameters(1).Type = adVarChar
Parameters(1).Size = 50
Set Parameters(2) = New ADODB.Parameter
Parameters(2).Name = "field_name_2"
Parameters(2).Type = adVarChar
Parameters(2).Size = 50
Dim i As Integer
For i = LBound(Parameters) To UBound(Parameters)
command.Parameters.Append Parameters(i)
Next i
Dim Records As ADODB.Recordset
Set Records = command.Execute
Tie that macro to your button, set up your values via the sheet or an input box, and fire away. But I'll repeat my warning: Going this way leads to massive support requirements. If people want to extract custom data, then they get very particular about it.
将这个宏绑定到你的按钮上，通过表单或输入框设置你的值，然后开火。但我将重申我的警告:这样做会导致大量的支持需求。如果人们想要提取自定义数据，那么他们就会对它非常挑剔。
#2
1
Instead of the article you linked, I'd rather use VBA script with reference to the ADO library and a normal connection string with a technical SQL user.
与您所链接的文章不同，我宁愿使用VBA脚本引用ADO库和与技术SQL用户的普通连接字符串。
Since the password would be in the connection string in this case, this technical user should have no other rights than executing your stored procedures.
由于在这种情况下，密码将位于连接字符串中，因此这个技术用户除了执行存储过程之外，应该没有其他权限。
Let me know if you need more details.
如果你需要更多的细节，请告诉我。
#1
3
Allowing users direct connections to your database is tricky. First off, you expose yourself to attack from without, as user accounts are compromised more frequently than well-isolated admin and service accounts. Having said that, the user account does need to be compromised to allow an attacker into the system, and you have good granularity of control built into SQL Server if every user has their own credentials.
让用户直接连接到数据库是很困难的。首先，您将自己暴露在来自外部的攻击中，因为与完全隔离的管理和服务帐户相比，用户帐户更容易受到攻击。话虽如此，为了让攻击者进入系统，用户帐户确实需要受到保护，而且如果每个用户都有自己的凭证，那么您在SQL Server中构建了良好的控制粒度。
Using the Excel-native interfaces isn't that different from doing it via VBA or VSTA, which is how most developers did it for the last decade or so. Those methods are about as secure as your network. I believe the Excel-native functionality works without extraneous references, as well, which is particularly nice for maintenance purposes. The main difference seems to be in the ability to do arbitrary queries. For security and data integrity purposes, this is probably for the best.
使用优秀的本地接口与通过VBA或VSTA来实现它并没有多大区别，这是大多数开发人员过去10年左右的做法。这些方法与您的网络一样安全。我认为优秀的本机功能可以在不需要额外引用的情况下工作，这对于维护来说是非常好的。主要的区别似乎在于执行任意查询的能力。出于安全性和数据完整性的目的，这可能是最好的。
Running a stored procedure is probably not a good idea, as you can get into massive support requirements if your users start needing(wanting) tweaks frequently. Can you make do with a view? Excel's inbuilt filtering and sorting abilities are pretty powerful. That would be my first approach.
运行一个存储过程可能不是一个好主意，因为如果您的用户开始频繁地需要调整，您可能会遇到大量的支持需求。你能将就一下风景吗?Excel的内置过滤和排序功能非常强大。这将是我的第一个方法。
There are several approaches depending on your needs:
根据您的需要，有几种方法:
1 - modify your schema to allow the database to tie data to individual users
1 -修改您的模式，允许数据库将数据绑定到单个用户
2 - move the access code into a VBA macro associated to the workbook. This is not recommended, but it will allow you to use ADO directly. Be SURE you have a solid security configuration on the database side if you do this, as an attacker who gains access to a user's account will be able to do anything that user can do.
2 -将访问代码移动到与工作簿关联的VBA宏中。这是不推荐的，但它将允许您直接使用ADO。如果这样做，请确保在数据库端有一个可靠的安全配置，因为获得用户帐户访问权的攻击者将能够做用户可以做的任何事情。
To go the VBA route, in the VBA environment Tools->References to find the latest Microsoft ADO version. The VBA code looks something like this:
要走VBA路线，在VBA环境工具—>中查找最新的Microsoft ADO版本。VBA代码如下所示:
Dim Connection as ADODB.Connection
Set Connection = new ADODB.Connection
Connection.Open"Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;"
Dim command As ADODB.command
command.CommandText = "exec sp_something"
Dim Parameters(2) As ADODB.Parameter
Set Parameters(1) = New ADODB.Parameter
Parameters(1).Name = "field_name"
Parameters(1).Type = adVarChar
Parameters(1).Size = 50
Set Parameters(2) = New ADODB.Parameter
Parameters(2).Name = "field_name_2"
Parameters(2).Type = adVarChar
Parameters(2).Size = 50
Dim i As Integer
For i = LBound(Parameters) To UBound(Parameters)
command.Parameters.Append Parameters(i)
Next i
Dim Records As ADODB.Recordset
Set Records = command.Execute
Tie that macro to your button, set up your values via the sheet or an input box, and fire away. But I'll repeat my warning: Going this way leads to massive support requirements. If people want to extract custom data, then they get very particular about it.
将这个宏绑定到你的按钮上，通过表单或输入框设置你的值，然后开火。但我将重申我的警告:这样做会导致大量的支持需求。如果人们想要提取自定义数据，那么他们就会对它非常挑剔。
#2
1
Instead of the article you linked, I'd rather use VBA script with reference to the ADO library and a normal connection string with a technical SQL user.
与您所链接的文章不同，我宁愿使用VBA脚本引用ADO库和与技术SQL用户的普通连接字符串。
Since the password would be in the connection string in this case, this technical user should have no other rights than executing your stored procedures.
由于在这种情况下，密码将位于连接字符串中，因此这个技术用户除了执行存储过程之外，应该没有其他权限。
Let me know if you need more details.
如果你需要更多的细节，请告诉我。