Always Encryption: Failed to decrypt a column encryption key using key store provider: 'MSSQL_CERTIFICATE_STORE'

Question

I am performing Always encryption in my web application, Which insert data by a WCF service which is hosted by a window service. I am getting following error while insertion of data into encrypted column.

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: '64-56-87-3B-EC-2E-11-9D-2A-B5'. Certificate with thumbprint 'A0D10777759BBD947EEA6F3F5D1A7989514C45F0' not found in certificate store 'My' in certificate location 'CurrentUser'. Verify the certificate path in the column master key definition in the database is correct, and the certificate has been imported correctly into the certificate location/store. Parameter name: masterKeyPath

Answer 1

Solution to This issue is

1. Run (MMC)
2. Select certificate in snap console.
3. Locate your always encrypted certificate, either My user, or Machine Account
4. Right click-> All tasks-> Manage private key.
5. Add the windows user which is making SQL connection.
6. User could be the process user, it can be either IIS or any windows Logon user.

It solved my issues.

Answer 2

Ok, I found out the problem. The case was exactly this: I set always encrypted for a column using SQL Server Management Studio which started with my own account. Because i run SSMS process with my own Windows Account, it placed the certificate inside Current User / MY directory of my account. However IIS application pool is running with a different account, so it couldn' t find the certificate under my account' s CurrentUser/My directory.

I restarted the IIS application pool with the same account who started SSMS and created the certificate, and the problem gone.

Thanks

Answer 3

This can happen when you start using the database with Visual Studio or a web server on a different machine then where the Always Encrypted DB is stored.

Part of your message will be: Certificate with thumbprint '............BAE90' not found in certificate store 'My' in certificate location 'CurrentUser'.

Go to the computer where the database resides. Open the Certificate Manager.

Right click on 'Certificates - Current User' and do a find on the last few digits (in the example BAE90). User look in Field : SHA1 Hash.

Once you find the Cert then right click on it and export to file.

Open the Certificate Manager on the new Visual Studio machine and do an import of the certificate and you should be good to go.

As in the other answers, you also need to 'sync' the user in the app pool to the user where the cert has been added.

Answer 4

I have faced the same issue and tried many solutions, but actual solution is very simple which solved my issue.

To install the certificate on ii's server irrespective to your db server whether db is on another server or same server:

1 - Go to the certificate manager of ii's server: type certmgr.msc in "Run" and press Enter

2 - You will find personal folder --> certificate folder: Right click on it and import your certificate.

3 - Select your certificate from file then instead of installing it on personal select the auto detect path option radio button and finish up your installation.

4 - Remember this installed certificate will not be seen under your personal --> certificate folder but your application run after this as certificate will install automatically on right path.

This solution worked for me after doing 3 days R&D.