So as you may know, SQL 2016 reached RTM on June 1st, and as such are now able to take advantage of the new “Always Encryption” within your environments. The encryption works by using multiple keys to ensure that not even the SQL Server itself can decrypt the data without the key. The two keys are:

  • Column Master Key
  • Column Encryption Key

The column Master Key is stored away from the SQL Server on an application server that would be connecting to SQL. This key is used to protect the other key, the “Column Encryption Key“. The column Encryption Key is stored within the SQL Server, and is used to encrypt and decrypt the “Always Encrypted Columns“. For this to all work, the application which is my case is normally SharePoint, has to support .NET Framework 4.6, as that is what actually does the hard work using the keys.

The keys are managed through the actual database that you wish to use it on.

To create the keys you simply right click on the container.

To actually create them you need to first create the “Master Key“, then the “Colum Key“.

Once you have given it a name, you need to choose the store you wish to use to hold the master key. I chose “Current – User” for now. Next you need to click the “Generate Certificate” button or you could have selected on that already existed if you have a root Certificate Authority that you use. Your new key will then be created, listed and selected.

Now we have the master we can create the column key, making sure we select the created master key for the “Column Master Key“.

Once created the keys are listed within the SQL Management Studio.

Now that we have the keys we can either run T-SQL to encrypt the columns we wish to use, or we can select “Tasks” then “Encrypt Columns” on the database.

T-SQL

Wizard

The wizard allows for you to select the columns to encrypt, the type and then the key to use.

One thing to also note is that the collation will be changed for a field and set it to “Latin1_General_BIN2“.

The wizard will then complete the conversion and then hopefully complete successfully.

Once it is done you can create a stored procedure for adding items into the table for testing.

I ran mine with the following values:

Once ran you will probably receive the following error:

“Msg 206, Level 16, State 2, Procedure DEV_INSERT_INTO_ENCRYPTED_TSQL, Line 0 [Batch Start Line 2]

Operand type clash: varchar is incompatible with nvarchar(25) encrypted with (encryption_type = ‘RANDOMIZED’, encryption_algorithm_name = ‘AEAD_AES_256_CBC_HMAC_SHA_256’, column_encryption_key_name = ‘DEV_ALWAYS_ENCRYPTED_COLUMN’, column_encryption_key_database_name = ‘DEV_ENCRYPTION’)”

This is by design as the encryption process is done at the .NET level not T-SQL Level. This would require us to write some .NET C# code to then insert the values into the table.

More details to come, for now you can read some further information here:

https://msdn.microsoft.com/en-us/library/mt163865.aspx
https://msdn.microsoft.com/en-us/library/mt459280.aspx
https://msdn.microsoft.com/en-us/library/mt621539.aspx
https://msdn.microsoft.com/en-us/library/mt607048.aspx
https://msdn.microsoft.com/en-us/library/mt653971.aspx