Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted December 3, 2015

WEBINAR: On-demand webcast

How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >

Exploration of SQL Server 2016 Always Encrypted – Part 1

By Greg Larsen

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
Always Encrypted Architecture

This diagram came from this Always Encrypted document from Microsoft:  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:

( NAME = N'DEMO', 
  FILENAME = N'C:\Program Files\Microsoft SQL
  SIZE = 4096KB , FILEGROWTH = 1024KB )
( NAME = N'DEMO_log', 
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\DEMO_log.ldf', 
  SIZE = 1024KB , FILEGROWTH = 10%)

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
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
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…
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
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
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
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…
New Column Encryption Key…

When I select that item The “New Column Encryption Key” window is displayed.

  New Column Encryption Key Window
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 

   LastName NVARCHAR(45),
   FirstName NVARCHAR(45),
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
        COLUMN_ENCRYPTION_KEY = Demo_Always_Encrypted_CEK
SSN CHAR(10) COLLATE Latin1_General_BIN2 
     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.

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM