This post will talk about DEK, what it is and how it is securely stored and managed inside a database. Before enabling TDE a DEK must be created which is used to encrypt the contents of the database. It is a symmetric key and supported algorithms are AES with 128-bit, 192bit, or 256bit keys or 3 Key Triple DES. Once TDE is enabled on a database then the DEK is used to encrypt the contents of the database and the log. When TDE is enabled for any database on the server, TempDB is also encrypted and its DEK is managed internally by SQL Server.
Database encryption key is stored inside the database boot page; the contents of this boot page are not encrypted so the DEK has to be encrypted by another key; we call it the DEK’s encryptor. Currently SQL Server allows encrypting a DEK by either a Server Certificate or an EKM Asymmetric key. Besides the DEK, the boot page also contains other information necessary to identify and open an encrypted database.
Note that both DEK encryption options, EKM Asymmetric Key and Server Certificate have to be present outside the encrypted database for SQL Server to be able to decrypt the DEK and subsequently the database; therefore it is required that the encryptor must be present in the Master database. In case of a certificate it is strongly recommended that you backup both the certificate and the private key since losing it will mean losing all the data in an encrypted database. In case of an EKM key, the Asymmetric key resides on the HSM which makes management a little easier. In either case it is important to hold on to this encryptor as long as the database or the log is dependent on it.
When you restore or attach a TDE database on another server make sure that the encryptor is present on this server as well. In case of a certificate, restore it with its private key on this server; in case of an EKM key, the provider and the key should be available on this server as well.
Before going into further detail let’s see how all of this fits together:
The above diagram shows the basic layout of DEK in an encrypted database and how it is protected; the blue arrows indicate encryption, X àY means X is encrypted by Y. The boot page is not encrypted and contains the encrypted DEK which encrypts all the data pages. The diagram also shows previous DEK which is encrypted by current DEK, this is to handle DEK rotation.
Generating a new Database encryption Key is referred as DEK rotation, look at Alter DEK DDL for the syntax and details. Regenerating a DEK triggers an encryption scan which re-encrypts the entire database with the new DEK. The encryption scan is ‘resumable’, i.e. in case of any interruptions SQL Server will resume this encryption scan on startup. Each page’s header contains the information of about the DEK which was used to encrypt this page. When SQL Server has to decrypt a page, it looks at the page header to find out whether the current or the old DEK should be used for decryption. Therefore, the previous DEK is kept in the boot page to make the encryption scan work across server shutdown or other interruptions.
Boot page also contains information about the encryptor which helps SQL Server look it up in Master database. The certificate should have a private key and it should be decryptable on the machine, i.e. it should be encrypted by DMK which should be encrypted by SMK. For any reason, if SQL Server fails to decrypt the private key of the certificate, it won’t be able to decrypt the DEK and database. If the DEK is encrypted by an EKM key then SQL Server should be able to connect to the HSM, access the key and decrypt the key. Refer to MSDN on how to setup EKM to work with TDE.
Putting it all together:
Looking at the above diagram one can see how SQL Server opens the DEK of an encrypted database. On opening an encrypted database SQL Server first opens up the boot page which contains the DEK and the information on how to decrypt it. It then looks at the encryptor type and thumbprint, which is used to find the certificate or asymmetric key in the Master database. Once the encryptor is located, it can then be used to decrypt the DEK. Finally, this decrypted DEK is used to decrypt the actual data pages as they are read from and written to disk.
In the next entry I will discuss the encryption of the log file by TDE and why it is important to know about this. Feel free to leave feedback, suggestions or ideas for future posts around TDE.