The SQL Server Encryption Hierarchy

Introduction

SQL Server provides several ways for you to encrypt your data. You can for example use Transparent Data Encryption (TDE), which is a real turnkey no-coding-required solution to encrypt an entire database. But SQL Server also provides several ways to encrypt or hash single values, using certificates, asymmetric keys, symmetric keys or just plain passwords.

When implementing a specific security solution often many cryptographic entities are involved. For example, you could have a symmetric key to encrypt the data in a specific column. That symmetric key in turn could be protected by a certificate, which in turn could be protected by the database master key.

All those entities build a natural hierarchy that I am going to explore in this article.

The SQL Server Encryption Hierarchy Diagram

The hierarchy of encryption objects is a quite complex construct. The easiest way to understand it is probably with a diagram:

The diagram consists of three parts. The top section shows entities that exist, are stored or are controlled outside of SQL Server. The bottom section contains the data that you want to protect. That can be an entire database or a single data value.

The middle section contains the actual cryptographic entities that SQL Server provides or handles. There are 5 different entity types: The Service Master Key, the Database Master Keys, Certificates, Asymmetric Keys and Symmetric Keys. Each one of them I will cover in a later separate post.

How to Read the Encryption Hierarchy Diagram

The blue arrows indicate encryption. For example, the database master key can be encrypted by a password. What that means is that the key is stored in an encrypted form. To use it you first have to decrypt it. In SQL Server terms, decrypting a key to make it usable is called opening the key.

Some keys can even be encrypted by more than one mechanism at the same time. For example, a symmetric Key can be encrypted by a certificate as well as another symmetric key. To open such a double encrypted key, it is enough to use either the certificate or the second symmetric key.

SQL Server can create all the encryption keys by itself. However, in some situations it is required to achieve additional security by using dedicated hardware security modules (HSM) for key management and encryption operations. HSMs can be connected to SQL Server using an EKM provider, a small dll that acts as a communication interface between the HSM and SQL Server.

Such an EKM provider can provide symmetric and asymmetric keys. Those keys are not stored by SQL Server and therefore do not need additional encryption within SQL Server. Therefore, EKM provided keys do not have to be decrypted before each use either.

Summary

In SQL Server, encryption keys have to be stored encrypted themselves. That encryption can be accomplished using other keys or passwords. That way you can build a hierarchy of encryption entities. This article explored the different options available.

SQL Server Encryption Hierarchy Series

This post is part of a series exploring the different cryptographic entities available in SQL Server.
Below is a list of links to the posts that are already available.