vba 如何将 Excel 安全地连接到 sql server 2008？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9037363/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I connect Excel securely to sql server 2008?
提问by l--''''''---------''''''''''''
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?
- how do i run a stored procedure from excel?
- 我如何确保从 excel 到 sql server 的连接是安全的？
- 如何从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 登录，但是如果他们实际上要输入指定数据标准，然后将标准发送到存储过程，然后从服务器获取数据，我可以使用这个解决方案吗？
回答by Mike Burton
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.
使用 Excel 原生界面与通过 VBA 或 VSTA 进行操作并没有什么不同，大多数开发人员在过去十年左右的时间里都是这样做的。这些方法与您的网络一样安全。我相信 Excel 原生功能也可以在没有无关引用的情况下工作，这对于维护目的来说特别好。主要区别似乎在于进行任意查询的能力。出于安全性和数据完整性的目的，这可能是最好的。
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环境Tools->References找到最新的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.
将该宏绑定到您的按钮，通过工作表或输入框设置您的值，然后开火。但我会重复我的警告：这样做会导致大量的支持需求。如果人们想提取自定义数据，那么他们就会非常挑剔。
回答by mshthn
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.
如果您需要更多详细信息，请告诉我。
