Always Encrypted In SQL Server 2016 - Step By Step Guide - Part One

In this article, I'm going to explain briefly about the "Always Encrypted" feature and how to use it in SQL Server 2016 for configuring the encryption.

Topics discussed,

• Project Scenario
• Tech Specs
• Always Encrypted Approach
• Certificate and Keys
  
• Configure Sample Database
• Configuring Encryption

Project Scenario

 

Let's say, we have a requirement to encrypt the PII data for an existing ASP.NET web application. Data needs to be encrypted at rest as well as in transit. Considering the existing PROD data, we need to identify an encryption approach that best suits the requirement. I'm going to share my experience in finding the encryption approach and implementing it on a sample database.

 

Tech Specs 

 

Below are the tools used for this implementation.

• Visual Studio 2013 Premium Edition
• SQL Server 2016 Enterprise Edition
• SQL Server Management Studio v17.0
• .NET Framework 4.5.2               

Always Encrypted Approach

Keeping in mind that encryption needs to be applied for existing PROD data as well as for new records with a minimal change, let's use the Always Encrypted (AE) approach. Microsoft has designed this technique to protect any sensitive data being stored in SQL Server database. Always Encryption feature allows the client application to do the encryption/decryption work without involving the SQL Server.

In .NET context, the client application should use the Always Encrypted enabled client driver, i.e., ADO.NET driver to retrieve the data stored in encrypted columns.

 

When you send plaintext data, it is encrypted by the ADO.NET Driver before sending it to the SQL Server and as well as decrypts the data available in the encrypted columns while reading it from the database. Thus, SQL Server always receives encrypted data while executing Select queries or DML queries.

 

Using this technique, we can selectively encrypt the columns required without disturbing the rest of the columns in the table. Configuring column encryption could be done using SSMS or Powershell scripts.

 

Regarding the version of SQL Server where this feature is available, prior to SQL Server 2016 SP1, Always Encrypted was limited to the Enterprise Edition of SQL Server. 

 

Certificate and Keys

 

When you configure the encryption for sensitive column data, you have to specify the encryption type and keys to be used for the mechanism. AE uses two types of keys namely column encryption key and column master key.

• Column Encryption Key - It is used to encrypt the data in the selected column.
• Colum Master Key - It protects the encryption keys. In simple words, it encrypts the available column encryption keys.
• Encryption Type - Two types are there, 1) Deterministic and 2) Randomized.
• Deterministic - This type always generates the same ciphertext for a given plaintext value. It allows equality joins, grouping and indexing on encrypted columns.
• Randomized - This type of encryption is more secure than the Deterministic encryption, but prevents searching, grouping, indexing, and joining on encrypted columns. 

During the configuration, we can specify the location where our column master key needs to be stored. Available options are 1) Windows certificate store and 2) Azure Key Vault. For the article, we are going to consider the first option. 

 

We have covered all of the basic concepts that need to be known before configuring the encryption process.

  

Internal details of how the feature is working can be read at this link. 

  

Configure Sample Database

 

As I said above, encryption needs to be applied considering the PROD data of an existing web application. During my analysis, I have created a new database in SQL Server 2016 instance and ported the real-time data for one table which had around 1,00,000 records. However, for the explanation purpose, I'm going to refer a sample database which has few records throughout the article.

 

Let's create a sample database with name "EmployeeDB". Let's create a simple Employee table using the below-given scripts. 

Create Table EmployeeDetails
(
   EmployeeDetailsId INT IDENTITY(1,1) PRIMARY KEY,
   EmployeeNo VARCHAR(10),
   FirstName VARCHAR(20),
   MiddleName VARCHAR(20) NULL,
   LastName VARCHAR(20),
   DateOfBirth DATETIME
)

Let's insert few dummy records using the scripts given below.

INSERT INTO EmployeeDetails(EmployeeNo, FirstName, MiddleName, LastName, DateOfBirth)
VALUES ('FE00000001','John','','Smith','1985-08-12')
INSERT INTO EmployeeDetails(EmployeeNo, FirstName, MiddleName, LastName, DateOfBirth)
VALUES ('FE00000002','James','M','Peter','1972-10-30')
INSERT INTO EmployeeDetails(EmployeeNo, FirstName, MiddleName, LastName, DateOfBirth)
VALUES ('FE00000003','Clarke','H','Wayne','1985-08-12')

 

After configuring the database, the table has records as shown below.

 

 

Configuring Encryption 

 

Let's start the encryption process for this table. Right-click on the table and select the option "Encrypt Columns..." as shown below.

 

 

 

Below shown wizard will pop-up for configuring the encryption. Click on Next button.

 

 

 

Now, it's time to select the required columns to be encrypted. Let's target the FirstName, MiddleName and LastName columns for encryption. In "Column Selection" stage, select these three columns, opt the Encryption type as "Deterministic", and select the default key option as shown below.

 

 

We are using the same column encryption key for encrypting all of these columns. Click on Next button.

 

Now, we are in the stage of master key configuration. As I said earlier, the column master key(CMK) protects the available column encryption keys. So, its a must have to create this key and store it in a secure place. Leave the option "Auto-generate column master key" as it is, select "Windows certificate store" option to store the key, and select "Current User" option for the master key source as shown below.

 

 

On clicking Next, you will see a warning message saying when encryption is in-progress, do not update the selected table. Select the "Proceed to finish now" option and click Next button.

 

 

Now, the wizard will show settings we have selected so far for verification purpose, just click on Finish button.

 

 

 

On successful completion of the encryption process, you will be seeing the summary as shown below.

 

 

Alright, we have done all of the work we wanted to do. It's fun time now.  Let's see the output. Execute a select * from EmployeeDetails query and see the result set. Data in columns like FirstName, MiddleName, and LastName will be displayed in encrypted format as shown below.



Cool, we have achieved the end result. If you see the whole configuring process executed so far, we haven't changed the datatype/size of the columns to be encrypted and the Always Encrypted feature has done the magic for us. All of the existing data are encrypted now and going forward, new records will have encrypted data stored in these columns.

 

Let's examine what has been changed now. Right-click on the table, select "Script Table as -> Create To .." option to generate the table script. The table script will look like as follows,



The encrypted columns will have the association to the column encryption key, encryption type and algorithm used. Now, under EmployeeDB ->Security, expand node "Always Encrypted Keys". You will see the two keys which were created as part of the encryption process as shown below. Right-click on "CMK_Auto1" and select "Script Column Master Key as -> Create To.. " option to generate the script. Similarly, generate the script for "CEK_Auto1" key.



SQL Server database engine never stores the keys in plaintext. Let's say if the database backup file is hacked by someone or the SQL Server admin wants to see the data in plaintext, since both of them has the access to the encryption keys and certificate, they won't be able to decrypt the sensitive data stored in the encrypted columns.

 

If we need to see the data in plaintext while connecting to the SQL Server in SSMS, there is a small setting that needs to be done. In the Connect to Server pop-up, click on "Options <<" button.

 

 

Click on "Additional Connection Parameters" tab, copy the text Column Encryption Setting=Enabled as shown below and connect with your credentials.

 

 

Run the select query once again and you will see the data in plaintext.

 

 

Ok, cool. Now, where is the certificate is stored and how to find it?. Let's console window by running the command "mmc" in run window. Console window opens-up and select the option "Add/Remove Snap-in.." from File menu.



Select Certificates from Available snap-ins and click on "Add >" button.

 

 

Select "My user account" option and click on Finish button and later click on Ok button.

  

You will see the certificate created during the encryption process under Console Root -> Certificates - Current User -> Personal -> Certificates location as shown below and the certificate is valid for one year.


Double-click on the certificate to view the certificate information, as shown below.

 

 

That's it. In this article, we have seen about Always Encrypted and how to use it to encrypt sensitive column data. Going forward, we will see the limitations of this feature, more information on the certificates, and how to overcome a few limitations in a subsequent article.

I hope you have understood this article completely and wish to give some feedback or comments. Thank you.