Always encrypted setup

Question

We are getting ready to test Always encrypted on 2 tables in our database. My first try involved setting up as server administrator because I wanted to make it available to all web users and I selected "Local Machine" for the master key source. However, our web connection string is using the SQL user login. When we first connected and ran a stored procedure which uses one of the encrypted tables we got the error below. I think it is due to me not setting up the column encryption using that sql login. Can someone verify this may be the cause of the error? I think the certificate is tied to the user setting up encryption. Thanks.

Message: Failed to decrypt column 'FirstName'.
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: '1E-1F-76-FA-FA-37-0A-09-A3-B3'.
Keyset does not exist

Answer 1

Hi @David Chase ,

Please make sure to deploy the certificate to each machine hosting your application/service to the right store location (Local Machine).
Please try to grant the IIS application pool user who's going to run the web app the permission to be able to read private keys. Please refer to Always Encrypted Feature - Failed to decrypt column and Why my app user couldn't find always encrypted certificate which could help.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
What can I do if my transaction log is full?--- Hot issues November
How to convert Profiler trace into a SQL Server table -- Hot issues November

Answer 2

Not really. You can set up the key and then distribute the key. When you create the key you say where it is stored, and that can be on the user's local machine or it can be in a central place like Azure keyvault.

Assuming that you used MSSQL_CERTIFICATE_STORE, the encryption key needs to be on the machine with the web server, and in the store for the service account for the web server. Or, I should hasten to add, that is my understanding of it. I have never made such a setup myself.

This topic may help to clear things out:
https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/create-and-store-column-master-keys-always-encrypted?view=sql-server-ver15

Answer 3

If I log into SQL Server as the sql user in my connection string then everything works fine. I can even export/import the certificate to my local PC for running SSMS and all works fine. I was hoping to be able to create the certificate and have it accessible to all that connect to the database without the need to import the certificate to the local PC. However, this seems more secure to have it created by the connection user.

Answer 4

Decrypting is all about having the certificate where the connection is. In this case the Web Server would need the certificate and if you wanted it where you are then the certificate would need to be on your machine so that you can query it. Always Encrypted is mainly about Separation of Duties so that it is possible to have the DBA not see the decrypted data if they do not have the certificate.