Forum Discussion
Access and SQL Server with Column Encryption
I've been asked to turn on column encryption on our Sql Server. We use Access 2019 as the front-end connected to SQL tables. The forms are bound to the tables. I am having a heck of time getting this to work. So far I have set-up column encryption, the proper keys, certifcates and the connection string to use column encryption. I can view the encrypted columns, unencrypted in Access, but am unable to sort, update or run any queries based on the encrypted column.
"....The encryption schema for the columns/variables is (encryption_type = "DETERMINISTIC", encryption_algorithm_name = "AEAD_AES_256_CBC_HMAC_SHA_@%^", column_encryption_key_name = 'CEK_Auto1", column_encryption_key_database_name = 'TEST') and the expresssion near line "1" expects it to be (encryption_type = 'PLAINTEXT') (or weaker) (#33299)"
- At the very least, you need to make sure you're using the latest ODBC driver.
https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver16- Here is the connection string used, tried versions 17 and 18
"Description=MFS DB SQL SRV 2016;
DRIVER=ODBC Driver 18 for SQL Server;
SERVER=xxx.xxx.x.xx,1450;UID=xxx;PWD=xxx;
Trusted_Connection=No;
APP=Microsoft® Windows® Operating System;
DATABASE=TEST;
Encrypt=Yes;
TrustServerCertificate=Yes;
ColumnEncryption=Enabled;
