sql server语言 - 通过VBA (ADODB)连接到Microsoft sql数据库，对数据库的危害最低

Question

我目前正在寻找一种通过VBA (ADODB)连接到Microsoft SQL Server数据库的方法，重点是在损害，阻塞和改变数据库结构的最小风险。因此访问是只读的。

我的尝试如下:

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

我使用以下连接字符串:

"Provider=SQLOLEDB;Data Source=IPDatabase;Initial Catalog=Databasename;Trusted_connection=yes;"

我使用手动错误处理来确保无论发生什么，记录集和数据库都被关闭。通过记录集的参数，我定义了只读访问。

是否有其他机制来确保数据库的完整性?

Answer 1

我认为Excel没有合理的安全性。所有的安全性都应该驻留在服务器上。如果您希望防止对数据库的意外或恶意更改，那么服务器上的数据库应该是只读的，或者所有用户都应该对SQL服务器具有只读访问权限。此外，您可以在服务器上实现跟踪、SQL审计C2或使用扩展属性。然而，所有这些都是在SQL服务器的一边。你可以在"客户端"做的事情(比如在这种情况下的Excel)只是支持功能。所以问题是(对我来说)我可以在Excel中实现什么样的支持功能来确保SQL server的安全。以下是我做的一些事情:

(1)使用全局变量或将字符串存储在隐藏表中使连接字符串动态。然后，您可以在开发服务器和生产服务器之间自动切换。例子:

Dim conRCServer As ADODB.Connection
Dim rstResult As ADODB.Recordset
Dim strSQL As String
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)有一个单独的错误处理程序连接到服务器和处理SQL语法错误。例子:

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

这是一个SQL语法的错误处理程序，在上面的例子中是一个单独的处理程序，用于可能的SQL连接错误。

(3)在SQL语法中合并自我识别。正如您在上面的示例中看到的，我还让服务器知道用户调用了哪个文件、哪个表(在文件中)以及表中的哪个函数来执行该语句。如果您通过跟踪在服务器上捕获这些数据，那么您可以看到谁在编写自己的查询，谁在使用您的标准文件，以及使用了哪些函数(以及它们各自的影响)。

(4)如果发生错误，您可能需要考虑编写自动错误电子邮件。例子:

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

我也研究了你改变连接参数的方法。但是，在我工作过的大多数企业环境中，这些连接参数都被覆盖了(例如，ADODB.Connection.CommandTimeout被服务器的每用户SQL超时或Windows企业预设(如果存在的话)所覆盖)。所以，它们对我不起作用。在过去的几年里，以上这些对我和我工作过的公司来说都很有效。

让我知道这是否是你一直在寻找的答案。