Encrypt a Column in SQL Server 2017

If you have data, perhaps it’s patient data, or social security numbers or credit card numbers or some other sensitive bit of information it can be very important that you store that data safely and in a way that it can’t easily be exposed to those who shouldn’t see it.

One way of protecting that data is by encrypting it at rest using column level encryption. SQL Server provides a fairly simple way to do this that I’ll run through today.

Let’s say you have a [users] table in your database that looks something like this:

Sample Users table

That SSN column might not be something we want just anybody with read-access to see so let’s make a couple changes.

The Service Master Key

First, in order to encrypt/decrypt any data we need to make sure the database has a master key. Each SQL Server instance has a single master key. This master key is the key that rules all the other encryption that happens within the instance. This is not the key you will be using to encrypt your data. This key is used to encrypt and decrypt the other keys that you will use later on.

Fortunately, SQL Server 2017 creates this key for you during installation. To check if you already have a master key you can run the following command:

The master key result should look like this:

Service Master Key Exists!

The Database Master Key

Stepping a level in from the instance we reach the database where our encrypted data will live. At this point we want to create another master key called the Database Master Key. (If you hadn’t noticed yet this is building a hierarchy)

The Database master key is the root of all encryption with, you guessed it, a specific database. Creating the key is fairly simple. Simply run the following command:

use mytestdb;
create master key encryption by password = ‘<some crazy strong password’;

Running the following query should show us our new key:

use mytestdb;
select * from sys.symmetric_keys;

Database Master Key Exists!

The Certificate

Now that we have the chain of master keys down to our database we need to create a specific key to encrypt our data in our table. Because we’d like the encryption/decryption to be fairly quick we are going to encrypt using a symmetric key (AES 256 to be exact).

In order to use a symmetric key we must provide a mechanism for encrypting the new key. We will be encrypting our key by using a self-signed certificate that derives from the chain of master keys we created earlier.

To create our certificate we run the following T-SQL:

use mytestdb;
create certificate MySSNCertificate with subject = ‘MY SSN Test Certificate’;

Again a simple query shows we have successfully created the cert:

use mytestdb;
select * from sys.certificates;

Our SSN cert exists!

The Encryption Key

Now that our certificate is ready to encrypt our new key we can type the command to finally create the encryption key we will use:

use mytestdb;
create symmetric key SSNSymKey with algorithm = AES_256 encryption by certificate MySSNCertificate;

Let’s check if our key was created:

use mytestdb;
select * from sys.symmetric_keys;

TADA!

Let’s Get Encrypting!

Ok, so now we have an encryption key so how do we encrypt our data? Well first off our encrypted data needs a column. We need a varbinary column so we won’t reuse the previous column. Let’s add a new one called SSN_Encrypted.

use mytestdb;
alter table [Users] add SSN_Encrypted varbinary(160);

New encrypted column ready and waiting

Next we need to encrypt the SSN data and store the result in the Encrypted column:

use mytestdb;
-- Open our encryption key
open symmetric key SSNSymKey decryption by certificate MySSNCertificate;
-- Encrypt the data
update [Users] set [SSN_Encrypted] = EncryptByKey(Key_Guid(‘SSNSymKey’), [SSN]);
-- Close our key
close symmetric key SSNSymKey;

Now if we take a look at our users table:

Encrypted!

Ok, so that was maybe a lot to take in so let’s break it down.

We need to first ‘open’ the key for use. We do this using the `open symmetric key` command followed by the key name and information on how we can decrypt the key (using our cert we made earlier). If you don’t run the open command first then encryption will return nulls instead of encrypted values.

The update statement is pretty straight forward, but the decryption bit breaks down like this: EncryptByKey(keyId, dataToEncrypt). We got the keyId using the key_guid built-in function and we are encrypting the SSN column data.

Last we want to close our key since we are done with it.

Now we can easily clear out or drop the old column data.

Getting the Data Back Out

Once the data is encrypted we’ll at some point want to get the data back out again. We’ll do that using the DecryptByKey function:

use mytestdb;
-- Open the key for use like before
open symmetric key SSNSymKey decryption by certificate MySSNCertificate;
-- Decrypt our data on the way out
select *, convert(nvarchar, DecryptByKey([SSN_Encrypted])) as SSN_Decrypted from [Users]
-- Close our key like before
close symmetric key SSNSymKey;

Decrypted!

Again this is a little more complicated so here’s the breakdown.

Just like with encrypting we need to open our key for use. Again if we don’t open the key then decryption will just produce nulls. Similarly, if we don’t have permission to use the key then we also get nulls and not errors. This is what an unauthorized user will receive when attempting to decrypt our data.

The decryption itself is quite simple DecryptByKey(encryptedData); It doesn’t even require a keyId. However the data we get back isn’t quite how we like it since it’s varbinary so we use the convert(type,data) function to convert the varbinary data to varchar text.

There you have it. Any user who logs in without the proper permission will no longer be able to view our SSN Data. To give another user access to the key we use the grant alter on symmetric key::SSNSymKey TO MyOtherUser; command.

Happy Encrypting!