Getting Started with Always Encrypted Part 1

  • Article

New to SQL Server 2016 are several new security features, each aimed at protecting your data in a very specific way.  Dynamic Data Masking allows you to create rules to mask data that you choose so that lower-security users do not see the actual data in the table but rather a mask of it instead.  Row-level security  allows you to create security schemes so that predicate logic can be used to allow users to see only specific data in a table.  Always Encrypted, which I'll be focusing on today, ensures that the data you choose is ALWAYS in an encrypted state – at rest and in transit.  Since the data is always in an encrypted state, you can prevent anyone from seeing the data – even the Database Administrators.  In fact, with this feature you can store your data in Azure and be assured that only your applications that retrieve data from the cloud can decrypt the data.

To begin the demo, I am going to expand my AdventureWorks2016 database and navigate to the Security folder in SSMS.  Inside the Security folder there is a folder for 'Always Encrypted Keys'.  Expanding that folder I see two additional folders: Column Master Key and Column Encryption Key.

Column Master Key – a metadata entry that represents a key and is used to encrypt the Column Encryption Key.  The Column Master Key uses a certificate on the machine, Azure Key Vault, or a Hardware Security Module to encrypt the Column Encryption Key. The CMK must be created first.

Column Encryption Key – This is the key used to encrypt the data in the SQL Server tables.  This binary key is generated by using the Column Master Key.

To create a Column Master Key, you must have the thumbprint of a certificate available.  You DO NOT (and SHOULD NOT) have the certificate on the SQL machine itself.  What I did in my case was generate the certificate on my SQL machine through the 'New Column Master Key' UI, Export the Certificate to my client machine, and then delete the certificate on my SQL Server machine.  If the certificate is left on the SQL Server machine, then the user who has access to the certificate (the certificate would have to be stored in a User or Local machine store) could then decrypt the data easily (more on this in a later post).

Recently released was a set of Powershell cmdlets that all you to separate the roles of Security Administrator and DBA.  These cmdlets allow the Security Administrator to create and load the necessary certificate while allowing the DBA to create the necessary SQL Server objects (such as the Column Encryption Key) without the need of having the certificate physically on the SQL machine.  You can read more about how to use these cmdlets here.

So, in this case I choose to 'Generate Certificate'.  The certificate is named "Always Encrypted Certificate".  Here I choose to store it in the 'Windows Certificate Store – Current User'.  I could also store it in Azure if I wanted to.  However, for our purposes I am going to delete it very soon anyway. I could also use Azure Key Vault to store the certificate. I'll show how to do this in a later post.

Here I can see that the certificate was created in the Personal  Certificates folder in the Current User store (in the Certificates snap-in in mmc).

If I were to go to my certificate that was created through the 'Create Column Master Key' wizard, I can see the thumbprint – which matches up to the KEY_PATH above (less the spaces):

The TSQL used to create the Column Master Key is below. The thumbprint for the certificate (highlighted) is used to correspond to the certificate on the client machine when communication is initiated.

Now that my Column Master Key has been created, I can use it to create (and encrypt) a Column Encryption Key.

The Column Encryption Key uses the Column Master Key to generate a varbinary key which can later be used to encrypt data in a SQL Server table.  Here I just give the Column Encryption Key a name an tell it to use the Column Master Key I created above to do the encrypting of the varbinary value.

Here is the TSQL to create the Column Encryption Key.  The value I have highlighted is the encryption key that was generated by using the Column Master Key.  It is abbreviated in this case (it's too long to print).

Since I used the method of creating the certificate on the SQL machine, at this point I can remove the certificate.  Had I went with the route of using Powershell to set up the necessary objects using role separation, I wouldn't need to do this.  But, alas - I do.

I'll first need to Export it with the private key so that I can restore it to the client machine (I'll cover this in my next post).  The main thing to make note of at this point is that the certificate is no longer needed on the SQL Server machine.  If I keep track of the TSQL statements generated from the above operations, I'll never need to have that certificate on the SQL machine again.

Next up, I will create a table that will store encrypted data.  In the TSQL table definition, I need to specify the encryption algorithm to be used as well as if the comparisons against the column will be Deterministic or Randomized.  Deterministic encryption uses a method which always generates the same encrypted value for any given plain text value. Using deterministic encryption allows grouping, filtering by equality, and joining tables based on encrypted values, but can also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column. This weakness is increased when there is a small set of possible encrypted values, such as True/False, or North/South/East/West region. Deterministic encryption must use a column collation with a binary2 sort order for character columns.  Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents equality searches, grouping, indexing, and joining on encrypted columns.

Now that my table is created, I can INSERT data into it.  However, since this table is encrypted the interaction must occur with an application that has access to the certificate I used to generate the Column Master Key.  I wrote an application to insert data into the table, which I will discuss in my next blog post. However, once the data has been inserted into the table, we can see that just viewing the data returns the encrypted form of the information.

Note:  I have sysadmin rights on this machine and the data is still returned in an encrypted manner.  More information on this coming… 

SELECT
*
FROM dbo.People

Thanks!

Tim Chapman