Encryption with symmetric keys & certificates

Posted On: In: Microsoft SQL Server

Recently I was asked to look into the possibility of encrypting some of the column level data that is held within a SQL Server instance, not knowing where to start and not running SQL Server 2017 I set about looking for options, one of which was to use Symmetric Keys & Certificates to encrypt the data and in this post I am going to demonstrate how I did that and what I learned.

First up I created a table that would potentially hold sensitive information I could use for demonstration purposes.

In this example, I am going to make use of Patients. (None of the data used in this example is real)

I created a master key within the database, using a strong password, recording this somewhere safe is mandatory else the data will be lost.

I needed a self-signed certificate to secure all the data with.

Once I had created the master key and certificate I could go ahead and build up the Symmetric Key

Inserting Some Data

Once I had the master key, certificate, and Symmetric Key in place I could go ahead and insert some dummy data into our patient’s table.


This is where the magic happens.

First I need to ask SQL Server to initialize the Symmetric Key, which is decrypted using the certificate I created.

Now I need to update the sensitive columns by encrypting the data and copying that encrypted data into the column marked “_encrypted”

Once completed I need to tell SQL Server to close up the Symmetric Key.

As you can see, the data has been encrypted

Drop & Roll

Now that the sensitive data has been encrypted, I can drop the plain text columns

Let’s Have A Read

The above example shows how to read the data from the encrypted columns.

As you can see, the data is returned as expected.

Insert Some More

The above example shows how to go about adding more data into the encrypted columns.

Finally, using the above you can see that PatientNumber UR102345679 was added successfully, encrypted and read back by SQL Server.

Performance wise this would be extremely horrible, it isn’t recommended to encrypt a primary key column, in production, there would be another column that would be the primary key and live within the index however this is for demonstration purposes only.

Backup Them Keys

It is a really good idea to back up the certificate, you can do this easily with DbaTools

In addition, you may want to back up the master key too

This will prompt for a password, once provided, the master key will be backed up.