Where does one place the Always Encrypted Certificate on an IIS 7.5 web server?

Question

We have a SQL Server 2016 database that employs Always Encrypted. Our recently published ASP.net web site attempts to pull data from this database, and when it does we get this error:

Error: Failed to decrypt column 'EnSSd'. 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: 'B8-48-B3-62-90-0B-1D-A6-7D-80'. Certificate with thumbprint '97B0D3A64CADBE86FE23559AEE2783317655FD0F' 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

Now we know that this means that the certificate has not been placed in the proper location on the server. During development we simply placed the certificate in the Certificates snap-in under the Personal Certificate Store, and that worked, however now that the site has been published we tried doing the same on the web server but it's not working (we kind of figured it wouldn't).

Anonymous Authentication is enabled on the site and the anonymous user identity is IUSR. ASP.NET impersonation is disabled.

Where is the proper place to put the certificate?

---

UPDATE - we got it to work by changing the Application Pool Identity account to the one that created the Certificate. It was also the account used when adding the certificate to the Current User-Personal list on the web server. We would rather not use this account, so again, where is the proper place to put the certificate?

Answer 1

IIS can't recognize the certificate from local user, while creating the certificate in SQL server, by default it's putting in to the local user store, do the following things and make sure the certificate generated under local machine -> current user certificate store

1. Generate the encrypted columns with default certificates
2. Undo all encrypted columns in to plain text
3. Go to the certificate and key from the table security "Your DB - > Tables - > Security -> Always Encrypted Keys" and right click the "CEK_Auto 1" -> Script Column Encryption Key as -> Create to new window, keep this generated script
4. Delete the CEK_Auto 1
5. Do the step 3 for "CMK_Auto 1" certificate and delete this as well
6. in the "CMK_Auto 1" script change the certificate path "CurrentUser" in to "LocalMachine"
7. you example path will be like this "N'LocalMachine/my/G4452V8ERH035D2557N235B29MWR0SV834263G26'"
8. execute the CMK_Auto 1 and CEK_Auto 1 script
9. make sure the certificate generated local machine personal directory
10. it will work, if not test with IIS express that means still your certificate held in local user personal directory
11. rest all same make sure the "Column Encryption Setting = Enabled" added in the connection string.

Thanks

John Rajesh J

Answer 2

Always Encrypted requires that the user that is accessing the database to have both the public and private key, which is what it appears to require you to use the account to generate the certificate as they will have this key.

What I usually do is generate the certificate and export the cert with a private key and secure passphrase. Then import the cert with key into the personal store of the account you use to run the app pool. This cannot be a generic integrated account and must be a service account you specify.

run a powershell script as the user:

whoami
COMPUTER\myIISPoolUser
Set-Location -Path cert:\localMachine\my
Import-PfxCertificate –FilePath c:\AlwaysEncrypt.pfx

or use mmc.

whoami
COMPUTER\myIISPoolUser
certmgr.msc

You must also allow the APP Pool user load user profile