Always Encrypted: Enhancements in SQL Server
By: Aaron Bertrand | Updated: 2023-05-09 | Comments (1) | Related: > Encryption
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?
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:
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.
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.
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":
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;
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;
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.
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):
Once enabled, we can now insert the data as expected. If we query the table in the same session, we see:
If we move to a session where Always Encrypted is not enabled, we'll once again see encrypted values:
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.
- See these tips and other resources:
- SQL Server 2016 Always Encrypted
- Configure Always Encrypted using SQL Server Management Studio
- Develop applications using Always Encrypted
- Always Encrypted with secure enclaves
- Enable Always Encrypted with Secure Enclaves in SQL Server Management Studio
- Configure and use Always Encrypted with secure enclaves
- Plan for Always Encrypted with secure enclaves in SQL Server without attestation
- All Encryption tips
About the author
View all my tips
Article Last Updated: 2023-05-09