Monthly Archives: September 2015

Testing Always Encrypted

Best Part of All This:

It’s Without a GUI!

And I Show You Getting Data Back Out!

So SQL Server 2016 is coming out soon, and I had a chance to play around with the latest CTP version, 2.3 recently.  When I say I had the chance to play around with it recently, I mean I volunteered at the last second to be a replacement speaker for the Nashville SQL Server User’s Group that I help run.

One of the demos I had for it was Always Encrypted.  I’m not going to go into the internals of it, but wanted to share with you the scripts I setup and ran to get it going.

Before I get started, here’s a big shout out to Aaron Bertrand.  Most of what you’ll see for the insert here is almost exactly like the example he writes about in the article below.  The only difference is I’m using powershell because I hate writing APIs and GUIs.

Here’s Aaron’s link; it really helped out and provided most of the example you’ll see below!

The first thing you need is an encrypted column.

In order to have an encrypted column, you have to have an encryption key.  For Always Encrypted, this means going into the security tab of your database and then -> Always Encrypted Keys -> Column Encryption Keys, and right clicking it and selecting “New Column Encryption Key”


I’ve named my column encryption key “MoreCowbell”.  Give it whatever name that pleases you.  Call it “MY PRECIOUS” if you want.  I believe everybody needs more cowbell, so I’ve named mine thus.  Next you’ll need to select your key definition source.  In the “Column Master Key Definition” drop down, select “New…”, and a list of Certificates will show up on your screen.


If you don’t have one, or want to create a new one, select “Generate Self-Signed Certificate”  Give your Column Master Key Definition a name.  I’ll go with something outrageous again, calling it “CMKDN”.  Press OK and you’re done with this part.

Now you can create your column in a table.  Here’s my table script:
CREATE TABLE dbo.Encryptedtable
datvarchar nvarchar(50)  COLLATE  Latin1_General_BIN2

The column has *got* to be collated with BIN2.  I used Latin1_General_BIN2 because that’s what I read in the example below on MSDN.  If you have a different collation you’d rather use, I won’t hate.  As long as it’s BIN2.  Oh and it can’t be null.

I used the encryption_type of “deterministic”, because I want to make sure it works.  Theoretically though, you should be able to use “randomized”.  It’s more secure, but test and look into the type you’d like.

Now we have the actual inserting part.

When I thought about how I wanted to demo this, I was terrified.  Was I going to have to write some sort of .NET API?

Then I started thinking about it…was there some other tool I could use to pass in and instantiate .NET objects?

I decided I could use powershell instead.  No GUI for me! Hooray!  Here’s my powershell script.  MAKE SURE YOU INCLUDE “Column Encryption Setting=enabled” in your connection string.  If you don’t, you’ll end up getting datatype errors.  THAT’s how you’ll know you’re doing it wrong.:

$insertmevalue = read-host ‘UMAD?’

$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = “Server=’.\sql2016′;Database=’AEDB’;Column Encryption Setting=enabled;Integrated Security=True;”

$sqlcmd = New-Object System.Data.SqlClient.SqlCommand
$sqlcmd.Connection = $conn
$sqlcmd.CommandText = “insert into dbo.encryptedtable (datvarchar) VALUES (@insertval)”
$sqlcmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter(“@insertval”,[Data.SQLDBType]::NVarChar, 50)))
$sqlcmd.Parameters[0].Value = $insertmevalue


Important to note that this will prompt you for an insert value.  Query your table, and these are the results after several runs:


If you fire up an Extended Event session (I just used a simple query template), you’ll see that the inserted value is already encrypted.


Why is it already encrypted?  Because it’s Always Encrypted; see what I did there?

So How Do I Do Analysis on This Witchcraft?

Now for my next trick, I’ll show you how to get the data back out for analysis.  I haven’t seen a way of doing this yet, and it even includes just using SSMS!  Maybe I just haven’t looked hard enough.

You’ll need to have the certificate you created on the machine you’re running SSMS on.

Ready?  OK, new SSMS connection window, options, additional connection parameters.  Add in the same string you did in the powershell script “Column Encryption Setting=enabled”:


WHEW THAT WAS HARD.  Now I can do a select star and get the results:


I can even do a truncate table!


Tweet me if you have questions.  Resources!


Here’s Aaron’s link again; it really helped out and provided most of the example!