Exploration of SQL Server 2016 Always Encrypted – Part 1-3

Thursday Dec 3rd 2015 by Greg Larsen
Share:

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. Follow Greg Larsen as he explores setting up a table that stores always encrypted data.

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: 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
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 
(
   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.

Stored Procedure for Testing Always Encrypted

To insert data into my first Always Encrypted table I will use the following stored procedures:

CREATE PROCEDURE Insert_Always_Encrypted (
@LastName varchar(45),
@FirstName varchar(45),
@BirthDate date, 
@SSN NVARCHAR(10))
AS 
INSERT INTO dbo.Demo_Always_Encrypted
   (LastName, FirstName, BirthDate, SSN)
VALUES (@LastName,@FirstName,@BirthDate,@SSN);

As you can see, this code accepts parameter values for every column in my Demo_Always_Encrypted table.  It then takes the passed parameter values and inserts them into my table.

Test Iteration #1 - Inserting Always Encrypted Data

As my first test of Always Encrypted I will call my Insert_Alway_Encrypted store procedure with the following code from within a query window within SQL Server Management Studio (SSMS):

EXEC Insert_Always_Encrypted @LastName = 'Larsen',
                            @FirstName = 'Gregory',
                            @BirthDate = '1950-01-01',
                            @SSN = '123-45-6789';

When I run this code I get the following error:

Msg
206, Level 16, State 2, Procedure Insert_Always_Encrypted, Line 11 
Operand type clash: varchar is incompatible with date encrypted with (encryption_type =
'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256',
column_encryption_key_name = 'Demo_Always_Encrypted_CEK',
column_encryption_key_database_name = 'DEMO')

I suspect this error message is telling me I sent a BirthDate column value in clear text, instead of it being an encrypted value.  Remember the Always Encrypted architecture requires the encrypted column to be encrypted via ADO.NET, which didn’t occur when I executed this code via SSMS.

I did find I could insert a row into my Demo_Always_Encrypted data with this INSERT statement:

INSERT INTO dbo.Demo_Always_Encrypted
   (LastName, FirstName) 
VALUES ('Larsen','Gregory');

But here I didn’t send any values for the Always Encrypted columns.  Time to look for another method that can successfully store data in my Always Encrypted columns in my Demo_Always_Encrupted table.

Setting up for Test Iteration #2 - Inserting Always Encrypted Data

For my second test I will use a C# program to insert a record into my Demo_Always_Encrypted table.  Once the record is inserted it will then be read back to verify my C# could read and decypted the encrypted columns.  The C# code for this test can be found at the bottom of this article.

In the C# code at the bottom of this article I will use the stored procedure “Insert_Always_Encrypted” to insert my a row into my “Demo_Always_Encrypted” table.  After the record is inserted the code displays a message box that says “Inserted Demo Records…”.  Next the code reads the encrtyped data by using a SELECT statement and lastly the code displays a message box showing the unencrypted data it read. 

Before I use the C# code I need to perform a few things.  First I will truncate my Demo_Always_Encrypted table, to removed the data from iteration #1, by running the following command:

TRUNCATE TABLE Demo_Always_Encrypted;

The second thing I need to do is make sure my machine where I will be running my C# code has access to the Column Master Key value.  Remember my C# code will be running on my host machine (or directly on my laptop OS, instead of inside my VM).  Since I created my Column Master Key (CMK) on my VM, I need to export the private key for my CMK from the certificate store on my VM machine and store the exported certificate in the certificate store on my laptop. 

Exporting and Importing Column Master Key

In order to export and import the CMK, I will use certmgr.exe, which I obtained by downloading the Windows SDK from this locaiton: http://go.microsoft.com/fwlink/p/?linkid=84091. I installed the Windows SDK on both my VM machine and my laptop.  If you already know how to use certmgr to import and export certificates then you can skip to section “Generating SQL Server Login for C# Application”.

To start the export process I executed certmgr from my VM machine.  When I do this the following window is displayed:

certmgr
certmgr

On this window you can see I’m browsing the “Current User” certificate store.  I drill down and look at the certs under the “Personal” folder.  When doing that I find the following certificate:

Personal Folder Certificates
Personal Folder Certificates

As you can see I found the certificate that was created when I created my Column Master Key.  To export this cert I right clicked on the certificate, and then clicked on the “All Tasks” item from the menu displayed and then finally clicked on the “Export…” task on the next window displayed.  When I select the “Export…” task, a welcome window is display, where I clicked the “Next” button which brings up the “Certificate Export Wizard” below: 

Certificate Export Wizard
Certificate Export Wizard

On this screen I select the “Yes, export the private key” radio button and then click on the “Next” screen.  Upon doing this the following screen is displayed:

Certificate Export Wizard – Export File Format
Certificate Export Wizard – Export File Format

Here I just take the defaults and click on the “Next” button. Doing this brings up the following window:

Certificate Export Wizard – Security
Certificate Export Wizard – Security

On this screen I select the “Password:” checkbox, and then enter a password that will be associated with my exported certificate file.  Once my password is entered I then click on the “Next” button, which brings up this window:

Certificate Export Wizard – File to Export
Certificate Export Wizard – File to Export

On this window I enter a location and name for my exported certificate.  As you can see I am going to export my cert to a file named “Demo_Always_Encrypted_CMK”.  When I click on the “Next” button the following window is displayed:

Certificate Export Wizard – Completing the Certificate Export Wizard
Certificate Export Wizard – Completing the Certificate Export Wizard

Here I review the export settings and then click on “Finish” button.   When I did this a message box was displayed that said my certificate was exported. 

To import the exported certificate I first copied the exported certificate from my VM machine to the C:\temp directory on my laptop.  Once the certificate file was copied I started certmgr.msc on my laptop so I could import the exported cert file.   In the certmgr interface, I expand the Personal folder and then clicked on the “All Task:” item and finally selected the “Import” option.  When I do this the following screen is displayed:

Welcome to the Certificate Import Wizard
Welcome to the Certificate Import Wizard

On this screen I just clicked on the “Next”, which displays the following window:

Certificate Import Wizard – File to Import
Certificate Import Wizard – File to Import

On this screen I first browsed for my CMK certificate that I copied to the C:\temp directory.  After finding and selecting my cert file I then clicked the “Next” button.   Upon doing that the following screen is displayed:

Certificate Import Wizard – Private Key Protection
Certificate Import Wizard – Private Key Protection

On this window I entered the password I associated with my exported cert file and then click on the
“Next” button.   When I do this the following window is displayed:

Certificate Import Wizard – Certificate Store
Certificate Import Wizard – Certificate Store

On this screen I reviewed where the imported cert was going to be placed.  In this case it defaulted to “Personal”.  I took the default and clicked on the “Next” button, and the following screen is displayed:

Completing the Certificate Import Wizard
Completing the Certificate Import Wizard

On this screen it shows where my imported cert will be stored and which file was used to import the cert.  Once verifying this information is correct I then clicked on the “Finish” button.   When I do this a message box is displayed verifying I successful imported my certificate. 

VALUES (@LastName,@FirstName,@BirthDate,@SSN);

Generating SQL Server Login for C# Application

Lastly I need to create a SQL Server login and database user that my C# application will use.  Below is the code I used to create the SQL authenticated login and database user. 

--Create SQL Authenticated login
USE [master]
GO
CREATE LOGIN [Greg] WITH PASSWORD=N'Test', DEFAULT_DATABASE=[Demo], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

--Create Database User
USE [DEMO]
GO
CREATE USER [Greg] FOR LOGIN [Greg] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_owner] ADD MEMBER [Greg]
GO

Normally I would use a Windows authenticated service account for an application connection.  But since this was just a demo I used a SQL Authenticated login.

Running Iteration #2 Inserting Always Encrypted Data

Now that my CMK certificate is on stored  in the cert store on my laptop I will run the C# code that is at the end of this article, in a  Visual Studio 2015 .NET 4.6 project.  Remember NET 4.6 framework is the mechanism from encrypting and decrypting Always Encrypted columns.   When I execute my C# code via Visual Studio I first see the following Message Box.

Inserted Demo Record
Inserted Demo Record

The text in this message box identifies the values for the BirthDate and SSN columns involved in the INSERT statement. My C# code uses the stored procedure name “Insert_Always_Encrypted” to performed the actual INSERT statement.      

When I click “Ok” on the message box above the following message box is displayed:

Selected Data with ID …
Selected Data with ID …

This message box displays the information that my C# code read when it runs through the code in the SelectData method.  In that method, a simple SELECT statement is used to select data for each column in the Demo_Always_Encrypted table.    Before this message box can be displayed, ADO.NET has to decrypt the data in SQL Server since both the BirthDate, and SSN are Always Encrypted columns.  As you can see the message box displayed the BirthDate and SSN in clear text.  In the next section I’ll verify my BirthDate and SSN columns are actually encrypted inside of SQL Server table Demo_Always_Encrypted.

Verifying Iteration #2 Inserted Encrypted Data

In order to verify that ADO.NET encrypted the data that I send to my insert stored procudure I will run the following SELECT statement in a SSMS query window:

SELECT [ID]
      ,[LastName]
      ,[FirstName]
      ,[BirthDate]
      ,[SSN]
  FROM [DEMO].[dbo].[Demo_Always_Encrypted];

When I run this code I get the following results:

ID        LastName   FirstName BirthDate                               SSN
--        --------   --------- --------------------------------------- --------------------------------
1         Larsen     Gregory   0x011065959924B9E9DE4AA77626F5CCF08...  0x0130F5B22FAD807B90653ED072B14...

 

Here you can see that the BirthDate and SSN column are encrypted. 

Validating Admin/DBA’s Can’t See Encrypted Data

Remember I had to add the “Column Encryption Setting = Enabled” parameter to my connection string in order for my .NET C# program to encrypt or decrypt the Always Encrypted columns.  Well for my next test I’m going to add this connection parameter to my connection string when I bring up a SSMS Query on my VM machine.  I’m going to use the connection to verify that Admins/DBA’s can see the encrypted data.   

To do that I’ll right click on my VM SSMS query window and then hover over the “Connection” item, and when the additional window comes up I select the “Change Connection” item as shown below.  

Change Connection
Change Connection

 When I do this the following connection information window is displayed:

Connect to Database Engine
Connect to Database Engine

On this screen I click on the “Options >>” button to add my additional connection parameters.  When I click on this button the following screen is displayed:

Connect to Database Engine – Additional Connection Parameters
Connect to Database Engine – Additional Connection Parameters

I don’t enter my additional connection parameter here, but instead I click on the “Additional Connection Properties” tab, which then brings up this window:

Connect to Database Engine – Enter Additional Parameters
Connect to Database Engine – Enter Additional Parameters

Here I add the additional connection property “Column Encryption Setting = Enabled”.  Once I add that addition parameter I click on the “Connect” button to  connect me to SQL Server with column encryption enabled.  Now that I’m connected to SQL Server with this new connection parameter let’s see if I can see my super secret BirthDate and SSN by running the following SELECT statement:

SELECT [ID]
      ,[LastName]
      ,[FirstName]
      ,[BirthDate]
      ,[SSN]
  FROM [DEMO].[dbo].[Demo_Always_Encrypted];

When I run this code I get these results:

ID         LastName   FirstName  BirthDate                     SSN
---------- --------- ----------- ---------------------------- --------------------------------
1          Larsen     Gregory    2015-01-02                   555-55-5555

This isn’t good.  I can now read that encypted data within SQL Server Management Studio in clear text.   Thought the point was Always Encrypted should keep the confidential data out of the prying eyes of the DBA.  There must be something wrong in how I set up my test. 

Summary

In this article I setup a new SQL Server 2016 CTP3 database and table to store some data in a couple of Always Encrypted columns.   I then proved I could write a C# program to insert encrypted data into my table, and then read it back out with a SELECT statement to display it in clear text.  What I wasn’t successful doing was hiding the encrypted data from the admin/dba account on my actual SQL Server instance.   In Part 2 of this article I will show you what I did wrong with my first set of iteration tests of Always Encrypted, and how to set up an Always Encrypted environment where encrypted columns are not available to the admin/DBA. 

Additional Resources for Understanding Always Encrypted

 

If you want to get started learning about Always Encrypted here are a few links that might help:

Complete C# Code used in Article:

using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

// Demo of using Always Encrypted Columns
class AlwaysEncryptedDemo
{
    SqlConnection conn;
    public AlwaysEncryptedDemo()
    {
        // Instantiate the connection
        conn = new SqlConnection(
          "data source=WIN-B95MVF88UT5;initial catalog=Demo;integrated security = False; 
             Column Encryption Setting=Enabled; User ID = Greg; Password = Test;");
    }

    // call methods that demo Always Encrypted
    static void Main()
    {
        AlwaysEncryptedDemo scd = new AlwaysEncryptedDemo();
        scd.Insertdata();
        scd.Selectdata();
    }

    public void Insertdata()
    {
        try
        {
            // Open the connection for Insertion 
            conn.Open();

            // Constructed command to execute stored proceudre
            string insertString = @"dbo.Insert_Always_Encrypted";

             // Declare variable tho hold insdert command
            SqlCommand icmd = new SqlCommand(insertString, conn);

             //set command type to stored procedure
            icmd.CommandType = CommandType.StoredProcedure;

             // Set value of LastName
            SqlParameter paramLastName = icmd.CreateParameter();
            paramLastName.ParameterName = @"@LastName";
            paramLastName.DbType = DbType.AnsiStringFixedLength; ;
            paramLastName.Direction = ParameterDirection.Input;
            paramLastName.Value = "Larsen";
            icmd.Parameters.Add(paramLastName);

            // Set value of LastName
            SqlParameter paramFirstName = icmd.CreateParameter();
            paramFirstName.ParameterName = @"@FirstName";
            paramFirstName.DbType = DbType.AnsiStringFixedLength; ;
            paramFirstName.Direction = ParameterDirection.Input;
            paramFirstName.Value = "Greg";
            icmd.Parameters.Add(paramFirstName);

             // Set value of Birth Date
            SqlParameter
            paramBirthdate = icmd.CreateParameter();
            paramBirthdate.ParameterName = @"@BirthDate";
            paramBirthdate.SqlDbType = SqlDbType.Date;
            paramBirthdate.Direction = ParameterDirection.Input;
            paramBirthdate.Value = "2015-01-02";
            icmd.Parameters.Add(paramBirthdate);

            // Set value of SSN
            SqlParameter
            paramSSN = icmd.CreateParameter();
            paramSSN.ParameterName = @"@SSN";
            paramSSN.DbType = DbType.AnsiStringFixedLength;
            paramSSN.Direction = ParameterDirection.Input;
            paramSSN.Value = "555-55-5555";
            paramSSN.Size = 10;
            icmd.Parameters.Add(paramSSN);
 
            // Exexute Insert 
            icmd.ExecuteNonQuery();
            MessageBox.Show("Inserted Demo Record With BirthDate=" + paramBirthdate.Value + "SSN=" + paramSSN.Value);

        }
        finally
        {
            // Close the connection
            if (conn != null)
            {
                conn.Close();
            }
        }
    }
    public void Selectdata()
    {
        try
        {
            // Open the connection for Selection 
            conn.Open();

             // Read Encrypted data 
            string selectString = @"SELECT ID, LastName, FirstName, BirthDate, SSN FROM [dbo].[Demo_Always_Encrypted] ";
            SqlCommand scmd = new SqlCommand(selectString, conn);
            SqlDataReader dataRead = scmd.ExecuteReader();
            while (dataRead.Read())
            {
                MessageBox.Show("Selected Data with ID=" + dataRead["ID"].ToString() +
                                " LastName=" + dataRead["LastName"] +
                                " FirstName=" + dataRead["FirstName"] +
                                " BirthDate =" + dataRead["BirthDate"].ToString() +
                                " SSN=" + dataRead["SSN"].ToString());
            }
        }
        finally
        {
            // Close the connection
            if (conn != null)
            {
                conn.Close();
            }
        }
    }
}

See all articles by Greg Larsen

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved