IIS & Always Encrypted Process

Who is this for:

• People already familiar with the process but are getting issues when using IIS.

Setup:

• Ad-SQL-VM(Windows Server 2019) => virtual machine where SQL Server is installed.
• AD-Vm-Work-Environment(Windows Server 2019)=> client machine.

Keep in mind:

• IIS can only read from LocalMachine store. IIS Express reads from CurrentUser. That is why it could work with IIS Express but not with IIS.
• “IIS_IUSRS” group should be granted read permissions to the certificate. This is, if you are using default Application Pool Identity. If not, you may need to manually add the account used as Application Pool Identity to “IIS_IUSRS” group.
• If you want to undo the Always Encrypted Keys and delete created keys, you will have to set “Encryption Type” to “Plain Text” for those encrypted columns. For this process certificate is needed, so, if you removed from MMC it won’t decrypt them. Once they are as plain text, then you can erase Encryption and Master Keys.

1)Create a Column Master Key with a Keystore :“Windows Certificate Store — Local Machine”.

Once you generate the certificate you should see it in MMC:

If you want to check Thumbprint is the same as the one in SQL search your newly created Master Key, right click and:

• Script Column Master Key > CREATE To > New Query Editor Window

2) Create a Column Encryption Key by mapping the Certificate created on step 1. i.e. Column Master Key

3) Choose the column of a table to be encrypted, map the encryption Key from Step 2.

Now, if you query these fields you will see the following:

4) Export the certificate into a *.PFX file. This format includes private key that you will need in client machine. (run as Administrator)

5) Transfer this certificate and import the certificate into a MMC “Local Machine” in the client machine (run as Administrator).

File > Add/Remove Snap-in > Certificates > Add > Computer Account > Next > Local computer > Finish > OK

Thumbprint should be the same:

6) Right click on certificate > All Tasks > Manage Private Keys and grant permission(read) to “IIS_IUSRS” group. If you are using a custom account as Application Pool Identity, there is a slight chance it is not in “ISS_IUSRS” group, in that case add it manually to this group.

For demonstration purposes I am using a custom account for Application Pool Identity, so I will manually add it to “IIS_IUSRS” group:

This is my connection string living in web.config file of IIS application in AD-Vm-Work-Environment:

<connectionStrings>
 <add name=”DefaultConnection” connectionString=”Data Source=Ad-SQL-VM;Initial Catalog=Clinic;User ID=ContosoClinicApplication;Password=XXXXXXX; Column Encryption Setting=Enabled” providerName=”System.Data.SqlClient” />
</connectionStrings>

Let us make a request: