I’m sure that we all know that SQL Server includes all sorts of interesting functionality to allow us to encrypt our data and like with all encryption techniques, that data is encrypted using keys.

In SQL Server we’ve got a number of different keys, we’ve got the Service Master Key, Database Master Keys, Symmetric Keys, Asymmetric Keys and Certificates.  These keys can be used to encrypt data but they can also be used to encrypt other keys and this is where the key hierarchy comes in.

If you google SQL Server key hierarchy you might come across a number of diagrams similar to that below

that’s all well and good but what does it really mean?

Well, to try an explain this I want to have a little look at what SQL does when it needs to decrypt our data.

For this example we’re going to imagine that we’ve got some data that we’ve encrypted using a password.  Think of the encryption routine as a safe, if we want to get our data out then we need to provide SQL Server with the password to open that safe.

SQL Server can now happily pop the password that we’ve given it into the safe and get our data for us.  That’s fine but what happens if we encrypt our data using a key?

Our SQL Server now has a problem, it can’t just decrypt (open the safe) using our password.  For this safe it’s going to need a key but were is that key?  SQL knows that keys are important and it can’t just leave them laying around anywhere, who knows who might find them.  So what does it do?

SQL protects those keys by encrypting them.  So that means in order for our SQL Server to open the safe and get our data, it first has to pop over to another safe to get the key.

So essentially, SQL needs to decrypt the key that’s encrypted the data before it can then decrypt our data.  And that’s where the idea of the key hierarchy comes in.  Let’s have a look at that diagram again and see if it makes any more sense now…

The above diagram shows an example similar to our first example, the data has simply been encrypted by a password.

The following example is slightly more complex,

In this example, our data is encrypted by a symmetric key (key 1).  This symmetric key is encrypted by another symmetric key (key 2) which is in turn encrypted by a password.  So if SQL wants to decrypt the data it’ll first need to decrypt Key 2 using a password, then use Key 2 to decrypt Key 1 and only then, when it has Key 1 decrypted can it use Key 1 to decrypt our data.

And this is what’s represented by the key hierarchy, it shows us how a key can be encrypted by a key higher in the hierarchy, that key can in turn be encrypted by another key and so on.

At the end of the day, every key in SQL Server needs to be encrypted by something higher in the hierarchy.  Up at the top of the key hierarchy we have only three things that don’t have to themselves be encrypted, passwords, the Service Master Key or a key stored in an Extensible Key Management (EKM) system.

I’ll talk about the different types of keys in another post but hopefully this has helped shed some light on the sometimes confusing subject of the SQL Server key hierarchy.