An introduction to Always Encrypted

One of the major new features in SQL Server 2016 will be a new Always Encrypted.  This is a method of separating the encryption keys from SQL Server in order to restrict access to sensitive data from highly privileged individuals.  The process of setting up Always Encrypted is very simple, straight forward and may require little application modification.  Let’s take a look.

The first step in implementing Always Encrypted is to create an Encryption Master Key.  This can be accomplished in SSMS by either drilling down Database->Security->Always Encrypted Keys, right clicking ‘Column Master Key Definitions’ and selecting ‘New Column Master Key Definition’. Give the master Key a name and generate a self-signed certificate. 
Creating the Column Master Key

The next step is to create a Column Encryption Key.  Right click ‘Column Encryption Keys’ and select ‘New Column Encryption Key’.  The wizard here is simple, give the encryption key a name and map it to the master key created in the last step.
Creating the Column Encryption Key

Next, we’ll create our tables. Always Encrypted supports randomized and deterministic encryption. Deterministic encryption always generates the same encrypted value for any plain text value.  As such, it is less secure than random encryption but allows some more capabilities such as filtering by equality, table joins on the encrypted column and grouping.  It is restricted to a collation with a binary2 sort order.  These are the table structures used in these examples.

USE [AlwaysEncrypted]
CREATE TABLE [dbo].[Account](
                [AccountID] [int] IDENTITY(1,1) NOT NULL,
                [SSN] [nvarchar](11) COLLATE  Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY =
[ColumnKey], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
                [Balance] [decimal](20, 5) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [ColumnKey],
 CREATE TABLE [dbo].[Customer](
                [AccountID] [int] IDENTITY(1,1) NOT NULL,
                [SSN] [nvarchar](11) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [ColumnKey], ENCRYPTION_TYPE =
Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
                [Type] [int] NULL
Inserting any querying data should be happening from your application.  Before we can do this properly, we need to ensure that the application is using .Net Framework 4.6 and has ‘Column Encryption Setting=enabled’ set in its database connection string.  This can be accomplished in Visual Studio by opening the project properties and selecting .Net Framework 4.6 in the Target Framework Dropdown box and updating the connection string.
Updating the .NET project
Now we can start inserting and querying data.  I put together a very crude c sharp console application to demonstrate this section. 

Application Insert Statements

Now if we try to select data in SSMS, we see we get encrypted values.

Values are encrypted when queried in SSMS

If we query the database from the application, we can see some values being returned.  Here are some examples of querying both randomly and deterministically encrypted columns.
Querying randomly encrypted data

Deterministic encryption allows filtering on the encrypted column

If you try to filter on a randomly encrypted column, you’ll receive the following error:

Error attempting to filter on a randomly encrypted column

And there you have it, a quick introduction to implementing Always Encrypted.  As we see, its pretty straight forward to set up.  There are a few restrictions using this feature which can be found here and I'd encourage anyone interested to read up on Always Encrypted development here.

Labels: ,