问题描述
目前，我正在寻找通过VBA（ADODB）连接到Microsoft SQL Server数据库的方法，其重点是损害，阻止和更改数据库结构的最小风险。因此访问是只读的。我的考试如下：
设置DBConn =新的ADODB .Connection
设置TmpRecset =新记录集
DBConn.ConnectionString = pConnStr
DBConn.Open
错误GoTo TermConnection
使用TmpRecset
.ActiveConnection = DBConn
.Source = pQuery
.LockType = adLockReadOnly
.CursorType = adOpenForwardOnly
.CursorLocation = adUseClient
.Open
结束与
错误GoTo TermRecordset
//做一些有用的TmpRecset
错误GoTo 0
TermRecordset：
TmpRecset.Close
设置TmpRecset.ActiveConnection = Nothing
TermConnection：
DBConn.Close
设置DBConn = Nothing
End Sub
我使用以下连接字符串：
Provider = SQLOLEDB; Data Source = IP\Database; Initial Catalog = Databasename; Trusted_connection = yes;
我使用手动错误处理来确保记录集和数据库关闭，无论发生什么。通过记录集的参数，我定义了只读访问。
是否还有其他机制可以确保数据库的完整性得到确保？
最佳
在我看来，Excel中没有合理的安全性。所有安全性应驻留在服务器上。如果要防止对数据库的意外或恶意更改，则服务器上的数据库应为只读或所有用户应具有对SQL Server的只读访问权限。此外，您可以在服务器上实现跟踪，SQL审核C2或使用扩展属性。然而，所有这一切都在SQL服务器的一边。您可以在客户端方面执行的操作（如本例中为Excel）仅支持功能。所以问题是（对我来说）我可以在Excel中实现什么样的支持功能来确保SQL Server的安全。这里是我做的一些事情：
（1）使用全局变量将连接字符串动态化，或将该字符串存储在隐藏的表单上。然后可以在开发服务器和生产服务器之间自动切换。示例：
设置conRCServer =新建ADODB.Connection
conRCServer.ConnectionString =PROVIDER = SQLOLEDB;_
& DATA SOURCE =& Ref.Range（C2）。Value2& ; _
& INITIAL CATALOG =& Ref.Range（C4）。Value& ; _
& 集成安全= SSPI
错误GoTo SQL_ConnectionError
conRCServer.Open
错误GoTo 0
（2）有一个单独的错误处理程序连接到服务器和处理SQL语法错误。示例：
设置rstResult =新建ADODB.Recordset
strSQL =set nocount on;
strSQL = strSQL& / *＃& ActiveWorkbook.Path& /& ActiveWorkbook.Name& {& WorksheetUsers.Name& } btnDownloadUserDataFromServer * /
strSQL = strSQL& select v.LastName，
strSQL = strSQL& v.FirstName
strSQL = strSQL& 从vuser作为v
strSQL = strSQL& 由v.LastName命令，v.FirstName
rstResult.ActiveConnection = conRCServer
错误转到SQL_StatementError
rstResult.Open strSQL
错误GoTo 0
这是SQL语法的错误处理程序，在上面的例子中是一个可能的SQL连接错误的单独处理程序。 p>
（3）在SQL语法中包含自我标识。正如你在上面的例子中可以看到的，我也让服务器知道哪个文件，文件中的哪个工作表以及用户调用的工作表中的哪个功能来执行这个语句。如果您在服务器上捕获这些数据，那么您可以看到谁在编写自己的查询，谁正在使用您的标准文件以及使用哪些功能（及其各自的影响）。
（4）如果发生错误，您可能需要考虑编写自动错误电子邮件。示例：
SQL_ConnectionError：
Y = MsgBox（无法连接到服务器，请确保您有工作互联网连接。& _
还要确保连接到公司网络并被允许访问服务器。& _
你要我准备一个错误的电子邮件吗？ ，52，连接到服务器...的问题）
如果Y = 6然后
设置OutApp = CreateObject（Outlook.Application）
设置OutMail = OutApp.CreateItem（0 ）
With OutMail
.to = Ref.Range（C7）。Value2
.CC = Ref.Range（C8）。Value2
.Subject =问题连接到数据库'& Ref.Range（C4）。Value& 在服务器上& Ref.Range（C2）。Value& '
.HTMLBody =< span style =font-size：10px> ---自动生成的错误电子邮件---& _
< / span>< br>< br>来自文件& _
< span style =color：blue> &安培; ActiveWorkbook.Name& _
< / span>'，并保存在'< span style =color：blue> &安培; _
ActiveWorkbook.Path& < /跨度>< BR>中&安培; _
Excel无法建立与服务器的连接。 &安培; <峰; br><峰; br> 中&安培; _
计算机名称：< span style =color：green;> &安培; Environ（COMPUTERNAME）& < /跨度><峰; br> 中&安培; _
登录为：< span style =color：green;> &安培; Environ（USERDOMAIN）& /& Environ（USERNAME）& < /跨度><峰; br> 中&安培; _
域服务器：< span style =color：green;> &安培; Environ（LOGONSERVER）& < /跨度><峰; br> 中&安培; _
用户DNS域：< span style =color：green;> &安培; Environ（USERDNSDOMAIN）& < /跨度><峰; br> 中&安培; _
操作系统：< span style =color：green;> &安培; Environ（OS）& < /跨度><峰; br> 中&安培; _
Excel版本：< span style =color：green;> &安培; Application.Version& < /跨度><峰; br> 中&安培; _
< br>< span style =font-size：10px>< br> &安培; _
这个错误的可能原因包括：（1）没有Internet连接，（2）没有工作的VPN连接到公司网络，& _
（3）服务器当前处于脱机状态，（4）DNS认证问题，（5）...其他原因...，& _
（6）用户没有连接到服务器底层数据库所需的权限。 &安培; _
< br>< br> ---自动生成的错误电子邮件---
.Display
结束与
设置OutMail =没有
设置OutApp =没有
结束如果
退出Sub
我还调查了更改连接参数的方法。但是在大多数企业环境中，我已经为这些连接参数所做的工作已被覆盖（例如ADODB.Connection.CommandTimeout被服务器的每个用户的SQL超时或Windows公司预设（如果存在）覆盖）。所以，他们没有为我工作。但是，对于我和过去几年我所从事的公司来说，上述工作相当不错。
让我知道，如果这是你一直以来的答案寻找。
im currently looking for a way to connect to a Microsoft SQL Server Database via VBA (ADODB) with the focus on a minimal risk in harming, block and change the structure of the database. Therefor the access is readonly.
My attemp is the following:
Set DBConn = New ADODB.Connection
Set TmpRecset = New Recordset
DBConn.ConnectionString = pConnStr
DBConn.Open
On Error GoTo TermConnection
With TmpRecset
.ActiveConnection = DBConn
.Source = pQuery
.LockType = adLockReadOnly
.CursorType = adOpenForwardOnly
.CursorLocation = adUseClient
.Open
End With
On Error GoTo TermRecordset
//Doing something useful with TmpRecset
On Error GoTo 0
TermRecordset:
TmpRecset.Close
Set TmpRecset.ActiveConnection = Nothing
TermConnection:
DBConn.Close
Set DBConn = Nothing
End Sub
And I'm using the following connection string:
"Provider=SQLOLEDB;Data Source=IP\Database;Initial Catalog=Databasename;Trusted_connection=yes;"
I used the manual error handling to ensure, that the recordset and the database is closed whatever happens. Via the parameters of the recordset I define the readonly access.
Are there some other mechanisms to make sure, that the integrity of the Database will be ensured?
Best regards
In my opinion there is no reasonable security in Excel. All security should reside on the server. If you want to prevent accidental or malicious changes to the database then the database on the server should be read-only or all users should have read-only access to the SQL server. Furthermore, you can implement traces on the server, SQL audit C2, or make use of extended properties. Yet, all of this is on the side of the SQL server. The things you can do on the "client" side (such as Excel in this case) are only support functions. And so the question is (to me) what kind of support functions can I implement in Excel to ensure SQL server safety. Here are some of the things I do:
(1) Make the connection string dynamic using global variables or storing the string on a hidden sheet. Then you can automatically switch between development server and production server. Example:
Set conRCServer = New ADODB.Connection
conRCServer.ConnectionString = "PROVIDER=SQLOLEDB; " _
& "DATA SOURCE=" & Ref.Range("C2").Value2 & ";" _
& "INITIAL CATALOG=" & Ref.Range("C4").Value & ";" _
& "Integrated Security=SSPI "
On Error GoTo SQL_ConnectionError
conRCServer.Open
On Error GoTo 0
(2) Have a seperate error handler for connecting to the server and handling SQL syntax errors. Example:
Set rstResult = New ADODB.Recordset
strSQL = "set nocount on; "
strSQL = strSQL & "/* #" & ActiveWorkbook.Path & "/" & ActiveWorkbook.Name & "{" & WorksheetUsers.Name & "}btnDownloadUserDataFromServer */"
strSQL = strSQL & "select v.LastName, "
strSQL = strSQL & " v.FirstName "
strSQL = strSQL & "from vUsers as v "
strSQL = strSQL & "order by v.LastName, v.FirstName "
rstResult.ActiveConnection = conRCServer
On Error GoTo SQL_StatementError
rstResult.Open strSQL
On Error GoTo 0
Here is an error handler for the SQL syntax and in the above example is a seperate handler for the possible SQL connection error.
(3) Incorporate self-identification within the SQL syntax. As you can see in the above example I am also letting the server know which file, which sheet (within the file) and which function within the sheet the user called to execute this statement. If you capture this data on the server with a trace then you can see who is writing their own queries, who is using your standard files and which functions are used (and their respective impact).
(4) If an error occurs you might want to consider writing automated error emails. Example:
SQL_ConnectionError:
Y = MsgBox("Cannot connect to the server. Please make sure that you have a working internet connection. " & _
"Also ensure that are connected to the corporate network and are allowed to access the server. " & _
"Do you want me to prepare an error-email?", 52, "Problems connecting to Server...")
If Y = 6 Then
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.to = Ref.Range("C7").Value2
.CC = Ref.Range("C8").Value2
.Subject = "Problems connecting to database '" & Ref.Range("C4").Value & "' on server '" & Ref.Range("C2").Value & "'"
.HTMLBody = "<span style=""font-size:10px"">---Automatically generated Error-Email---" & _
"</span><br><br>Error report from the file '" & _
"<span style=""color:blue"">" & ActiveWorkbook.Name & _
"</span>' located and saved on '<span style=""color:blue"">" & _
ActiveWorkbook.Path & "</span>'.<br>" & _
"Excel is not able to establish a connection to the server. Technical data to follow." & "<br><br>" & _
"Computer Name: <span style=""color:green;"">" & Environ("COMPUTERNAME") & "</span><br>" & _
"Logged in as: <span style=""color:green;"">" & Environ("USERDOMAIN") & "/" & Environ("USERNAME") & "</span><br>" & _
"Domain Server: <span style=""color:green;"">" & Environ("LOGONSERVER") & "</span><br>" & _
"User DNS Domain: <span style=""color:green;"">" & Environ("USERDNSDOMAIN") & "</span><br>" & _
"Operating System: <span style=""color:green;"">" & Environ("OS") & "</span><br>" & _
"Excel Version: <span style=""color:green;"">" & Application.Version & "</span><br>" & _
"<br><span style=""font-size:10px""><br>" & _
"Possible reasons for this error include: (1) no Internet connection, (2) no working VPN connection to the corporate network, " & _
"(3) the server is currently offline, (4) DNS authentication problems, (5) ... other reasons ..., " & _
"(6) the user does not have the required permission to connect to the underlying database on the server." & _
"<br><br>---Automatically generated Error-Email---"
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End If
Exit Sub
I also looked into your approach of changing the connection parameters. But in most corporate environments I have worked for these connection parameters have been overridden (for example ADODB.Connection.CommandTimeout is overridden by the server's SQL timeout per user or Windows corporate presets if they exist). So, they did not work for me. But the above worked rather well for me and the companies I worked for over the last couple of years.
Let me know if this is the kind of answer you've been looking for.
这篇关于通过VBA（ADODB）连接到Microsoft SQL数据库，危害数据库的风险最低的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！