Solving an issue with Always Encrypted

I got called into a meeting with a few co-workers who were struggling with Always Encrypted. The encryption was working, the decryption in the tool… not so much. And that was the challenge.

For those who are not familiar with Always Encrypted: it’s a built-in technique in SQL Server where data gets encrypted in a random or deterministic manner with a certificate and an algorithm. Long story short, without the certificate it’s gobbledygook. If you want to read more, check out this link.

As mentioned, the encryption was working perfectly, no-one could read the data in a usable way in the application. The point was that the application should be able to do that. And so we took up the challenge to see where things went wrong.

First on the list were the certificate and user authorisation. The certificate was a major suspect, the authorisation just something to check. After some research, user authorisation seems to be connected to access to the certificate; if you have the certificate, you can read the data.


Let’s create a very simple demo to show the basics and explain the issue.

  CustName nvarchar(60), 
  SSN varchar(11), 
  Age int NULL

A simple table, let’s insert one row and see the result when selected:


Now, let’s encrypt the data. You can’t do that with T-SQL, you need to use either SSMS or PowerShell.

Encrypt columns
Select the columns to encrypt and don’t forget to choose the type
Either run now or save the script for later

We’ve got the PowerShell script. When run, it returns this.

weird angry letters

For some reason my installed SqlServer module is angry with me. It’s not the first time and I really need to clean things up. Luckily I can encrypt the columns through SSMS as well.

That’s more like it

What if we select the data now?

Ok, cool

SSMS and Always Encrypted

It’s nicely encrypted. To decrypt it, all you need to do in SSMS is check one simple box:

Check this one and you’re done

This way SSMS knows it needs to use the certificate to decode the data and done. But next up was the application. Because SSMS wasn’t the troublesome application. The app uses ODBC to connect to the database. In the ODBC driver, you need to set one check

ODBC and Always Encrypted

Check for CE

This should suffice in enabling the Column Encryption. Now the fun part was that it didn’t work the first time.

We changed two things. First, a new ODBC driver (17). Second, re-imported the certificate into the certificate store. After trying out the ODBC again, it all magically worked. In the App, we added the connection parameter Column Encryption Setting = Enabled to make sure the connection takes this setting with it.

Make sure you’re on a current version of the ODBC driver that matches your database. The default 10 and 11 versions you might find on your laptop or server will not do.

Final thoughts

As a security engineer, I love Always Encrypted. It’s an easy way to encrypt data and, as long as you’ve got a good certificate management, safe. If the certificate is shared among all users it completely loses it’s value. It’s supported on Azure as well where you can store the certificate in the key vault. Only users with the correct permissions can get to the key vault and to the certificate. It can be a bit of a hassle to get working for your app and please check with your vendor if they support it. If they don’t, you’ll need to make a choice.

Thanks for reading!

One thought on “Solving an issue with Always Encrypted

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s