Always Encrypted Architecture

Deploying Always Encrypted

• 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

Creating a Database to Hold Always Encrypted Table  

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
   ) );

Test Iteration #1 - Inserting Always Encrypted Data

As first test of Always Encrypted will call 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 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_database_name = 'DEMO')
column_encryption_key_name = 'Demo_Always_Encrypted_CEK',

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

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

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

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

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

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

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

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

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

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

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

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

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.

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.  

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

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 …

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:

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

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

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

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

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:

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.