Where is the always-encrypted certificate hosted on the IIS 7.5 web server?

Question

We have a SQL Server 2016 database that uses Always Encrypted. Our recently published ASP.net website is trying to retrieve data from this database, and when that happens 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 was not placed in the right place on the server. During development, we just placed the certificate in the "Certificates" snap-in in the "Personal certificate store", and it worked, but now that the site was published, we tried to do the same on the web server, but it does not work (we kind of understood will not be).

Anonymous authentication is enabled on the site, and the anonymous user ID is IUSR. ASP.NET ignore is disabled.

Where is the place to place the certificate?

---

UPDATE - we made it work by changing the application pool identifier account to the one that created the certificate. This is also the account used to add the certificate to the Current User Personal list on the web server. We would prefer not to use this account, so again, where is the place to place the certificate?

Answer 1

IIS SQL-, , , , , →

• " → → → " "CEK_Auto 1" → Script Column Encryption Key as → window, script
• CEK_Auto 1
• 3 "CMK_Auto 1"
• "CMK_Auto 1" Script "CurrentUser" "LocalMachine"
• , , : "N'LocalMachine/my/G4452V8ERH035D2557N235B29MWR0SV834263G26"
• CMK_Auto 1 CEK_Auto 1 script
• ,
• , IIS express, ,
• , , ​​ "Encryption Encryption Setting = Enabled".

J

Answer 2

" " , , , , , , , -, , .

. , . .

powershell script :

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

mmc.

whoami
COMPUTER\myIISPoolUser
certmgr.msc

APP

Answer 3

IIS Express IIS.

(CMK) SSMS , , CEK.

IIS MyLocalMachine, - . .

IIS. , , " " IUSR.

Answer 4

, , , , .

( , , )

:

1. " ".

   Win + R → lusrmgr.msc , " " . , .

2. ( CustomAppPoolUser) "" . ( , mmc.exe , ).

3. powershell CustomAppPoolUser.

   ( Powershell , " ". , " ", Shift + . "" , , , " " , Powershell " " . ( Shift )).

4. certmgr.msc ( mmc.exe " ") .

5. Go to IIS Manager and verify that the application pool is configured to act as a user of the user application pool, and the Load User Profile option is set to True.

6. Go to the Local User and Groups utility and remove the user from the administrators group.

I also restarted after the whole process to make sure that it was working as expected, but this might not be necessary.