Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-14-2017, 07:45 AM   #1
bitflipper
Newly Registered User
 
Join Date: Mar 2017
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
bitflipper is on a distinguished road
Post 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.

Quote:
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 by bitflipper; 03-14-2017 at 05:08 PM. Reason: Improve title
bitflipper is offline   Reply With Quote
Old 03-14-2017, 09:12 PM   #2
HiTechCoach
Newly Registered User
 
HiTechCoach's Avatar
 
Join Date: Mar 2006
Location: Oklahoma City, OK
Posts: 4,254
Thanks: 8
Thanked 156 Times in 144 Posts
HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough
Re: Is SQL Server 2016 Always Encrypted Right For Me?

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.
__________________
Boyd Trimmell
Specializing in Accounting, CRM, and Business solutions
Microsoft Access MVP 2010-2015 , aka
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
HiTechCoach is offline   Reply With Quote
The Following User Says Thank You to HiTechCoach For This Useful Post:
Rx_ (03-16-2017)
Old 03-15-2017, 04:37 AM   #3
bitflipper
Newly Registered User
 
Join Date: Mar 2017
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
bitflipper is on a distinguished road
Re: Is SQL Server 2016 Always Encrypted Right For Me?

Quote:
Originally Posted by HiTechCoach View Post
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 by bitflipper; 03-15-2017 at 04:45 AM.
bitflipper is offline   Reply With Quote
Old 03-15-2017, 09:20 AM   #4
HiTechCoach
Newly Registered User
 
HiTechCoach's Avatar
 
Join Date: Mar 2006
Location: Oklahoma City, OK
Posts: 4,254
Thanks: 8
Thanked 156 Times in 144 Posts
HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough
Re: Is SQL Server 2016 Always Encrypted Right For Me?

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/micros...-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.
__________________
Boyd Trimmell
Specializing in Accounting, CRM, and Business solutions
Microsoft Access MVP 2010-2015 , aka
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
HiTechCoach is offline   Reply With Quote
Old 03-15-2017, 11:08 AM   #5
bitflipper
Newly Registered User
 
Join Date: Mar 2017
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
bitflipper is on a distinguished road
Re: Is SQL Server 2016 Always Encrypted Right For Me?

I will look into Access 2016 support for Always Encrypted. Thank you.
bitflipper is offline   Reply With Quote
Old 03-15-2017, 01:30 PM   #6
HiTechCoach
Newly Registered User
 
HiTechCoach's Avatar
 
Join Date: Mar 2006
Location: Oklahoma City, OK
Posts: 4,254
Thanks: 8
Thanked 156 Times in 144 Posts
HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough
Re: Is SQL Server 2016 Always Encrypted Right For Me?

Thanks for the update.

Please let us know what you find out.
__________________
Boyd Trimmell
Specializing in Accounting, CRM, and Business solutions
Microsoft Access MVP 2010-2015 , aka
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
HiTechCoach is offline   Reply With Quote
Reply

Tags
access , odbc , sql

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Deploying Access 2016 Front End, Access 2016 RossWaddell General 2 07-24-2016 05:43 AM
Encrypted backend database PaulA General 3 01-18-2016 05:32 PM
Question Encrypted Database problem richardwilder General 2 12-03-2014 05:08 AM
your password will not be encrypted popup Rocksteer General 0 05-01-2014 07:56 AM
Question Encrypted data sunilpandey General 9 04-16-2013 04:30 AM




All times are GMT -8. The time now is 05:59 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World