Advanced database encryption with SQL Server Always Encrypted!

The strongest encryption methods currently available in SQL Server and Azure SQL!

In the previous article, Database encryption becomes transparent with SQL Server TDE the second one in this series of articles dedicated to SQL Server Encryption features, we described how to protect data by encrypting it at the I/O level when data is at rest. This article introduces another encryption feature, first known as Column Encryption, and later renamed Always Encrypted.

Always Encrypted is a feature designed to protect confidential data, such as credit card numbers, national or regional identification numbers, political affiliation, etc. stored in on-premises SQL Server instances or in the cloud with Azure SQL. Always Encrypted allows you to encrypt sensitive data within client applications and never reveal the encryption keys to the SQL Server Engine. This technology provides a separation between those who own the data and can view it and those who manage it but shouldn’t have access to that information, whether they are on-premises database administrators, cloud database operators, or other highly privileged unauthorized users.

Creating the AlwaysEncryptedDB database

The examples below illustrate how to get started with Always Encrypted. We will see how to encrypt columns referring to sensitive data and how to query those encrypted columns. We will use the AlwaysEncryptedDB database and the table dbo.Person, that stores sensitive data in the SocialSecurityNumber, CreditCardNumber, and Salary columns.

The following T-SQL script does:

• AlwaysEncryptedDB database setup
• The creation of the dbo.Person table
• Entering some test data

USE [master];
GO

CREATE DATABASE [AlwaysEncryptedDB]
 ON  PRIMARY 
 (
   NAME = N'AlwaysEncryptedDB'
   ,FILENAME = N'X:\SQL\DBs\AlwaysEncryptedDB.mdf'
   ,SIZE = 8192KB
   ,FILEGROWTH = 65536KB
 )
 LOG ON 
 (
   NAME = N'AlwaysEncryptedDB_log'
   ,FILENAME = N'X:\SQL\DBs\AlwaysEncryptedDB_log.ldf'
   ,SIZE = 8192KB
   ,FILEGROWTH = 65536KB
  )
 WITH LEDGER = OFF
GO

USE [AlwaysEncryptedDB];
GO

CREATE TABLE dbo.Person
(
  ID INTEGER IDENTITY(1, 1) NOT NULL
  ,FirstName NVARCHAR(64) NOT NULL
  ,LastName NVARCHAR(64) NOT NULL
  ,SocialSecurityNumber CHAR(11) NOT NULL
  ,CreditCardNumber CHAR(19)
  ,Salary MONEY NOT NULL
);
GO

INSERT INTO dbo.Person (FirstName, LastName, SocialSecurityNumber, CreditCardNumber, Salary)
VALUES
  ('Rob', 'Walters', '795-73-9838', '1111-2222-3333-4444', $31692)
  ,('Gail', 'Erickson', '311-23-4578', '5555-6666-7777-8888', $40984);

SELECT * FROM dbo.Person;
GO

The contents of the dbo.Person table appears as shown in the following picture.

Encrypt columns that store sensitive data!

SQL Server Management Studio offers a wizard that allows you to easily configure Always Encrypted for columns that store sensitive data by configuring:

• A column master key
• A column encryption key
• Encryption type for selected columns

To start the encryption process using SSMS (the latest version is recomended), navigate to the dbo.Person table of the AlwaysEncryptedDB database, right-click the table, choose Encrypt Columns, and launch the Always Encrypted wizard as the following picture illustrates.

Selecting the Next button in the Introduction window, you will be able to select the columns you want to encrypt. For this example, we will choose deterministic encryption for the SocialSecurityNumber and CreditCardNumber columns; we will choose random (non-deterministic) encryption for the Salary column. Leave CEK-Auto1 (New) as the column encryption key for both columns. Because key not exist, it will be generated by the wizard. The picture below illustrates the configuration to be applied.

The wizard warns that the COLLATE of the SocialSecurityNumber and CreditCardNumber columns will be changed from SQL_Latin1_General_CP1_CI_AS to Latin1_General_BIN2 because actually Always Encrypted isn’t supported for the text (varchar, char, etc.) columns with collations other than binary-code point (_BIN2) collations when using deterministic encryption. Selecting the Next button, the Master Key configuration window will be displayed. The Master Key configuration window allows you to configure a new key and it allows you to configure the archiving mode by choosing from the options:

• Azure Key Vault (recommended type if the database is located in Azure)
• Windows certificate store

For this example, we will use the Windows certificate store.

Pressing the Next button, the window displayed tells you that the dbo.Person table will be locked during the encryption process, and it is recomanded to schedule the activity during the maintenance hours. The Run Settings window is show in the picture below.

By pressing the Next button, the summary window will be show to examine the configuration settings and proceed with the encryption of the selected columns. At the end of the process, the Summarizing window shows the result of the procedure, as illustrated in the following picture.

Query encrypted columns

Let’s use SQL Server Management Studio to simulate the client application that manages the AlwaysEncryptedDB database. We open a new Query window, and after specifying the credentials to connect to the SQL Server instance, select the Always Encrypted tab and make sure that the Enable Always Encrypted (column encryption) option is disabled as shown in the following picture.

Let’s run the query to extract the entire contents of the dbo.Person table. Working with the Enable Always Encrypted (column encryption) option set to off, SQL Server Management Studio will not be able to decrypt the data stored in the encrypted columns; the following query will return the encrypted data.

USE [AlwaysEncryptedDB];
GO

SELECT * FROM dbo.Person;
GO

The contents of the dbo.Person table are show in the following picture.

Now let’s change the connection settings to view the decrypted data. After specifying the credentials to connect to the AlwaysEncryptedDB database, we must select the Always Encrypted tab to activate the Enable Always Encrypted (column encryption) option shown in the following picture.

Let’s repeat the previous SELECT to extract the entire contents of the dbo.Person table, because we are now connected to SQL Server with Always Encrypted enabled, SQL Server Management Studio will attempt to decrypt the data stored in the encrypted columns using the previously created cryptographic keys.

USE [AlwaysEncryptedDB];
GO

SELECT * FROM dbo.Person;
GO

The contents of the dbo.Person table are show in the following picture.

Now let’s try to insert a new record in the dbo.Person table with the following INSERT statement.

USE [AlwaysEncryptedDB];
GO

INSERT INTO dbo.Person (FirstName, LastName, SocialSecurityNumber, CreditCardNumber, Salary)
VALUES
  ('Janice', 'Galvin', '327-89-2514', '9999-1111-2222-3333', $38115);
GO

The INSERT fails with the following error message.

Msg 206, Level 16, State 2, Line 68

Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'AlwaysEncryptedDB') collation_name = 'SQL_Latin1_General_CP1_CI_AS'

When a query tries to insert data into encrypted columns or when it attempts to filter data based on one or more encrypted columns (with deterministic encryption), passing values or T-SQL variables corresponding to the encrypted columns is not supported. Inserting values into encrypted columns is allowed only using parameters.

We must modify the INSERT statement using parameters for the encrypted columns and retry the operation.

USE [AlwaysEncryptedDB];
GO

DECLARE
  @SocialSecurityNumber CHAR(11) = '327-89-2514'
  ,@CreditCardNumber CHAR(19) = '9999-1111-2222-3333'
  ,@Salary MONEY = $38115;

INSERT INTO dbo.Person (FirstName, LastName, SocialSecurityNumber, CreditCardNumber, Salary)
VALUES
  ('Janice', 'Galvin', @SocialSecurityNumber, @CreditCardNumber, @Salary);
GO

SQL Server Management Studio will present the following window to ask the user if he wants to activate the Enable Parameterization for Always Encrypted option. Pressing the Enable button INSERT statement will be executed successfully.

The following picture shows the full content of the dbo.Person table after INSERT statement, the new record is show.

All details on how queries involving encrypted columns work are described at this link.

How to remove encryption

To remove encryption from one or more columns previously encrypted with Always Encrypted, repeat the wizard by selecting PlainText in the Encryption Type column as shown in the following picture.

Summary

Always Encrypted is one of the strongest encryption methods currently available in SQL Server and Azure SQL. It is recomended to encrypt only the strictly necessary columns considering the following points of attention:

• Choose the most appropriate type of encryption between Deterministic and Random by evaluating the pros and cons!
• Analyze the impacts due to the possible change of COLLATE for the encrypted columns, more details here
• Analyze existing Limitations, more details here
• Analyze possible impacts on client applications (parametric queries, connection strings, supported drivers), more details here

Enjoy encryption with SQL Server Always Encrypted!