UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Ms Access With Odbc & Always Encrypted, Access 2016    
 
   
mickelmusman
post Dec 23 2018, 12:55 AM
Post#1



Posts: 2
Joined: 23-December 18



Hi all,

Recently my workplace informed me that an Access database of ours which connects to an external SQL server via ODBC needs to have its data encrypted. Thanks GDPR!

Anyway, after looking through the various options for doing this I decided to use the MSSQL Always Encrypted feature as it looks to be fairly "transparent" to the client app side of things. However, like most things that promise too much this too appears to have done so - I have managed to successfully connect to the SQL database with encrypted columns via the ODBC driver V13, and can view the tables directly and fully decrypted in Access, however when I try to open forms that use these tables I keep getting the following message (taken from the ODBC trace file - but same message as appears in Access error dialog when I open the forms):

"Encryption scheme mismatch for columns/variables 'CONFIDENTIAL_COLUMN_NAME'. The encryption scheme for the columns/variables is (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'CONFIDENTIAL') and the expression near line '1' expects it to be (encryption_type = 'PLAINTEXT') (or weaker). (33299)"


My feeling is that this could be caused by attempting to use ORDER BY statements on the encrypted columns but I am not sure - is there something which commonly causes this error?

Also, is there any decent literature on connecting Access to SQL Always Encrypted with ODBC and the limitations of doing so - I cannot find much at all! For example, it appears that I can no longer us DLOOKUP for encrypted columns as the plaintext comparisons in the third argument to DLOOKUP are not being automatically parameterized and subsequently encrypted.

Hope this makes sense - and Merry Christmas! smile.gif


Kind regards,
Michael







Go to the top of the page
 
mickelmusman
post Jan 1 2019, 09:56 PM
Post#2



Posts: 2
Joined: 23-December 18



Hello all - just bumping this - is this new territory (Access + Always Encrypted ODBC) or is my question not clear?
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 09:50 PM