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?
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
- Generate the encrypted columns with default certificates
- Undo all encrypted columns in to plain text
- 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
- Delete the CEK_Auto 1
- Do the step 3 for "CMK_Auto 1" certificate and delete this as well
- in the "CMK_Auto 1" script change the certificate path "CurrentUser" in to "LocalMachine"
- you example path will be like this "N'LocalMachine/my/G4452V8ERH035D2557N235B29MWR0SV834263G26'"
- execute the CMK_Auto 1 and CEK_Auto 1 script
- make sure the certificate generated local machine personal directory
- it will work, if not test with IIS express that means still your certificate held in local user personal directory
- rest all same make sure the "Column Encryption Setting = Enabled" added in the connection string.
Thanks
John Rajesh J
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
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.