Exploration of SQL Server 2016 Always Encrypted – Part 1-2 - Page 2

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

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.