Encryption Hierarchy

Hello and welcome. As with most types of encryption, Always Encrypted is based on an encryption hierarchy. In this segment, we’re going to look at this SQL Server Always Encrypted encryption pipe.

It’s important to understand that this hierarchy is unrelated to the SQL Server encryption hierarchy that starts with a master key and works its way down through database master keys and later to individual keys and certificates. Those are there to support server side encryption and Always Encrypted is client side encryption. There are only two layers to the Always Encrypted encryption hierarchy. Those two layers are the column master key and the column encryption key.

It is the job of the column encryption key to encrypt the actual data inside of tables that is deemed sensitive. It is the job of the column master key to encrypt those column encryption keys. The column encryption keys are generated by SQL Server and, after having been encrypted by the column master key, are stored among the column metadata inside of SQL Server. Each column encryption key can encrypt one or more underlying columns.

Opinions on the best practice for column encryption keys can vary. One such approach, which is common and popular, is to create one column encryption key for every column that’s going to be encrypted as we see in this example where there are three separate columns across two tables that will be encrypted, and we’ve chosen three distinct keys to perform those operations. Other people suggest that there should be one column encryption key for each table. So in this example, the table on the left, which has two columns to be encrypted, are both sharing the same red key while the other table is using this purple key.

Certainly possible, but far less popular is to create one column encryption key and let it encrypt multiple columns across multiple tables. That’s certainly allowed by the system but is not generally considered a best practice. The top layer of our encryption hierarchy is the column master key.

Column master keys are generated by the operating system and stored outside of SQL Server. This key is the one that will be presented to the appropriate client users so that they may perform cryptographic operations on the column encryption keys and, therefore, the underlying column data. It is also this key that can be denied access to the server insiders so that even they, potentially as sysadmins on the SQLs over instance, cannot decrypt column encryption key and, therefore, cannot decrypt the underlying data.

Column master keys can encrypt one or more column encryption key. Best practices for column encryption keys are a little less straightforward than column encryption keys. Column master keys will generally encrypt groups of columns via one or more column encryption keys. When choosing how many column master keys to make, consider the audience of the encrypted data.

Will all of the encrypted columns need to be visible to all the same users? If so, all the column encryption keys can be encrypted by a single column master key. If there is a split– perhaps some of the users need to be able to decrypt certain billing information like bank account numbers, but they should not be able to see medical histories that are stored in the same database. Another group may need to be able to see medical history but does not need to know anything about bank account numbers. In this case, two separate column masterpiece would make sense with each decrypting a subset of column encryption keys and then a subset of related columns and each being granted to a different group of users.

A few key points to remember when making key decisions– these keys do not supersede database permission. If someone doesn’t need access to an entire database or schema or table, just deny them permission to those options. Don’t let them have permission on the table but just not the key. That’s a nice bonus for our privileged users but should not be the default security method for all users.

Remember, if necessary, we can grant one person or group access to multiple keys. But we cannot grant one person access to half of the key. So the decision needs to be made granularly. Avoid the temptation to simply make a single column master key for every column encryption key.

This would allow for the most granular permissions to be set. But it creates an administrative nightmare, trying to manage all the column master keys. Let’s look at some examples.

In each of these examples, we’ll be looking at four encrypted columns across three tables. In this example, we chose to encrypt all of them with a single column encryption key [INAUDIBLE]. And therefore, we can only encrypt that single column encryption key with one column master key. Anyone with access to that column master key can decrypt the single column encryption key and, therefore, can read the data in all four columns. This is generally not a best practice.

In this example, we’ve encrypted the same four columns, but we’ve given each its own column encryption key. Although we’ve still only encrypted all four of them with a single column master key. This is certainly closer to a best practice because it allows us to split these later if we had to. But still, anyone have access to that column master key will be able to encrypt any of the four columns.

In this example, we’ve chosen that each table should get its own column encryption key, meaning the third key is in charge of encrypting or decrypting two columns. We’ve also split those across two different column master keys. So the users who are given access to the top column master key will be able to see the government ID, perhaps the patient and a diagnosis, while holders of the bottom key will be able to see a [INAUDIBLE] and bank account information.

This is a very realistic example that might be used in a scenario with medical information. Now that we have a handle on the hierarchy and how to choose keys, let’s investigate the types of encryption available to each column.