Infrastructure at your Service

One of the top new features of SQL Server 2016 is the Always Encrypted functionality. Always Encrypted provides that data, store in a database, remains encrypted the all times there are in the database. There is a complete separation between persons who own the data and person who manage it. Only persons who own the data can see plain text data and person like DBAs, sys admins or privilege logins cannot have access to the data.

Data are encrypted and decrypted in flight between the database and the client application inside a Client Driver on the client side.
The client manages encryption keys which are stored outside of SQL Server.
Let’s start to explain via a concrete example.

I have a table in my database with sensitive data and I want to encrypt those data to avoid that Database Administrator can see plain text data for Credit Card number, Account Number and Account Balance:


To enable encryption columns right click on our Customer table and select “Encrypt Columns…”:


An introduction screen appears explaining how Always Encrypted works, click Next:


The next screen shows all the columns of our table and we have to select which ones we want to encrypt. Here the Credit card number, the account balance and the account number:


We need to choose the Encryption Type between two options which are described if we click in the “Encryption Type” text:


I will choose Randomized for Credit Card Number and Account Number as I don’t want to query  on those columns and it is more secure. But I choose Deterministic for Account Balance as I want to filtering by equality with this field.
Please note that Deterministic encryption use a column collation with a binary2 sort order for character columns, so the collation for our char columns will be changed from French_CI_AS to French_BIN2 in my example.

For the column Encryption Key which are the key which will encrypt the data for each column, I will let the wizard generated one for me. I will also use the same column encryption key for all my encrypted columns:


The next screen is used for the master key configuration. The column encryption key is used to encrypt the data in the column and the column master key protect, encrypt the column encryption key. I will, here also, use an auto generated column master key which will be a self-signed certificate saved in the Windows Certificate Store:


In the Run Settings screen, first of all, a warning points the fact that if the encryption/decryption is executed during some insert statement, there could be a risk of data loss.
You could normally choose if you want to run the encryption immediately or if you want to generate a PowerShell script to do it later. For the time being the PowerShell generation could not be chosen… So I will run it now:


A summary explains the operation that will be proceeded. A column master key will be generated and saved in the Windows Certificate store, the column encryption key will be also generated and used to encrypt my three columns:


My columns have been encrypted:


Now , I go back to my query, refresh it and I see that I cannot anymore read plain text for my three columns but instead I have varbinary encrypted blobs:


There is just a problem in this demo… In fact, I have created my column master key certificate with a self-signed certificate in the context of the current user.
So, this user have access to my certificate and can decrypt the encrypted columns if we add in the connection string “Column Encryption Setting=Enabled”,  which is the change required to use Always Encrypted.


Now, as the certificate used to create the Column Master Key is available the encrypted columns appear in plain text…


We will have to separate physically the certificate used to create my column master key from the SQL Server machine used to create the Column Master Key and the Column Encryption Key.
I will show you how to do that in a future blog post.


One Comment

Leave a Reply

Stéphane Savorgnano
Stéphane Savorgnano