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

Solution 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.

Solution 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

Solution 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.

Comments

[tal]

Hi, you could explain how you did it? i have the same problem

[Alpay]

@tal Please pay attention to my last sentence. Go to IIS manager / Application Pools and select your pool. Under Advanced Settings / Process Model you can change the user to the one who really 'has' the certificate.

[tal]

There isn't security issue with this change? because i use with windows authentication..

[Alpay]

@tal alternatively, you can run sql server management studio using the same user as in IIS pool i believe. If you run ssms with a specific user and create the certificate, it will be installed into that user' s MY directory, so that IIS will be able to find the certificate

[Gregory Bologna]

I think this worked out for you by coincidence. AppPool Identity is most likely going to be an account that doesn't have anything to do with generating keys in the database. One example may be that the app pool Identity is an Active Directory account for login authentication.