SQL Server Always Encrypted - Enabling, Querying and Modifying Data

By: Aaron Bertrand | Updated: 2023-05-09 | Comments (1) | Related: > Encryption

Problem

In 2015, during the SQL Server 2016 beta, I explored a new feature in this article, Always Encrypted. This feature finally allowed us to encrypt data at rest and on the wire, and I showed how beneficial this was and how much more secure your data could be. I also explained that, as a new feature, some limitations made it difficult to use and, sometimes, impossible to adopt.

Several major versions of SQL Server later, how has this feature evolved, and is it easier to use today?

Solution

Some of the more helpful enhancements around Always Encrypted have been introduced in client tools. For example, encrypting a column using SQL Server Management Studio (SSMS) is much easier now.

Enabling Always Encrypted

Let's start by creating a simple table:

CREATE DATABASE AlwaysEncrypted;
GO
USE AlwaysEncrypted;
GO
CREATE TABLE dbo.Employees
(
  EmployeeID  int IDENTITY(1,1) NOT NULL,
  FirstName   nvarchar(64)      NOT NULL,
  LastName    nvarchar(64)      NOT NULL,
  BirthDate   date              NOT NULL,
  Salary      int               NOT NULL,
  NationalID  char(9)           NOT NULL,
  CONSTRAINT  PK_Employees PRIMARY KEY (EmployeeID)
);

We'll insert a single row:

INSERT dbo.Employees(FirstName, LastName, BirthDate, Salary, NationalID)
  VALUES(N'A', N'B', '19770101', 50000, '992993994');

Then we can apply Always Encrypted using a wizard in SSMS. Right-click the table and select "Encrypt Columns…"

You'll first be presented with a list of columns to choose from, whether to use deterministic or randomized encryption (explained in the previous tip) and which column encryption key(s) to use. The column encryption key is there to encrypt the actual data. In theory, you could use a different key for different columns, but to keep things simple, we'll use a single key here:

Column Selection screen in Always Encrypted wizard

An important note is that string columns may need to be converted to a collation compatible with encryption, e.g., Latin1_General_BIN2.

Next, you'll be asked for the master encryption key, which protects the column key(s). The master key needs to be stored in a trusted key store. In the previous tip, I used the local certificate store, which made everything work fine when connecting from SSMS on the same machine. You'll want to use a central certificate store or Azure Key Vault for remote access. In this case, we'll use Azure Key Vault. You'll need an active Azure subscription, a key vault, and a user or identity who either is in the Key Vault Crypto Officer role (if using role-based access policies) or has get, list, create, unwrap key, wrap key, verify, and sign permissions (if using the permissions access policy model). Further details can be found here: Tutorial: Getting started with Always Encrypted.

Master Key Configuration screen of Always Encrypted wizard

If you're applying encryption to columns in an existing table, there is a warning about performing this work during a maintenance window, just in case:

While encryption/decryption is in progress, write operations should not be performed on a table. If write operations are performed, there is a potential for data loss. It is recommended to schedule this encryption/decryption operation during your planned maintenance window.

Otherwise, you can click Next and Finish, and the encryption process should complete quickly on the sample table we've created.

Querying Data

With the table created, you can launch a new query window in SSMS against the database and run a simple SELECT query. You'll see that the encrypted columns return binary values:

SSMS is not set up by default to support Always Encrypted. You could override the behavior in older versions with a custom connection string attribute. But now, it's made much easier with a simple checkbox in the Options panel for a connection. You can right-click the text editor portion of the query window, choose Connection > Change Connection, press Options, and on the Always Encrypted tab, check the box "Enable Always Encrypted":

Always Encrypted tab of the Connection dialog

Keep in mind that this will likely change your connection to master, so remember to issue another USE command or change the database here on the Connection Properties tab.

When you run the same query again, you'll be prompted to connect to Azure, which is only complicated if you have many accounts and/or subscriptions like me. If the connection is successful, you should see decrypted values in the output:

You'll be similarly prompted the first time you query any such data in any subsequent SSMS session.

For deterministic columns, we can perform equality searches. For example:

DECLARE @BirthDate date = '19780101';
SELECT * FROM dbo.Employees WHERE BirthDate = @BirthDate;

Results of equality search against deterministic column

The blue squiggly line presents a tooltip with the following text explaining how the variable will be parameterized: @BirthDate will be converted to a Microsoft.Data.SqlClient.SqlParameter object with the following properties: SqlDbType = Date, Size = 0, Precision = 0, Scale = 0, SqlValue = 1/1/1978 12:00:00 AM

But we can't perform range or inequality searches:

DECLARE @BirthDate date = '19780101';
SELECT * FROM dbo.Employees WHERE BirthDate >= @BirthDate;

Result:

Msg 33277, Level 16, State 2
Encryption scheme mismatch for columns/variables '@BirthDate', 'BirthDate'. The encryption scheme for the columns/variables is (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'AlwaysEncrypted') and the expression near line '5' expects it to be RANDOMIZED, a BIN2 collation for string data types, and an enclave-enabled column encryption key, or PLAINTEXT.

And for random columns, we can't even perform equality searches:

DECLARE @Salary int = 60000;
SELECT * FROM dbo.Employees WHERE Salary = @Salary;

We get a similar result here:

Msg 33277, Level 16, State 2
Encryption scheme mismatch for columns/variables 'Salary', '@Salary'. The encryption scheme for the columns/variables is (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'AlwaysEncrypted') and the expression near line '5' expects it to be DETERMINISTIC, or RANDOMIZED, a BIN2 collation for string data types, and an enclave-enabled column encryption key, or PLAINTEXT.

And remember that if your string columns used a case-insensitive collation before being encrypted, equality searches will now be case-sensitive.

Manipulating Data

Performing inserts that include Always Encrypted columns is a little trickier. If we try a similar insert statement to the previous one:

INSERT dbo.Employees(FirstName, LastName, BirthDate, Salary, NationalID)
  VALUES(N'C', N'D', '19780101', 60000, '892994994');

Management Studio doesn't know how to handle the plain text values, yielding a message like this:

Msg 206, Level 16, State 2
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 = 'AlwaysEncrypted') collation_name = 'SQL_Latin1_General_CP1_CI_AS'

Instead, we need to use parameters or local variables with matching types. For example:

DECLARE @BirthDate  date    = '19780101',
        @Salary     int     =  60000,
        @NationalID char(9) = '892994994';
INSERT dbo.Employees(FirstName, LastName, BirthDate, Salary, NationalID)
  VALUES(N'C', N'D', @BirthDate, @Salary, @NationalID);

To run this statement, we need to enable SSMS to properly parameterize variables. If this option isn't already enabled, we'll be prompted to do so (Configure Always Encrypted using SQL Server Management Studio):

Prompt to enable parameterization of local variables

Once enabled, we can now insert the data as expected. If we query the table in the same session, we see:

Decrypted values after adding a second row

If we move to a session where Always Encrypted is not enabled, we'll once again see encrypted values:

Other Considerations

Encrypted data is not the easiest to work with, and I didn't even get into how to manage this data from an application that would also have to authenticate to Azure to work with keys stored there, especially if the applications and/or the databases are not in Azure. So, always weigh whether Always Encrypted is the right mechanism to protect your data.

Consider whether information like date of birth – commonly used for functionality that is not sensitive, like monthly birthday announcements – needs to be encrypted or protected in other ways (like column permissions or dynamic data masking). Perhaps you can lock down the column using permissions, exposing the month alone in a more accessible way (for example, a computed column).

You can also consider whether a new addition in SQL Server 2019, secure enclaves, can help by making encrypted columns more feature-rich and accessible. Many of the limitations of Always Encrypted columns – like being unable to match patterns or sort – are because decryption happens on the client. The secure enclaves enhancement provides a way to perform some operations in a safe area of memory on the server (guaranteed safe through attestation), removing many restrictions that make adoption harder. You can read more about secure enclaves (Configure and use Always Encrypted with secure enclaves) and ways to simulate attestation – for example, in a test or staging environment – here and here.

Next Steps

See these tips and other resources:

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-05-09

Always Encrypted: Enhancements in SQL Server