SQL SERVER 2016: Testing Always Encrypted – Part 1

One of the most exciting features of the new SQL Server 2016 is definitely Always Encrypted – a technology that allows data to be encrypted both in rest and in transit. You can learn more about Always Encrypted here: http://aka.ms/alwaysencrypted

In this article I will test Always Encrypted using my test database Resources and the SSMS 2016 as a client application  – it is the .Net Framework version 4.6 and later which is responsable for the encrypting/decrypting data so the MS SQL Management Studio can be easily used as a client as long as .Net Framework version 4.6 is the prerequisite for SSMS 2016 – (https://msdn.microsoft.com/en-us/library/mt147923.aspx).

I’m going to restore my test database Resources which I used in my previous posts to the newly installed SQL Server 2016 RC3 (Installing SQL Server 2016 RC3), encrypt a column in one of the tables and see whether the MS SQL Management Studio will be able to read the encrypted column and insert new rows to it.

Before we’ll dive into encrypting SQL data I’d like to look under the hood of the SQL Server 2016 encrypting techniques. The main idea: all encryption/decryption is done on the client side – SQL Server does not have access to the key (Column Master Key) that the client (either a user or a computer) possesses. Always Encrypted uses two types of keys: Column Master Key and Column Encryption Key. The real encryption/decpryption process uses Column Encryption Key (this key in the form of the encrypted hash is stored on the SQL Server computer), but SQL Server must first decrypt the Column Encryption Key itself before it can use this key for encrypting/decrypting data. Column Master Key is the key which is used to encrypt/decrypt Column Encryption Key. Since Column Master Key  is not stored on the computer with SQL Server installed there’s no risk that any DB administrator can view the sensitive data.

It’s worth noting that although Column Master Key is not stored on the SQL Server computer, SQL Server 2016 does  store the Column Master Key metadata (as well as the metadata for the Column Encryption Key) in its catalog views – you can use the following views to see the metadata for both keys: sys.column_master_keys for the Column Master Key and sys.column_encryption_keys and sys.column_encryption_key_values for the Column Encryption Key.

More information: https://blogs.msdn.microsoft.com/sqlsecurity/2015/07/06/always-encrypted-key-metadata/

There are two ways to enable Always Encrypted:

1) create Column Master Key (CMK) and Column Encryption Key (CEK) beforehand and then enable encryption for some database column(s) and
2) use the Encrypt Columns wizard – in case there’re no keys available it will generate the keys and encrypt the column(s).

In case 1) the command which we use for the CMK key creation – CREATE COLUMN MASTER KEY  – does NOT generate any keys, it just ‘connects’ already existing key with the metadata on SQL Server 2016, telling it where SQL Server can find the CMK.
By default SQL Server 2016 expects the CMK to be a general certificate key pair that is located either in the user or local machine certificate store. Having that said, by issuing CREATE COLUMN MASTER KEY we are telling SQL Server that it should use some certificate (e.g. its private/public key pair) from the corresponding certificate store. Of course, SQL Server can use other providers for storing CMK (https://msdn.microsoft.com/en-us/library/mt163865.aspx).
CREATE COLUMN ENCRYPTION KEY command creates the key and uploads its encrypted value to SQL Server .

In case 2) Encrypt Columns wizard first creates the certificate (by default!) – either in User’s certificate store on the client machine or in the Local Machine store –  that will be used as CMK and then  ‘connects’ it with the metadata on SQL Server computer. After that it creates CEK and uploads its encrypted value to the server.

Here’s the schema of Always Encrypted keys:


Let’s get started by restoring  the Resources database:


Suppose I want to encrypt ComputerID column of the CPUDetails table- for this I right click the column ComputerID and choose Encrypt Columns:4445

This starts the wizard:
46 47Here I must choose the encryption type – either Determenistic or Randomized (the former is less secure but allows data operations over encrypted columns, the latter is the most secure but does not allow any operations with the encrypted columns) and the Column Encryption Key which will be used for the column encryption. As you can see the wizard will be using the new autogenerated key named CEK_Auto1(New).

Since I haven’t created any CMK yet the wizard offers to generate a new one:48

By default the wizard will create a certificate (because the key store provider is set to Windows certificate store) in Current User certificate store – it means that only that user will be able to encrypt and decrypt respective columns. If you want to allow any user access to the sensitive column(s), you can choose Local Machine certificate store.

Click Next.49 50 5152

Oh, …completely forgot it… – encryption can not be applied to columns with indices!

The same is true for the columns with foreign keys:
53 54

…so it’s a good idea to check all indices and other restrictions applied to the columns that are to be encrypted:

Having checked the restrictions for the CPUdetails table I see that I can encrypt the Frequency column:

60 61 62 63 64

Clicking Alway Encrypted Wizard Log Report opens the log:

Here’s the result:

We can now use the catalog views mentioned below to see what metadata is contained in the SQL Server:

select * from sys.column_master_keys

select * from sys.column_encryption_keys

select * from sys.column_encryption_key_values
If we script these keys to the new windows we’ll see the commands which had been used to create the keys:
104 105

Part 2

%d bloggers like this: