If you set up SQL Server Always Encrypted and are now getting an error that says Failed to decrypt a column encryption key using key store provider: ‘MSSQL_CERTIFICATE_STORE’ there’s a good chance it’s a permissions issue. All you need to do to fix this error is to give the user running the client accessing the data permissions to the Always Encrypted Certificate that was generated when you created the encryption keys. This user is different than the user connecting to the SQL Server, this is a Windows account that needs access to the local machine certificate store on the client machine.
Always Encryption works by allowing each client requesting the data to decrypt the data on the client side via a generated certificate. Therefore, all the data remains encrypted from at rest until it reaches the client. If the client requesting the data does not have the correct certificate or access to the certificate, the decryption fails and you will receive this 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’.
Solution: Give client permissions to the generated certificate
- Open the local machine certificate manager on the client machine. This can be either your local machine or a web server. Whichever computer you installed the certificate on. Either type certlm into the run command or type Manage Computer Certificates into the search bar and you’ll see the certificate manager appear.
- Navigate to Personal –> Certificates –> Right click [Your Always Encrypted Certificate]
- Select All Tasks –> Manage Private Keys…
- Add permissions for the user who is running the client application that decrypts the data. If this is a website then the user would be the user running the application pool. If it’s SQL Server Management Studio than most likely you will want to give yourself or the whoever is running SSMS access to this certificate.
- Rerun your query and you will see that error is gone and decrypted data will be returned .