ODBC Connection to SQL Server Always Encrypted

CPGDeveloper

Board Regular
Joined
Oct 8, 2008
Messages
157
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?
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

CPGDeveloper

Board Regular
Joined
Oct 8, 2008
Messages
157
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


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.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,219
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top