Always Encrypted In SQL Server 2016 - Step By Step Guide

This is part three of "Always Encrypted In SQL Server 2016 - Step By Step Guide" series. In this article, we are going to see how to search In an encrypted column and how to deploy a certificate to a different environment. In case you have missed out the previous two articles of the series, you can read them here:

Search Encrypted Column

As part of the Always Encrypted In SQL Server 2016 - Step By Step Guide - Part One article, we have created a table with few records as shown below.

Let's create a simple MVC application having a simple View to perform the equality search on LastName/FirstName/Middle Name columns. The proposed View will look like below.

The requirement is when a user enters the name and hits the search button, the application should make a call to a stored procedure that will pull all the matching records (remember, no wild-card search is possible as AE technique only allows equality search). In this article, we will not cover the implementation details for this screen logic.

For illustration purpose, we will consider the same "EmployeeDB" custom database created using the SSMS from our local development machine. To keep things simple, we are going to reference a single server that will serve as a web server and as well as a database server.

Let's deploy the ASP.NET MVC application to a dev. server (Server Name: DevServer01) and connect to this database. Configure a website with name "EPortal" on this dev. server and ideally, the website should look like below.

For our example, we will have a single server that would host the MVC app and SQL Server 2016. Let's have an app pool for the website as shown below.

Alright, everything is ready now! The connection string in the web.config file points to "EmployeeDB" database which will have a single stored procedure to perform the search query. Let's enter the First Name as "John" and hit the search button in the front-end application. Ohh! there seems to be an issue, doesn't there? The below error is displayed after performing the search.

So, what's going wrong? Remember that initially, we had configured the encryption process on "Employee" table using SSMS from the local development machine and due to this, the CMK certificate lies on our local machine and its not accessible on the dev. server where we published our sample application. The certificate created on the local development machine is shown below.

To fix this problem, we need to export this certificate to the dev. server under a service/system admin account. For illustration purposes, we shall use a system administrator account "devadmin" (which is available on our dev. server) for this process.

Let's first export the certificate from our local machine to a centralized place. Navigate to the Current User certificate store on local machine. Right click on the certificate and select "All Tasks -> Export..." as shown below.

Continue with the export wizard by clicking the Next button

Now, select the option "Yes, export the private key" and click Next

Select the option "Personal Information Exchange - PKCS" and click on Next

Then, select the "Password" check-box, enter a password for protecting the private key and click Next

Now select the location where you want to save the certificate and save the file with "AEC1"

That's it, the certificate is stored with .pfx extension and it can be imported on any server for encryption. So far, we have seen on setting up a website and exporting the certificate to a centralized place. Now, it's time to import on dev. server environment.

Login to the dev. server with system admin account "devadmin" and navigate to Current User Personal certificate store. Right-click on Personal and select "All Tasks -> Import.." option as shown below.

Continue the Import wizard by clicking on Next,

Now, we need to specify which file needs to be imported. Click on Browse...

In the below Open dialog, browse to the location where we have exported the certificate and select the .pfx file type from the drop-down as shown below

Now, we can see available .pfx files at this location. Select the file AEC1.pfx and click on Open

Now, Import wizard has the required file reference, so to move forward click on Next

Now, enter the password which we used earlier as part of export process and click on Next

Select the option "Place all certificates in the following store" and click on Next

Now, we can review the import settings once again before finishing the import process. As we don't need any change, just click on Finish

Finally, the import process gets completed and we get the below confirmation message displayed on the screen.

Summary

That's it. In this article, we have gone through the steps on setting up a website, exporting the certificate to a centralized place and importing to the intended dev. server environment for having the encryption to work. Thanks for reading the article.