ODBC Connection to SQL Server Always Encrypted

[CPGDeveloper]

Hello~

I've been scouring the internet for answers and can't seem to find a solution. I have an MS Access Front End/Azure SQL Server Back End.

I've been tasked with encrypting certain columns, and that part is relatively simple as SSMS has a user friendly tool to do just that. I need to however decrypt these columns for the end user.

With VB.Net or C#, simply adding 'Column Encryption Setting = Enabled' to the connection string decrypts the columns in question, however I cannot seem to get any equivalent to work with MS Access. I am connecting to SQL Server using ODBC Driver 17 for SQL Server.

Anyone out there have experience with this issue? Is there a magical connection string that would work here?

 

[CPGDeveloper]

I somehow figured it out -- for anyone who might need this -- this is the connection string I used that decrypted an always decrypted column in SQL Server. For this to work, you will have to go through the steps to decrypt on SQL Server (SSMS has a fairly user friendly GUI Tool).

Here are two good articles explaining that process:
SQL Server 2016 - Always Encrypted – SQLServerCentral

SQL Server Encryption: Always Encrypted

Is 'Always Encrypted' SQL Server 2016's most widely important new feature? It is significant that 'Always Encrypted' in SQL Server is in all editions of SQL Server. Because of the increasing importance of encryption to data governance, it allows encryption for the sensitive application data for...

www.red-gate.com

Encrypting will produce a certificate that will be part of your windows user profile. As I understand it, that certificate will need to be present on any end user's user profile as well. Anyway, here's the text of the DSN that worked for me (this is with ODBC Driver 17 for SQL Server -- obviously replace 'MYDB' and 'MYSERVER' with your db and server names respectively):

[ODBC]
DRIVER=ODBC Driver 17 for SQL Server
ColumnEncryption=Enabled
TrustServerCertificate=Yes
DATABASE=MYDB
APP=Microsoft Office
Trusted_Connection=Yes
SERVER=MYSERVER

I hope this is helpful.