One of the new features in SQL Server 2016 is Always Encrypted, which allows us to select certain columns in a table that contain sensitive information that we want to protect. Once we encrypt those columns, the data is protected on disk, in memory, and even in SSMS and SQL queries. The data will be encrypted in the application and will be protected throughout the round trip to the database. This is quite a bit more secure than TDE(Transparent Data Encryption) where the data was only protected on disk.
I’m using SQL Server 2016 CTP 3.
Setup is straightforward. First, we’ll create a table and enter some test data:
if object_id('dbo.EncryptTest') is not null
drop table dbo.EncryptTest;
create table dbo.EncryptTest(
CustomerId int not null identity(1,1) primary key,
CustomerName varchar(20) not null,
SSN char(9) not null
insert into dbo.EncryptTest(CustomerName, SSN) values('Customer 1', '000000000');
insert into dbo.EncryptTest(CustomerName, SSN) values('Customer 2', '000000001');
insert into dbo.EncryptTest(CustomerName, SSN) values('Customer 3', '000000002');
select * from dbo.EncryptTest;
In this example, we’ll encrypt the SSN column of the test table using the Encryption Wizard in SSMS.
In SSMS, right-click on table and select ‘Encrypt Columns…’ to start the Wizard.
After the splash page we’ll go to the ‘Column Selection’ screen. We’ll check the SSN column to encrypt. We’ll need to select an Encryption Type, either Deterministic or Randomized. With Deterministic, the same values will result in the same encrypted value, where with Randomized the same values will have different encrypted values. Determinitisic will allow equality operations and grouping, where Randomized may be a little more secure with no patterns to discern.
Here we can also generate a column encryption key or select one that already exists.
The next screen is ‘Master Key Configuration’. This will create a master key to protect the column encryption key. A new master key can be created or an existing one used. This master key is stored outside of the database, so we’ll need to also select somewhere to store it, either the ‘Windows certificate store’ on the local server or use the Azure Key Vault to store it in the cloud.
The next screen is ‘Validation’ where we select to either setup encryption now or to generate a Powershell script to set it up later. The setup needs to occur at a time where the table won’t be in use so that new records won’t be inserted. We’ll go ahead and run the setup now.
Next is the ‘Summary’ screen, so we’ll click ‘Finish’ to start the setup.
After the setup has completed, we can select from our test table and see that the SSN column has been encrypted.
Originally I had a unique constraint set up on the SSN column as well as a check contraint to ensure that 9 characters were entered. When going through the wizard, I had to remove the unique contraint to even be able to select the SSN column for encryption. The first time I got all the way through the wizard the setup failed and gave me an error saying that the encrypted column couldn’t have a check contraint.
In retrospect this makes sense, if SQL Server can’t read the encrypted data then it wouldn’t be able to enforce any sort of contraints on the table other than datatype and nullabllity.