Is SQL Server 2016 Always Encrypted Right For Me? (1 Viewer)

bitflipper

New member
Local time
Today, 10:09
Joined
Mar 14, 2017
Messages
5
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:

Code:
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?
 
Last edited:

HiTechCoach

Well-known member
Local time
Today, 09:09
Joined
Mar 6, 2006
Messages
4,357
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

New member
Local time
Today, 10:09
Joined
Mar 14, 2017
Messages
5
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?
 
Last edited:

HiTechCoach

Well-known member
Local time
Today, 09:09
Joined
Mar 6, 2006
Messages
4,357
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

New member
Local time
Today, 10:09
Joined
Mar 14, 2017
Messages
5
I will look into Access 2016 support for Always Encrypted. Thank you.
 

HiTechCoach

Well-known member
Local time
Today, 09:09
Joined
Mar 6, 2006
Messages
4,357
Thanks for the update.

Please let us know what you find out.
 

bitflipper

New member
Local time
Today, 10:09
Joined
Mar 14, 2017
Messages
5
Rewriting my function to use ADO rather than DAO solved the problem.

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

Code:
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

New member
Local time
Today, 10:09
Joined
Mar 14, 2017
Messages
5
The resolution to my problem was to convert my function from DAO to ADO. Hope the following code helps someone else down the road:

Code:
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

Nifty Access Guy
Staff member
Local time
Today, 14:09
Joined
Jul 9, 2003
Messages
16,244
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.
 

Users who are viewing this thread

Top Bottom