Подключение к базе данных Microsoft SQL через VBA (ADODB) с наименьшим риском нанести вред базе данных

Question

В настоящее время я ищу способ подключения к базе данных Microsoft SQL Server через VBA (ADODB) с акцентом на минимальный риск нанесения вреда, блокирования и изменения структуры базы данных. Для этого доступ только для чтения.

Моя попытка заключается в следующем:

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=IP\Database;Initial Catalog=Databasename;Trusted_connection=yes;"

Я использовал ручную обработку ошибок, чтобы гарантировать, что набор записей и база данных закрыты, что бы ни случилось. Через параметры набора записей я определяю доступ только для чтения.

Существуют ли другие механизмы, обеспечивающие целостность базы данных?

С наилучшими пожеланиями

Answer 1

На мой взгляд, в Excel нет разумной безопасности. Вся безопасность должна находиться на сервере. Если вы хотите предотвратить случайные или злонамеренные изменения в базе данных, тогда база данных на сервере должна быть доступна только для чтения, или все пользователи должны иметь доступ только для чтения к серверу SQL. Кроме того, вы можете реализовать трассировки на сервере, SQL-аудит C2 или использовать расширенные свойства. Тем не менее, все это на стороне сервера SQL. Вещи, которые вы можете делать на стороне клиента (например, в данном случае Excel), являются только функциями поддержки. И поэтому вопрос (для меня), какие функции поддержки я могу реализовать в Excel, чтобы обеспечить безопасность сервера SQL. Вот некоторые из вещей, которые я делаю:

(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, если они существуют). Таким образом, они не работают для меня. Но вышесказанное сработало довольно хорошо для меня и компаний, в которых я работал последние пару лет.

Дайте мне знать, если это тот ответ, который вы искали.