With the release of SQL Server 2016, a new feature called Always Encrypted was introduced. Always Encrypted allows you to encrypt columns at the application layer using ADO.NET, ensuring that your confidential data remains encrypted even when stored in the database. In this article, we will explore the architecture and deployment process of Always Encrypted.

Always Encrypted Architecture

The architecture of Always Encrypted involves the application layer performing column-level encryption before sending the data to SQL Server. This encryption is done by the ADO.NET drivers on the client machine. When a .NET application sends plain text data to ADO.NET, it is encrypted before being sent to SQL Server. The application only needs to change the connection string to indicate that column encryption is enabled. ADO.NET will then encrypt the Always Encrypted columns before sending the data to SQL Server and decrypt them when they are read from the database.

The architecture includes two types of keys: the Column Master Key and the Column Encryption Key. The Column Master Key is stored on the application machine in an external key store, protecting the Column Encryption Key. By keeping the Column Master Key on the application machine, SQL Server does not have direct access to it, ensuring that it cannot decrypt the Always Encrypted data. The Column Encryption Key, on the other hand, is stored on SQL Server and is used to encrypt and decrypt the Always Encrypted columns.

My Experience in Deploying Always Encrypted

To share my experience with Always Encrypted, I will walk you through the steps I took to create and store encrypted data in my first Always Encrypted table.

I started by setting up a SQL Server 2016 instance on my laptop and creating a C# Visual Studio project using .NET 4.6 framework. The C# project ran directly on my laptop, separate from the SQL Server 2016 VM. My goal was to prove that Always Encrypted could hide confidential data from DBAs with sysadmin role access to SQL Server.

The first step was to create a database to hold my Always Encrypted table. I used the following code to create the database:

CREATE DATABASE [DEMO]
CONTAINMENT = NONE
ON PRIMARY (
    NAME = N'DEMO',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\DEMO.mdf',
    SIZE = 4096KB,
    FILEGROWTH = 1024KB
)
LOG ON (
    NAME = N'DEMO_log',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\DEMO_log.ldf',
    SIZE = 1024KB,
    FILEGROWTH = 10%
);

Next, I created the Column Master Key and Column Encryption Key using SSMS on my VM machine. I expanded the “Security” item in the object explorer and selected “Always Encrypted Keys”. From there, I created the Column Master Key by right-clicking on “Column Master Keys” and selecting “New Column Master Key…”. I provided a name for the key and selected the “Window Certificate Store – Current User” as the key store. I then generated a certificate for the key.

With the Column Master Key created, I proceeded to create the Column Encryption Key. I right-clicked on “Column Encryption Key” and selected “New Column Encryption Key…”. I provided a name for the key and selected the previously created Column Master Key from the drop-down menu.

Finally, I was able to create my first Always Encrypted table. I used the following code:

CREATE TABLE dbo.Demo_Always_Encrypted (
    ID INT IDENTITY(1, 1) PRIMARY KEY,
    LastName NVARCHAR(45),
    FirstName NVARCHAR(45),
    BirthDate DATE ENCRYPTED WITH (
        ENCRYPTION_TYPE = RANDOMIZED,
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
        COLUMN_ENCRYPTION_KEY = Demo_Always_Encrypted_CEK
    ),
    SSN CHAR(10) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (
        ENCRYPTION_TYPE = DETERMINISTIC,
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
        COLUMN_ENCRYPTION_KEY = Demo_Always_Encrypted_CEK
    )
);

In this code, you can see that the BirthDate and SSN columns are always encrypted. The BirthDate column is encrypted with a RANDOMIZED encryption type, while the SSN column is encrypted with a DETERMINISTIC encryption type. It is important to note that when encrypting a string value, the Always Encrypted column should be set to a BIN2 collation setting.

By following these steps, I was able to successfully set up an Always Encrypted table in SQL Server 2016 and store encrypted data. Always Encrypted provides an additional layer of security for sensitive data, ensuring that it remains encrypted even when stored in the database.

Stay tuned for more articles on SQL Server concepts and features!