MS Access SQL Server Always Encrypted Parameterization

Question

I'm evaluating whether or not SQL Server 2016 Always Encrypted will work with an existing MS Access 2010 application that I support.

Here's my current roadblock:

My application calls many SQL Server stored procedures that require parameters. I use the following function to make those calls:

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

Calls to this function will now include parameters that are clear text versions of values encrypted in the database.

When this happens, I get the following error.

206 [Microsoft][ODBC SQL Server Driver][SQL Server] Operand type clash: varchar is incompatible > with nvarchar(255) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'sandbox')

I've done some investigation on Always Encrypted Parameterization. It requires one of two technologies

.NET
ODBC 13.1 For SQL Server

Since this is an MS Access application, .NET is not applicable.

I have ODBC 13.1 installed, but I'm guessing that my pass through query is bypassing parameterization.

Here are my ODBC settings:

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

Any ideas on how I can resolve this problem or is Always Encrypted not a fit for my application?

score 1 · Answer 1 · answered Mar 15 '17 at 18:28

I have not directly worked with access, but, Seems like your connection string might not have been configured properly. Please set ColumnEncryption to enabled by appending the following to your connection string

;ColumnEncryption=Enabled

This article explains how to use always encrypted with ODBC driver.

score 0 · Accepted Answer · answered Mar 29 '17 at 16:03

The resolution to my problem was to convert my function from DAO to ADO. Hope the following code helps someone else down the road:

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

MS Access SQL Server Always Encrypted Parameterization

2 Answers2