Exploration of SQL Server 2016 Always Encrypted – Part 2

In my last article (Exploration of SQL Server 2016 Always Encrypted – Part 1) I discussed how to setup and use the new SQL Server 2016 Always Encrypted feature, and the problem I had with restricting access from Database Administrators.  In this article I will expand my exploration of Always Encrypted.  I will explore the roles different individuals or groups should assume when setting up encryption keys and tables that contain Always Encrypted data.  By splitting the setup roles you can ensure only authorized individuals have access to Always Encrypted data.  Additionally I will show how I resolved the problem I had in Part 1 with Database Admins being able to see the Always Encrypted data.

Different Staff Role

As with anything security related there are always different roles individuals must play to keep things secure.   There are basically two types of access to always encrypted data.  Those that can see the encrypted data and those that can’t.    Additionally there are those that administer the application, which I’ll call Application Admins, and then there are those that administer the data and I’ll call them Database Admins.  In some large organizations you might even find the Security Admin staff might be involved in storing Always Encrypted data by creating and manage the certificate, the Column Master Key (CMK) and Column Encryption Key (CEK).   

In part 1, the Database Administrator created the CMK and CEK, and stored it on it on the SQL Server machine.  As we already saw in part 1, by doing this the Database Administrator could see the encrypted data.   To avoid this either the Application Admin, or the Security Admin needs to create the CMK and CEK.  Once the keys have been created they share the CMK key location and the CEK with the Database Admin, so the Database Admin can use the provided CMK and CEK to define a table that contains Always Encrypted columns.

Let’s see how to set up an Always Encrypted table where the Security Admin defines the certificate, the CMK and CEK, and then provide the Application Admin and Database Admin with the components they need to securely implement Always Encrypted.

Creating Certificate for Always Encrypted

In order to make it easy for the Security Admin to create a CMK and a CEK I set up another SQL Server virtual machine that was running SQL Server 2016 CTP3.   This way the Security Admin staff can use SSMS to define the CMK and CEK needed for Always Encrypted.   In addition to installing SQL Server 2016 on the Security Admin SQL Server machine I also installed the Windows SDK. I downloaded the SDK so I could get the makecert.exe.  I used the makecert.exe so I could create a self-signed certificate for testing Always Encrypted.

The first thing I do while assuming the role of the Security Admin is run the following markcert.exe command, from the command prompt:

makecert.exe -n
"CN=Part2_Demo" -pe -sr LocalMachine -r -eku, -ss my -sky exchange -sp
"Microsoft Strong Cryptographic Provider" -sy 1 -len 2048 -a sha256

By running this command I created a certificate that gets stored in the LocalMachine key store on the Security Admin SQL Server machine.

Creating CMK and CEK

The next step I take as playing the Security Admin role is to generate and execute a script to create the Column Master Key (CMK).  To generate a script to create the CMK, I logged on to SSMS on my new Security Admin SQL Server 2016 machine.  I then created a database named SecAdmin.  After the database is created I then expanded the SecAdmin database, and Security item and then right clicked on the “Always Encrypted Keys”, which brought up the following menu.


Here I clicked on the “New Column Master Key…” item.  When I did this the New Column Master Key window was displayed:

New Column Master Key
New Column Master Key

On this screen I entered the name of my Column Master Key, which in this case is called “Demo_CMK”.  I used the drop down on the “Key Store” item to identify the certificates available in the LocalMachine key store.  As you can see there is only one.  It is the one I created with the makecert command I ran above.  After selecting the “Part2_Demo” cert I then clicked on the “Script” item and generated so I can generate the script to create the CMK.  Here is the script that was generated:

     KEY_PATH = N'LocalMachine/My/61405FF54F0E2C2CA9879D0EDF9907D4988EC7BB'

I reviewed the script and then executed it on an SSMS query window to create the Column Master Key on the Security Admin machine. 

Next I need to create the Column Encryption Key (CEK). I do this on the Security Admin machine, by expanding the Column Encryption Keys item in SSMS and then clicking on the “New Column Encryption Key…” option as shown below:

New Column Encryption Key…
New Column Encryption Key…

When I did this the New Column Encryption Key window was displayed. 

New Column Encryption Key Window
New Column Encryption Key Window

On this screen I entered the name of my CEK, which I called “Demo_CEK”.  I then used the drop down on the Column Master Key item to select the CMK I generated above, in this case that is “DEMO_CMK” item.  I then generated the script to create the CEK.  Here is the script that was generated:

       COLUMN_MASTER_KEY = [Demo_CMK],
       ENCRYPTED_VALUE = 0x01700000016C006F00630061006C006D0061006300680069006E0065002F006D0079002F003600310034003000350066006600350034006

Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles