8

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

Hakan Fıstık
  • 16,800
  • 14
  • 110
  • 131
user2640965
  • 83
  • 1
  • 1
  • 6
  • @Alpay did you do what error message suggested (verified that certificate with provided thumbprint is present in provided store)? – Evk Jan 22 '18 at 14:50
  • @Evk I exported the key from current user and imported it to local machine in order for iis to be able to see the certificate but it didn' t help. Error message says that it should be under current user but it is already there (sql server management studio placed it there by default) and i can see decrypted data using sql server management studio – Alpay Jan 22 '18 at 19:53

5 Answers5

5

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.

dilipkumar katre
  • 128
  • 1
  • 12
4

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

Alpay
  • 1,350
  • 2
  • 24
  • 56
  • Hi, you could explain how you did it? i have the same problem – tal Feb 19 '18 at 15:24
  • @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. – Alpay Feb 20 '18 at 06:21
  • There isn't security issue with this change? because i use with windows authentication.. – tal Feb 20 '18 at 07:24
  • @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 – Alpay Feb 20 '18 at 10:50
  • 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. – Gregory Bologna May 12 '20 at 18:12
1

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.

pat capozzi
  • 1,412
  • 1
  • 20
  • 16
1

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.

lucasreta
  • 965
  • 2
  • 10
  • 25
Rajans
  • 11
  • 1
1

I encountered the same issue and just wanted to share how I fix it. My app is a .Net Core Web API hosted in IIS in Windows Server 2016. SQL server is also installed in Windows Server 2016.

I created the Certificate inside Windows Server.

  1. Via RDP, perform the Always Encrypt steps.

    • Using SSMS, right click table then select Encrypt Column.
    • On column selection, select which table/fields to encrypt. Then choose Encryption type then click Next
    • On Master Key Configuration window, choose the following. Then click next. Select generate column: Auto generate, Select the key store provider: Windows certifidate store, Select a master key source: Local Machine
    • On Run Settings window, choose Proceed to Finish Now then click finish.
    • On summary window click finish.
    • On Results window, make sure all the Tasks are completed successfully.

  2. Verify that the Certificate was created for Local Machine. Open Certificates for Local Machine

    • Click windows, then start typing Certificates. You will see to matches. 1. Manage User Certificates, and 2. Manage Computer Certificates. Click on Manage Computer Certificates.
    • Certificates Manager window will open. You should see Always Encrypted Auto Certificate1 in this path: Certificates (Local Computer) > Personal > Certificates.

  3. Add permissions to IIS_IUSRS.

    • On Certificates Manager, right click Always Encrypted Auto Certificate1, then select All Tasks > Manage Private Keys.
    • On permissions window, Click Add. On Select Users or Groups window search for IIS_IUSRS user then click Check Names. Then Click Ok. Give IIS_IUSRS Full Control.

Please note the IIS application pool Identity in my case is LocalService. After all the steps, I was able to access the .Net core web api executing Stored Procedures for tables where Always Encrypt is enabled.

To query the tables from local machine, I downloaded the Certificates from Windows Server, then Imported it to my local machine. When you import in local machine, make sure it is also imported in Certificates - Local Computer > Personal > Certificates.

rpocfemia
  • 36
  • 3