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?

Remonstrance

Hey @RoastBeast, did you resolve this in the end, I'm struggling to get this working myself. I'm on IIS10 though... – Lagting

According to learn.microsoft.com/en-us/azure/sql-database/… in the Master Key Configuration section there are only two places to store a cert - In the Windows certificate store or Azure Key Vault. When adding to the Windows cert store, you can add as a specific user (The SA) or local machine, meaning anyone with access to that server can access they key. I think the most secure option is to only allow access to the SA,but I have found that the user must always be logged on to the server. – Snowflake

To clarify also - I would say the Azure Key vault would be more secure, but in a enterprise environment, we usually only have access to Windows cert store, and adding the cert here as a local user (Service Account) would prevent admins from being able to use the cert to access the data themselves. – Snowflake

Answer 1

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

Eakins

Could you add some more detail to your answer? I was able to export my cert as a PFX file, but I'm lost after that. – Algor

How do I get the cert into the App Pool's store? – Algor

When I run certmgr it's running under my account. How do I get certmgr to run under the App Pool's account? – Algor

I was not able to use certmrg.msc from my App Pool user since it required me to launch it as admin (and my app pool user isn't admin). However, variant with Import-PfxCertificate worked for me: PS C:\>$cred = Get-Credential PS C:\>Import-PfxCertificate -FilePath '.\DB encryption key.pfx' -CertStoreLocation Cert:\CurrentUser\My\ -Password $cred.Password – Monadnock

Answer 2

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

Ungenerous

Thanks a lot for this. I have been searching for hours and finally this helped me, – Geraldgeralda

Answer 3

Running on IIS Express is different from running on IIS.

when we create Column Master Key (CMK) from SSMS at that time it generates Always Encrypted Certificate based on which location we have set while creating CEK.

For IIS, you have to generate certificate under MyLocalMachine, and then install certificate on hosting server with administrator rights. this will work for you.

You also need to give access of that certificate to IIS User. This can be done by right click on certificate and then click on manage primary key and add IUSR.

Canonicity

Answer 4

I had the same issue and here's what solved it for me.

1. Run SSMS as administrator
2. Right-click the first column you want to encrypt and select Encrypt column...
3. In the wizard to encrypt the column, under Select a master key source, select Local Machine and not Current User. (In my case, the Local Machine option only appears when I am running SSMS as administrator, hence #1).
4. Then launch mmc.exe. Under File > Add/Remove Snap in..., add the Certificates snap-in. When prompted, select Computer Account then Local Computer.
5. Now that Certificates has been added, expand Certificates (Local Computer) > Personal > Certificates, select the Always Encrypted Certificate you just created, right-click it and go to All Tasks > Manage Private Keys..., then make sure the user who is going to access the database has read permissions.

If you want to move the database to another machine, in this same MMC window, right-click the certificate, then All Tasks > Export. When prompted, choose the option to export the private key with the certificate (won't work if you don't). You'll need to enter a password before exporting. Once exported, import it to the other machine using that same MMC window. You'll need to enter the password you created when exporting and again make sure your user has read permission to it.

Dessertspoon