IIS & Always Encrypted Process

  • 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:

--

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Selenium — for uncovering black holes

AWS CLI : Let’s learn more

The ‘Tightening Cycles’ of Agile, TDD, Learning, Wellbeing: Makers Academy, Day 1

Lenovo ThinkPad P52s Intel Core i7–8650U 32GB RAM 1TB NVMe SSD NVIDIA Quadro FHD

Lenovo ThinkPad P52s Intel Core i7-8650U 32GB RAM 1TB NVMe SSD NVIDIA Quadro FHD

Rollback to default graphic driver

I am glad to have this opportunity to express my opinion,This is a great attempt, with complicated…

Spain passport template in PSD format, fully editable (2015 — present)

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Adrian Jenkins

Adrian Jenkins

More from Medium

CS371p Spring 2022: Jae Garcia-Herrera

CS373 Spring 2022: Maria Gu

CS373 Spring 2022 — Week 7

CS371p Spring 2022 Blog # 4: Jeffrey Liu