With the introduction of SQL Server 2016 you now have a new way to encrypt columns called Always Encrypted. With Always Encrypted, data is encrypted at the application layer via ADO.NET. This means you can encrypt your confidential data with your .NET application prior to the data being sent across the network to SQL Server. In this article I will explain my experience with exploring setting up a table that stores always encrypted data.
Always Encrypted Architecture
The architecture for Always Encrypted has the application performing the column level encrypting prior to the confidential columns from being sent to SQL Server. The actual encryption is done by the ADO.NET drivers on an application, or client machine. When a .NET application sends plain text data to ADO.NET it is encrypted prior to sending it to SQL Server. The only change to store encrypted data that the application needs to make is to change the connection string to indicate column encryption is enabled. When column encryption is enabled ADO.NET will encrypt Always Encrypted columns prior to sending the data to SQL Server, and will decrypted Always Encrypted columns when they are read from SQL Server. The following diagram shows this architecture:
Always Encrypted Architecture
This diagram came from this Always Encrypted document from Microsoft: https://msdn.microsoft.com/en-us/library/mt163865.aspx. In this diagram you can see two different kinds of keys: Column Master Key, and Column Encryption Key.
The Column Master Key is stored on an application machine, in an external key store. This key is used to protect the Column Encryption key. By placing this key on the application machine SQL Server doesn’t have access to the column master key directly. Therefore SQL Server by itself will not be able decrypt the Always Encrypted data.
The other key, the Column Encryption Key, is stored on SQL Server. This key is used to encrypt/decrypt the Always Encrypted columns. Once ADO.NET has decrypted the Column Encryption Key, using the Column Master Key it can use the decrypt Column Encryption Key to decrypt/encrypt Always Encrypted columns.
Note in order to make this work the client application needs to support .NET framework 4.6. This framework is what does the actual encryption, and decryption.
My Experience in Deploying Always Encrypted
To explain my experience using Always Encrypted, I will discuss the steps I went through to create and store encrypted data in my first Always Encrypted table.
I stated by doing some research using the links in the “Additional Research …” section near the bottom of this article. From these articles I identified that I needed the following components to store Always Encrypted column in a SQL Server table:
- An application that uses .NET 4.6 framework
- A SQL Server 2016 instance
- A certificate store to support the Column Master Key
- A Column Master Key
- A Column Encryption Key
- A table with Always Encrypted columns
For my Always Encrypted test environment I used my laptop. On my laptop I created a single VM that runs my SQL Server 2016 demo machine, and I created a C# Visual Studio project to create my .NET 4.6 application. The C# Visual Studio project run directly on my laptop, outside the SQL Server 2016 VM.
For testing Always Encrypted I had one and only one goal in mind. My goal was to prove I could use Always Encrypted to hide confidential data from DBAs (individuals in sysadmin role) within SQL Server. My testing went through a couple of iterations while working to meet this design goal. Let me walk you through each of the iterations I went through.
Creating a Database to Hold my Always Encrypted Table
The first thing I did was create a SQL Server 2016 DEMO database. I will use this database to store my table that will contains Always Encrypted Columns. Here is the code I used to create my database:
CREATE DATABASE [DEMO] CONTAINMENT = NONE ON PRIMARY ( NAME = N'DEMO', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\DEMO.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'DEMO_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\DEMO_log.ldf', SIZE = 1024KB , FILEGROWTH = 10%) GO
Creating Column Master Key and Column Encryption Key
Next I create my Column Master Key using SSMS on my VM machine. I did this by expanding my DEMO database in SSMS, then expanding the “Security” item. When I did that you can see the “Always Encrypted Keys” item, as shown below.
Always Encrypted Keys
When I expand the “Always Encrypted Keys” item I find the two key items as show below:
Column Master Keys and Column Encryption Keys
To create my Column Master Key I right clicked on the “Column Master Keys” item, which displayed the following drop down:
New Column Master Key…
On this drop down I picked the “New Column Master Key…” item. When I clicked on that item the following screen was displayed:
New Column Master Key
On this window you can see I need to identify a “Name” and a “Key store” location for creating my new Column Master Key. For the name I type in “Demo_Always_Encrypted_CMK”. For the “Key store”, I expand the drop down box to see the different key store options, see below:
New Column Master Key – Name and Key Store
By reviewing this window you can see I have a choice of three different key store locations: Window Certificate Store – Current User, Window Certificate Store – Local Machine, Azure Key Vault. For my initial testing I select the “Window Certificate Store – Current User”, and then click on the “Generate Certificate” button. When I did that the following window was displayed:
Always Encrypted Certificate
Here you can see that a new certificate was created, and there is a Thumbprint associated with it.
Now that my “Demo_Always_Encrypted_CMK” encrypted master key has been created I can create a column encryption key. I do this by first right clicking on the “Column Encryption Key” item in the object explorer and then selecting the “New Column Encryption Key…” item as shown below:
New Column Encryption Key…
When I select that item The “New Column Encryption Key” window is displayed.
New Column Encryption Key Window
On this screen I entered the name of my new column encryption key, which is “Demo_Always_Encrypted_CEK”. I also selected the “Column master key” from the drop down menu. Once my name and master key where identified I then entered the OK button to create my column encryption key.
Creating My First Always Encrypted Table
Now that my column master key and column encrypted key have been created I can create my a table that will store always encrypted columns. To do this I used the code below:
CREATE TABLE dbo.Demo_Always_Encrypted ( ID INT IDENTITY(1,1) PRIMARY KEY, LastName NVARCHAR(45), FirstName NVARCHAR(45), BirthDate DATE ENCRYPTED WITH ( ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = Demo_Always_Encrypted_CEK ), SSN CHAR(10) COLLATE Latin1_General_BIN2 ENCRYPTED WITH ( ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = Demo_Always_Encrypted_CEK ) );
In this code you can see that the BirthDate, and SSN are always encyrpted columns. For the BirthDate column I created it with an encryption type of RANDOMIZED, whereas the second always encrypted column, SSN has a encryption type of DETERMINISTIC.
DETERMINISTIC encryption means that clear text of a given column value will always be encrypted to the same value. Whereas a RANDOMIZE encrypted column could get a different encrypted value each time the column value is encrypted. If you plan to encrypted a column for searching or joining then you will need to use an encryption type of DETERMINISTIC. You should use RANDOMIZE encryption type for columns used for displayed purposes only. DETERMINISTIC is less secure, because by using a brute force attack, eventually a hacker could determine the unencrypted value. Therefore using RANDOMIZE encrypted columns are more secure then using DETERMINISTIC encrypted columns.
One additional comment. When encrypting a string value you need set the Always Encrypted column to a BIN2 collation setting. By reviewing the code above you can see SSN is defined as a nvarchar(10), with a collation of Latin1_General_BIN2.
