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

需要注意的是，证书必须在Local Machine里面，在证书上的右键菜单才有Manage Private Keys

回答1

Solution to This issue is

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

It solved my issues.

回答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

How to give ASP.NET access to a private key in a certificate in the certificate store?

回答1

Create / Purchase certificate. Make sure it has a private key.
Import the certificate into the "Local Computer" account. Best to use Certificates MMC. Make sure to check "Allow private key to be exported"
Based upon which, IIS 7.5 Application Pool's identity use one of the following.
- IIS 7.5 Website is running under ApplicationPoolIdentity. Open MMC => Add Certificates (Local computer) snap-in => Certificates (Local Computer) => Personal => Certificates => Right click the certificate of interest => All tasks => Manage private key => Add IIS AppPool\AppPoolName and grant it Full control. Replace "AppPoolName" with the name of your application pool (sometimes IIS_IUSRS)
- IIS 7.5 Website is running under NETWORK SERVICE. Using Certificates MMC, added "NETWORK SERVICE" to Full Trust on certificate in "Local Computer\Personal".
- IIS 7.5 Website is running under "MyIISUser" local computer user account. Using Certificates MMC, added "MyIISUser" (a new local computer user account) to Full Trust on certificate in "Local Computer\Personal".

Update based upon @Phil Hale comment:

Beware, if you're on a domain, your domain will be selected by default in the 'from location box'. Make sure to change that to "Local Computer". Change the location to "Local Computer" to view the app pool identities.

回答2

If you are trying to load a cert from a .pfx file in IIS the solution may be as simple as enabling this option for the Application Pool.

Right click on the App Pool and select Advanced Settings.

Then enable Load User Profile

回答3

Note on granting permissions via MMC, Certs, Select Cert, right-click, all-tasks, "Manage Private Keys"

Manage Private Keys is only on the menu list for Personal... So if you've put your cert in Trusted People, etc. you're out of luck.

We found a way around this which worked for us. Drag and drop the cert to Personal, do the Manage Private Keys thing to grant permissions. Remember to set to use object-type built-ins and use the local machine not domain. We granted rights to the DefaultAppPool user and left it at that.

Once you're done, drag and drop the cert back where ever you originally had it. Presto.