Encrypting sensitive within databases using SQL Server 2016 AlwaysEncrypted feature

Author: Basit A. Farooq

With the increasing number of incidents of lost and unauthorized exposure to sensitive data, database security is a vital and growing concern for many enterprises. Publicly owned enterprises that hold sensitive data such as social security numbers or credit card numbers in their databases should encrypt their sensitive data both at the database and operating system layer. This is to comply with the statutory requirements like Sarbanes-Oxley act, PCI-DSS, HIPPA for industries such as healthcare and banking. SQL Server has number of encryption features that are built into core Database Engine and can be used with minimal effort to encrypt or decrypt sensitive data within databases. The SQL Server data encryption features include transparent data encryption, column-level encryption, encryption of SQL Server objects definitions, backup encryption, encryption of SQL Server connections, database file-level encryption through Windows EFS and BitLocker drive encryption. These encryption features provide strong security for data at the database and OS levels, preventing the likelihood of unauthorized disclosure of confidential information, even if the SQL Server infrastructure or databases are compromised. The downside to using any of these SQL Server data encryption methods is that you cannot directly access encrypted data or make changes to it. To perform operations on encrypted data, you have to decrypt the data first, meaning data does not remain in encrypted form throughout its lifecycle. Moreover, in order to implement these, you require reworking of application or database code.

Fortunately, the next version of Microsoft SQL Server, SQL Server 2016 addresses these issues and makes data encryption easier through its new AlwaysEncrypted feature. This feature uses an enhanced ADO.NET client-side library and cipher text to encrypt and decrypt data. When you encrypt data using this feature, you can perform operations on encrypted data without decrypting the data first. That’s because the actual process of encrypting and decrypting data is handled in the database driver level. This means that sensitive information encrypted using this feature will not be in plain text. So, unlike other SQL Server data encryption capabilities, this feature ensures that your data will be kept encrypted at rest and in motion.

AlwaysEncrypted feature encryption modes

AlwaysEncrypted feature supports two encryption modes: randomized encryption and deterministic encryption. The difference between the two modes is that deterministic encryption method ensures it always generates the same encrypted value for any given plain text value. This is useful because allows equality lookups, joins and group-by based on encrypted values. However, the downside of this mode is that it allows unauthorized users to guess information about encrypted values by examining patterns in the encrypted column. Therefore, it is recommended to use randomized encryption because it encrypts data in less predictable manner, which is more secure.

Prerequisites AlwaysEncrypted security feature

To implement this feature you require two types of keys: column master keys (CMKs) and column encryption keys (CEKs). The purpose of CMKs is to protect your CEKs, and CEKs is to encrypt sensitive data stored in database columns. You can use the same CEK to encrypt all columns of table. The information about the keys can be retrieved from system catalog views. You should back up a CEKs in a secured, trusted location on network.

Creating CMK and CEK using SSMS 2016

The following are the steps to create the CMK and CEK using SSMS 2016:

Create CMK by using SSMS 2016

Using Object Explorer in SQL Server 2016, expand the Databases folder, then the database in which you want to provision AlwaysEncrypted keys. Next, expand the Security folder, and then expand the Always Encrypted Keys folder. Right-click on the Column Master Key Definitions folder, and then choose New Column Master Key Definition. This opens a New Column Master Key Definition window, as shown in figure below:

In the Name box, enter a name of a new CMK. Next, specify the name of the key store provider for the key store containing the CMK. You have three options here: Windows Certificate Store – Current User, Windows Certificate Store – Local Machine, and Azure Key Vault. For the CMK, the best option is to use a certificate stored either in your Local Machine certificate store or in Azure Key Vault. For this demo, I’m creating the certification in Current User store. In here, you can also create self-signed certificate by clicking the Generate Certificate button. Finally, click OK, to create a CMK in the database.

Create CEK by using SSMS 2016

In the Always Encrypted Keys folder, right-click on the Column Encryption Keys folder, then click New Column Encryption Key. This opens up a New Column Encryption Key window.

In the Name box, enter a name of a new CEK and define its corresponding CMK. Click OK to create your new CEK.

Creating CMK and CEK using Transact-SQL script

You can use CREATE COLUMN MASTER KEY and CREATE COLUMN ENCRYPTION KEY statements to create CMK and CEK. Here is the example of how you can use both statements:

Example of creating CMK using Transact-SQL script

The general syntax for this statement is as follow:

For example, in the SSMS 2016 query editor, type and run the below CREATE COLUMN MASTER KEY statement to create the CMK.

Example of creating CEK using Transact-SQL script

The general syntax of this statement is as follows:

For example, in the SSMS 2016 query editor, type and run the below CREATE COLUMN ENCRYPTION KEY statement to create the CEK.

Creating a table with encrypted columns

Now that we have fulfilled the prerequisites for AlwaysEncrypted feature, our next step is to create the table with encrypted columns. Remember that when creating the encrypted table, you must use one of the BIN2 collations for any string data type columns that are using deterministic encryption. Other than that, the syntax for CREATE TABLE Transact-SQL statement is similar to the one you normally use to create ordinary table. That’s because the syntax for specifying encryption on a column is pretty much straight forward. For example, in the SSMS 2016 query editor, type and run the below CREATE TABLE Transact-SQL statement to create dbo.Employee_AlwaysEncryptedDemo table with encrypted columns.

Note here that currently AlwaysEncrypted feature of SQL Server 2016 CTP 3.2 release only supports only one encryption algorithm, that is, AEAD_AES_256_CBC_HMAC_SHA_256.

Testing the Always Encrypted security feature

Now that we have created a table with encrypted columns, it’s time to insert some data to table, so we can verify if the copied data is encrypted. For this task, I designed a simple Windows Form based application in Visual Studio 2015 that will allow me to populate and query this table. This Window Form base application looks as follows:

For example, as shown above, to enter the employee’s record, you first need to specify values for the National ID number, Login ID, PayCode, Daily Rate and Date of Birth fields. You should then click on the Submit button to insert the row into the table. The following is the code behind the Submit button.

As you can see in the above code snippet that database connection string now includes a Column Encryption Setting attribute, which tell that column data must me encrypted. Rest of the C# code is the same as the one normally be used to insert data into ordinary table.

To view the table data, you can use the Load Table button that will query the table and display the result set in Data Grid View control of form. Here is the code behind this button:

Again, with the exception of Column Encryption Setting attribute in database connection string, rest of the C# code looks similar to the one that you will use to query the data in ordinary table. When you click this button, the code behind this button will execute, and the table data will loaded into the Data Grid View control. For example, when I clicked this button, it queries the table and displays the result set Data Grid View control, as shown in following figure:

To verify the data is properly encrypted, query your encrypted table. For example, when I queried the dbo.Employee_AlwaysEncryptedDemo table, it returned the result set as shown in figure below.

As you can see in the above figure, the data in the encrypted columns is displayed as binary data.