MS Access SQL Server始终加密参数化

Question

我正在评估SQL Server 2016是否始终加密会使用我支持的现有MS Access 2010应用程序。

这是我目前的障碍：

我的应用程序调用许多需要参数的SQL Server存储的过程。我使用以下功能进行这些调用：

Public Function ExecuteSPWithParamsQuery(poQDFStub As DAO.QueryDef, psParameterString As String) As DAO.Recordset
'-------------------------------------------------------------------------------------------------
' Purpose   : Execute an SQL pass-through query that calls a stored procedures requiring parameters.
'
' Params    : poQDFStub: pass through query with name of SPROC
'                : psParameterString : one or more parameters to be appended to poQDFStub
'
' Returns   : Dao.Recordset(dbOpenSnapshot)
'-------------------------------------------------------------------------------------------------
'
    If G_HANDLE_ERRORS Then On Error GoTo ErrorHandler
    Dim rstResult As DAO.Recordset
    'db interface
    Dim dbs As DAO.Database: Set dbs = CurrentDb
    Dim qdfResult As DAO.QueryDef: Set qdfResult = dbs.CreateQueryDef(vbNullString)
    'setup pass through
    With qdfResult
        .Connect = poQDFStub.Connect
        .SQL = poQDFStub.SQL & " " & psParameterString
        .ODBCTimeout = 0
        .ReturnsRecords = True
    End With
    'setup result
    Set rstResult = qdfResult.OpenRecordset(dbOpenSnapshot, dbSQLPassThrough + dbReadOnly + dbFailOnError)
ExitHere:
    'housekeeping
    On Error Resume Next
    'add cleanup here
    Set qdfResult = Nothing
    Set dbs = Nothing
    'exit protocol
    On Error GoTo 0
    Set ExecuteSPWithParamsQuery = rstResult
    Set rstResult = Nothing
    Exit Function
ErrorHandler:
    Err.Source = "SQLStoredProcedureHelper.ExecuteSPWithParamsQuery"
    HandleError
    Resume ExitHere
End Function

对此功能的调用现在将包含数据库中加密值的清晰文本版本的参数。

发生这种情况时，我会收到以下错误。

206 [Microsoft] [ODBC SQL Server驱动程序] [SQL Server]操作数类型clash：varchar不兼容>与nvarchar（255）加密（encryption_type ='确定性='centryistic' ='aead_aes_256_cbc_hmac_sha_256'，column_encryptim_key_name ='cek_auto1'， column_encryption_key_database_name ='sandbox'）

我已经对始终加密的参数化进行了一些研究。它需要两种技术之一

• .net
• SQL Server的ODBC 13.1

由于这是MS访问应用程序，因此.NET不适用。

我已经安装了ODBC 13.1，但是我猜我通过查询正在绕过参数化。

这是我的ODBC设置：

[ODBC]  
DRIVER=ODBC Driver 13 for SQL Server  
ColumnEncryption=Enabled  
TrustServerCertificate=No  
DATABASE=sandbox  
WSID=********  
APP=Microsoft Office 2010  
Trusted_Connection=Yes  
SERVER=*********  

关于我如何解决此问题或始终不适合我的应用程序的任何想法？

Answer 1

我尚未与访问直接使用，但是，似乎您的连接字符串可能未正确配置。请通过将以下内容附加到连接字符串

将以下内容设置为启用列列。

;ColumnEncryption=Enabled

本文解释了如何始终使用ODBC驱动程序加密。

Answer 2

我问题的解决方案是将我的功能从dao转换为ADO。希望以下代码能帮助其他人：

Public Function ExecuteSPWithParamsQueryADO(pSPROCName As String, ParamArray pParams() As Variant) As ADODB.RecordSet
'---------------------------------------------------------------------------------------------------------------------
' Purpose   : Executes an SQL pass-through query that requires parameters and returns a recordset.
'           : Utilizes ADO rather than DAO.
'
' Author    : M. Minneman
'
' Params    : pSPROCName - (required) name of SPROC to be executed
'           : pParams - (required) one or more parameters required by SPROC
'
' Returns   : ADODB.Recordset - ResultSet
'
' Contract  : Dependencies
'           :   G_HANDLE_ERRORS - Global Boolean Constant
'           :   ImprovedErrorHandler.HandleError - Global error handler
'           :   ADODB - Microsoft AcitveX Data Objects Library
'           :   ADO_CONNECT_STRING - valid connect string
'           :   GeneralFunctions.doCloseAndRelease - CCL Function for cleaning up DAO objects
'           :
'           : Assumptions (routine may still work, but produce unexpected results)
'           :   pParams has one index that is 0-based
'           :
'           : Pre Conditions (must be true before execution)
'           :   pSPROCName - SPROC exists in ADODB.Connection
'           :
'           : Post Conditions (should be true after execution)
'           :   ADODB.Recordset has 0 to many records
'           :
'---------------------------------------------------------------------------------------------------------------------
'
' Change Log:
'
' Date      By              Comment
' 03/17/17  M. Minneman     created
'
    If G_HANDLE_ERRORS Then On Error GoTo ErrorHandler
    Dim oReturn As ADODB.RecordSet
    'db interface
    Dim cnn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim prm As New ADODB.Parameter
    ' Set CommandText equal to the stored procedure name.
    cmd.CommandText = pSPROCName
    cmd.CommandType = adCmdStoredProc
    ' Connect to the data source.
    cnn.Open ADO_CONNECT_STRING
    'validate connection
    If cnn.State <> adStateOpen Then
        Err.Raise vbObjectError, , "ADO Connection failed to open"
    End If
    'assign connection to command
    cmd.ActiveConnection = cnn
    'automatically fill in parameter info from stored procedure.
    cmd.Parameters.Refresh
    'make sure expected parameters and given arguments are equal
    If cmd.Parameters.Count <> UBound(pParams) + 2 Then
        Err.Raise vbObjectError, , "SPROC '" & pSPROCName & "' expects " & cmd.Parameters.Count & " arguments. " & UBound(pParams) & " provided."
    End If
    'set the param values.
    Dim i As Integer
    For i = 1 To cmd.Parameters.Count - 1
        cmd(i) = pParams(i - 1)
    Next i
    'execute SPROC
    Set oReturn = cmd.Execute
ExitHere:
    'housekeeping - failure okay
    On Error Resume Next
    'add cleanup here
    GeneralFunctions.doCloseAndRelease _
        prm, _
        cmd, _
        cnn
    'everything else - failure not okay
    On Error GoTo 0
    Set ExecuteSPWithParamsQueryADO = oReturn
    Exit Function
ErrorHandler:
    'local action
    'add local actions here
    'default action
    Select Case Err.Source
    Case "CONSUMED"
        Call MsgBox("Operation failed!", vbExclamation, "Message")
    Case Else
        Err.Source = "SQLStoredProcedureHelper.ExecuteSPWithParamsQueryADO"
        Select Case Err.Number
        Case Else
            HandleError , , , True         'rethrow
        End Select
    End Select
    Resume ExitHere
    Resume
End Function