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
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?
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