Always Encrypted Setup Example

After initially being available only on Enterprise and Developer editions of SQL Server 2016, Always Encrypted was made available to all editions with SQL Server 2016 SP1. Let’s take a look at how to configure Always Encrypted and do a few tests.

Encryption Types

Always Encrypted can encrypt columns with deterministic encryption or randomized encryption. Your choice on which is better for you depends on how you plan to use the encrypted data. Deterministic encryption will produce the same encrypted value every time whereas randomized will not have the same encrypted value.

If you want to encrypt records but will also want to be querying encrypted records, you’ll want to choose deterministic for more efficient queries. Deterministic encryption will still allow point lookups, equality joins, grouping, and indexing when querying data.

If you want the highest level of protection with Always Encrypted, you’ll want to go with randomized. With deterministic, it’s possible for someone to figure out patterns of deterministic values if they have the unencrypted value of a record. This is more of an issue if the encrypted value has a low set of possible values. For example, if someone knows the encrypted value of “True” or “Yes” then they will also know the value for “False” and “No” records.

Yes and No

Now that we have some background on encryption options, let’s configure it for ourselves.

Let’s create a table to test in:

CREATE TABLE Patient 
(
	Patient_Id INT IDENTITY(1,1),
	Patient_LName VARCHAR(50) NOT NULL,
	Patient_SSN VARCHAR(15) NOT NULL,
	Patient_Vaccinated BIT NOT NULL
);
GO

And insert some test records:

INSERT INTO Patient
VALUES ('Doe', '123-45-6789', 0)
,('Johnson', '111-22-3333', 1)
,('Carter', '444-44-4444', 1)
,('Callihan', '555-00-5555', 1)
,('Lane', '999-88-7777', 0);

For our example, let’s say we want to encrypt Patient_SSN and Patient_Vaccinated. The first step to setting Always Encrypted is to right click on the table name and select Encrypt Columns:

Which takes us to the column selection screen:

We’ll go ahead and click the checkboxes next to Patient_SSN and Patient_Vaccinated. For Encryption Type, let’s choose Randomized for Patient_SSN and Deterministic for Patient_Vaccinated:

Note the yellow warning we see next to Patient_SSN regarding the column collation change:

This is column specific and will not change the overall database collation.

We can click next to check the Master Key Configuration window.

For our example we’re just going to leave those settings alone and click Next.

On the Run Settings screen we find that we can save our Always Encrypted setup scripts for later as a Powershell script or we can run them now. We’ll leave Proceed to finish now selected and click Next.

Our summary looks good so we’ll click Finish to implement changes.

If we run our original SELECT statement again, we’ll see the following results:

Patient_SSN and Patient_Vaccinated are both encrypted.

If we take a closer look at Patient_Vaccinated, we’ll see only two unique values across our five records due to the deterministic encryption.

If I know that Callihan was vaccinated, I can determine that Johnson and Carter are as well since they have the same value. This is a simple example of why deterministic is not the best approach if security is your top priority. If we follow the same steps above and change Patient_Vaccinated to randomized encryption, we’ll see this type of result when running our SELECT statement:

Much better.

If you’re encrypting data that you must query against then deterministic will probably be best. If it’s private data that you are storing and nothing else, go with randomized encryption.

Thanks for reading!