Encrypt a Column in SQL Server 2017

Image for post
Image for post

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:

Image for post
Image for post
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:

Image for post
Image for post
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:

Running the following query should show us our new key:

Image for post
Image for post
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:

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

Image for post
Image for post
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:

Let’s check if our key was created:

Image for post
Image for post

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.

Image for post
Image for post
New encrypted column ready and waiting

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

Now if we take a look at our users table:

Image for post
Image for post

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:

Image for post
Image for post

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!

Written by

Married, software-developing, backpacking, photographing, cooking, father of three. https://www.codecutting.com

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store