Troubleshooting an Always Encrypted Certificate Error

The benefit of SQL Server Always Encrypted is to encrypt sensitive data in specified columns to prevent it from being seen by unauthorized users. It is a great feature, but there are some steps to consider besides the SQL setup side. What if you are one of the lucky (or unlucky) users who should have access to query that data unencrypted but get blocked by an error? Or, what if you’re setting up Always Encrypted and your application encounters issues decrypting data?

Let’s step through an example of one error you may come across that prevents authorized access.

None Shall Pass

When attempting to query data that is encrypted with Always Encrypted, you may get the following error message:

Failed to decrypt a column encryption key using key store provider: ‘MSSQL_CERTIFICATE_STORE’. The last 10 bytes of the encrypted column encryption key are: ‘XX-XX-XX-XX-XX-XX-XX-XX-XX-XX’.

The actual error message will have a mix of characters compared to all of the X’s seen above. The cause of this error is the lack of certificate access for the user.

Fixing Certificate Access

Let’s resolve our access issue. First, the certificate must be present on the machine from which we will be accessing Always Encrypted data. Just having the certificate is not enough. We need to grant our user access.

To do so, access the certificate manager by clicking the start button and typing “Manage Computer Certificate” or by opening “certmgr. msc” from the Run menu. Under Certificates on the left side, expand Personal and select Certificates.

You should see your Always Encrypted Certificate listed in the right pane. Again, if it’s not already there, you’ll need to track the certificate down and import it.

Right click on your Always Encrypted certificate and select All Tasks > Manage Private Keys. Add your user or users and select the appropriate permissions.

That should do it. Try accessing the data again and you should be able to view the unencrypted data without that annoying “Failed to decrypt…” error getting in your way.

Thanks for reading!