Encryption has always been intriguing to me but seemed like it could be a very complex process to set up. However, SQL Server has made it very simple when they introduced Always Encrypted (AE) into SQL Server 2016 and Azure SQL Database. Unlike Transparent Data Encryption (TDE) which only encrypts data files and backups at rest, AE is configured on a column level and not database level. Additionally, Always Encrypted is available in Standard (and Express) Edition, starting with SQL Server 2016 SP1. You can easily encrypt a social security number (SSN) which is considered very sensitive within the United States or Salary column in a table with just a few clicks. In past versions of SQL Server, you could use cell-level encryption (CLE) perform this, but it required code changes and the keys were stored in the database, and the data was sent to the application unencrypted. Which brings us to the other benefit of AE, which is that DBAs can no longer see the unencrypted values of the data, as they could with CLE, because the column encryption key is stored outside of SQL Server.
Let’s see how you do it and walk through what each of these options means.
Using Adventure Work 2016 CTP3 (current Adventure Works download) HumanResources.Employee Table we are going to encrypt the Birthdate column.
Start by Right Clicking on the Table > Choose Encrypt Columns
It brings up a Wizard, one of the two recommend ways to configure AE. The other option is to use PowerShell.
Click Next on the Intro Screen
You will note in the example below, that it lists the columns and then shows the encryption STATE, which indicates if the column is eligible for encryption. There are several unsupported column characteristics that may make it so a column cannot be encrypted. This link to MSDN describes this in further detail. The items on this list are unsupported because they have a default constraint or a check constraint defined:
This is just an example of one of the road blocks you may encounter. So, let’s take a step back and setup an example we can easily use.
The Setup
Run the below to create a copy of the Employee table. We are doing this to make a table without any constraints.
There are two possibilities Deterministic and Randomized.
MSDN defines Deterministic encryption as always generates the same encrypted value for any given plain text value. Which means that if you have a birthdate of 01/03/1958 it will always be encrypted with the same value each time such as ABCACBACB. This allows you to index it, use it in WHERE clauses, GROUP BY and JOINS.
Randomized encryption per MSDN- uses a method that encrypts data in a less predictable manner. This makes Randomized encryption more secure, because using the example above each encrypted value of 01/03/1958 will be different. It could be ABCACBACB, BBBCCAA, or CCCAAABBB. All three encrypted values are subsequently decrypted to the same value. Since the encrypted value is random you cannot perform search operations etc. as you can with Deterministic.
In most cases, you will want to use deterministic encryption. The places where random encryption makes sense is where you have a low range of distinct values. An attacker might be able to determine what the encrypted value was by brute force attacking using a variety of parameters. Some examples of this data include birth date, blood type, or credit card verification numbers (CVV).
So, going back to our example, select deterministic from the drop down.
The next step is to choose an Encryption Key. Let’s choose CEKAUTO (NEW). This stands for Column Encryption Key. You can use the same Key for every column or choose a new one for each.
Then click NEXT
Every Encryption Key must have a MASTER KEY. This is the value that is used to protect the other column keys. In the below we are going to just go with the defaults. If you have already generated a master key in you SQL Server instance, you can choose to reuse it for any new column you add.
One of the most complex parts of encryption is determining where to store these keys and who will have access to it. You can store these keys on a client machine using a Windows Certificate store or in Azure Key store.
The next screen has a great feature and kudos to Microsoft for this add-in. You can choose to generate a PowerShell Script, so you can rerun this again, or store in your source control.
After clicking NEXT, you’re done. The wizard will create all the keys, and encrypt the selected columns.
Now if you SELECT from the table you will see the values in Birthdate are now encrypted.
Key Management in Windows Certificate Store
If you would like to see where the keys are stored within Windows, you can do so by doing the below. Go to Microsoft Management Console (type MMC your run bar Win+R). Then go to File, then Add/Remove Snap In. Certificates will be the third one down, click Add.
If you scroll back up you will note the when we created our Master Key it did so under CURRENT USER so choose My user account.
Expand Personal and Click Certificates (Key)
So, there you have it. Encryption made easy. This is only the tip of the iceberg. You need to understand how your environment will access and decrypt the data, encrypting is only part of the puzzle. I will cover how to get SSMS to decrypt the data in Part 2, in the meantime play around with it.
ALTER TABLE [HumanResources].[Employee] ADD CONSTRAINT [DF_Employee_VacationHours] DEFAULT ((0)) FOR [VacationHours] ALTER TABLE [HumanResources].[Employee] WITH CHECK ADD CONSTRAINT [CK_Employee_BirthDate] CHECK (([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate()))) GO
This is just an example of one of the road blocks you may encounter. So, let’s take a step back and setup an example we can easily use.
The Setup
Run the below to create a copy of the Employee table. We are doing this to make a table without any constraints.
SELECT * INTO [HumanResources].[Employee_AE] FROM [HumanResources].[Employee]Now again, Right Click on the Table > Choose Encrypt Columns In this case, the column we want is BirthDate, so I place a check next to it. To continue I need to Choose a Type of Encryption.
There are two possibilities Deterministic and Randomized.
MSDN defines Deterministic encryption as always generates the same encrypted value for any given plain text value. Which means that if you have a birthdate of 01/03/1958 it will always be encrypted with the same value each time such as ABCACBACB. This allows you to index it, use it in WHERE clauses, GROUP BY and JOINS.
Randomized encryption per MSDN- uses a method that encrypts data in a less predictable manner. This makes Randomized encryption more secure, because using the example above each encrypted value of 01/03/1958 will be different. It could be ABCACBACB, BBBCCAA, or CCCAAABBB. All three encrypted values are subsequently decrypted to the same value. Since the encrypted value is random you cannot perform search operations etc. as you can with Deterministic.
In most cases, you will want to use deterministic encryption. The places where random encryption makes sense is where you have a low range of distinct values. An attacker might be able to determine what the encrypted value was by brute force attacking using a variety of parameters. Some examples of this data include birth date, blood type, or credit card verification numbers (CVV).
So, going back to our example, select deterministic from the drop down.
The next step is to choose an Encryption Key. Let’s choose CEKAUTO (NEW). This stands for Column Encryption Key. You can use the same Key for every column or choose a new one for each.
Then click NEXT
Every Encryption Key must have a MASTER KEY. This is the value that is used to protect the other column keys. In the below we are going to just go with the defaults. If you have already generated a master key in you SQL Server instance, you can choose to reuse it for any new column you add.
One of the most complex parts of encryption is determining where to store these keys and who will have access to it. You can store these keys on a client machine using a Windows Certificate store or in Azure Key store.
The next screen has a great feature and kudos to Microsoft for this add-in. You can choose to generate a PowerShell Script, so you can rerun this again, or store in your source control.
After clicking NEXT, you’re done. The wizard will create all the keys, and encrypt the selected columns.
Now if you SELECT from the table you will see the values in Birthdate are now encrypted.
SELECT * FROM [AdventureWorks2016CTP3].[HumanResources].[Employee_AE]
Key Management in Windows Certificate Store
If you would like to see where the keys are stored within Windows, you can do so by doing the below. Go to Microsoft Management Console (type MMC your run bar Win+R). Then go to File, then Add/Remove Snap In. Certificates will be the third one down, click Add.
If you scroll back up you will note the when we created our Master Key it did so under CURRENT USER so choose My user account.
Expand Personal and Click Certificates (Key)
So, there you have it. Encryption made easy. This is only the tip of the iceberg. You need to understand how your environment will access and decrypt the data, encrypting is only part of the puzzle. I will cover how to get SSMS to decrypt the data in Part 2, in the meantime play around with it. 
One Response
Great article.
Important to mention as well that it’s recommended to generate the certificate on a different machine from where it will be stored. Storing the keys in Azure KeyVault is also a great option as the integration goes smoothly(most of the time).