Is SQL Server 2016 Always Encrypted Right For Me?

[bitflipper]

SQL Server 2016 Always Encrypted Parameterization

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. Further, I assume that my strategy for calling Stored Procedures bypasses ODBC 13,1 parameterization.

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

 

[HiTechCoach]

With Access 2010 I find it best to use MS SQL Server 2008/R2 or older.. If you must use a newer SQL Server version then I would not use any new feature that is not compatible with MS SQL Server 2008/R2.

 

[bitflipper]

With Access 2010 I find it best to use MS SQL Server 2008/R2 or older.. If you must use a newer SQL Server version then I would not use any new feature that is not compatible with MS SQL Server 2008/R2.

If I upgrade to a newer version of MS Access do you think that will resolve this particular issue?

 

[HiTechCoach]

Access tends to lag behind in support of the new features in MS SQL Server.

I saw an article that Microsoft will be rolling out an update to Access 2016 that supports more of the features in MS SQL Server. ( see: http://www.eweek.com/database/microsoft-access-2016-makes-room-for-large-numbers )

If I did not have Office 2016, I would fire up a VM and installed the trial of Office 2016 and test it. If it works then you know it is worth the upgrade.

 

[bitflipper]

I will look into Access 2016 support for Always Encrypted. Thank you.

 

[HiTechCoach]

Thanks for the update.

Please let us know what you find out.

 

[bitflipper]

Rewriting my function to use ADO rather than DAO solved the problem.

Here is my code in hopes that it helps someone else:

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

 

[bitflipper]

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

 

[Uncle Gizmo]

Here is my code in hopes that it helps someone else:

The resolution to my problem

Due to a bug in the forum software these messages were "unapproved" (hidden) for some considerable time. I have just approved them. I hope no one has been inconvenience too much! The new forum software no longer has this bug, so this problem should not reoccur.