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.

Demo

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

CREATE TABLE Customers 
(
  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.

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

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.

What if we select the data now?

SSMS and Always Encrypted

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

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

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!