Always Encrypted with applications running under IIS
When configuring Always Encrypted via Sql Server Management Studio (SSMS) it's easy just to right click your database or tables 'Encrypt Columns' and go from there. When developing locally this will probably be fine and everything works as expected.
When running through that process on a production application running on IIS , you may run into some issues. For example running an Asp.Net Core application leveraging EF Core, you may encounter the following error when trying to read or write encrypted data
An unhandled exception has occurred while executing the request.
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
Microsoft.Data.SqlClient.SqlException (0x80131904): 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: 'KEY-HERE'.
Certificate with thumbprint 'AABBCCCTHUMBPRINTAABBCC' 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 'masterKeyPath')
Looking at the error, you can see our application is looking for the certificate in the 'Current User ' certificate store. Typically your IIS application will run under an ApplicationPoolIdentity account and thus wont have access to this certificate store.
To create a certificate and make it accessible to the account your application pool is configured to run under, there is a slightly different approach required rather than letting the wizard do all of the work for you.
From SSMS expand the 'Security' node under your database, then expand 'Always Encrypted Keys'and select 'New Column Master Key'
From the 'New Column Master Key' enter a name for your key then change the Key store to 'Windows Certificate Store - Local Machine' and click 'Generate Certificate' . Note if this button is greyed out you may need to run SSMS as Administrator
Now when you are encrypting columns using the wizard, you can select this master key rather than letting the wizard create a new key
The final step is to ensure the certificate can be used by the account your application in IIS is running under. You can check this from IIS Manager
The ApplicationPoolIdentity is part of the IIS_IUSRS group that we will want to grant permission too, to access the encryption key. To do this open the MMC snap in (run mmc.exe from your start menu)
Select 'Certificates' from the available snap-ins and click 'Add', selecting 'Computer Account' when prompted and finish on the next screen.
Once the snap-in is loaded, expand Certiicates (Local Computer) > Personal > Certificates and you should see your certificate listed (probably called Always Encrypted Auto Certificate) or similar.
Right click certificate and select 'All Tasks' and 'Manage Private Keys'
Finally click 'Add' and find the IIS_IUSERS and add them, setting the correct permissions
When your run your application now, and try to read or create encrypted data you should no longer have issue around the location of the certificate.