Database Encryption Key (DEK) management

  • Article

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.

DEK Storage:

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.

DEK's encryptor:

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:

 

 

 

DEK's encryptor:

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.

    Insider Information:

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.

What's next?

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.

Comments

  • Anonymous
    June 14, 2010
    The comment has been removed

  • Anonymous
    August 04, 2010
    Hi Zubair,I read your article but I don't understand most of it simply because I'm very very new to SQL Server. My previous db experience was only with MS Access.Say if I use TDE on my database and the database files (mdb, log, etc..) are copied by some other people. Will they be able to see the data if they attach the database files to their SQL server?Thanks.

  • Anonymous
    October 05, 2010
    Hi Paul,The short answer is No. They would need the encryptor (Certificate or the Asymmetric key) to decrypt the database.TDE encrypts the database using DEK which is encrypted by a certificate. This certificate is stored in master database and is required to decrypt the database. If someone copies your database and log files they will need the certificate (with private key) or the asymmetric key to restore it on their server. Note that the database files are encrypted so they won' be able to see you data if they even try to directly open the file.

  • Anonymous
    November 02, 2010
    Good information about the encryption scan.

  • Anonymous
    January 13, 2011
    The comment has been removed

  • Anonymous
    January 18, 2011
     The information in sys.dm_database_encryption_keys includes the thumbprint for the object protecting the DEK, unfortunately we missed to add an encryptor_type column that would explicitly define the nature of this protection. The following query should be a good starting point. It is not a 100% solution since there is a very slim possibility (close to 0, but it exists) of thumbprint collision between a certificate & the EKM-based asymmetric key (the HSM would define this thumbprint).SELECT deks., certs.name as encryptor_name, 'certificate' as encryptor_type FROM sys.dm_database_encryption_keys deks, sys.certificates certs WHERE deks.encryptor_thumbprint = certs.thumbprintUNIONSELECT deks., asmk.name as encryptor_name, 'asymmetric key' as encryptor_type FROM sys.dm_database_encryption_keys deks, sys.asymmetric_keys asmk WHERE deks.encryptor_thumbprint = asmk.thumbprint I hope this information helps,-Raul Garcia SDE/T SQL Server Engine

  • Anonymous
    January 25, 2011
    So, just to clarify.  When you run the ALTER DATABASE ENCRYPTION KEY and replace the DEK with a new key, what happens to the backups that were taken previous to the key rotation.  Do the old DEK and new DEK stay in the database so they can both be used to decrypt backups?Additionally, what if you also want to replace your Certificate that is used to encrypt the DEK?  My assumption is that I would create the new certificate, in the master DB, then use the new certificate to encrypt the new DEK that is set up with the Alter DB Encryption key command.  Please correct me if I am thinking of this in the wrong way.  Also, my assumption is that you would still need to leave the old and new certificates in the sysdb so they would be available to decrypt the old/new DEK.  Is this correct?Thanks,Jerrod

  • Anonymous
    October 18, 2011
    Very nice representation of the dependencies of the key in TDE.

  • Anonymous
    June 26, 2015
    Hi, Is there a way I could recover the private key in clear format. To give you backgroud - I would like to switch from ServerCertificate to EKM based encryptor so I am thinking to get the public key from certificate, however not able to extract the private key (which is password protected). Once I have pub and priv key I would think of exporting them to HSM and later when setting up TDE EKM would setup TDE to use existing key pair from HSM

  • Anonymous
    June 30, 2015
    @AjayChauhan You can recover the Column Encryption Key (CEK) in plaintext programmatically by using System.Data.SqlClient.SqlColumnEncryptionCertificateStoreProvider. The following code fragments may be useful (I apologize for the lack of formatting): Step 1) Get the CMK definition path, the CEK ciphertext & algorithm from catalog views: SELECT cek.name, cmk.key_path, cekv.encrypted_value, cekv.encryption_algorithm_name  FROM sys.column_encryption_key_values cekv, sys.column_encryption_keys cek, sys.column_master_key_definitions cmk    WHERE cek.column_encryption_key_id = cekv.column_encryption_key_id        AND cmk.column_master_key_definition_id = cekv.column_master_key_definition_id; Step 2) In .Net code, use SqlColumnEncryptionCertificateStoreProvider. DecryptColumnEncryptionKey to recover the CEK plaintext: SqlColumnEncryptionCertificateStoreProvider cmkprov = new SqlColumnEncryptionCertificateStoreProvider(); byte[] plaintextKey = cmkprov.DecryptColumnEncryptionKey(cmkPath, encryptionAlgorithmId, encryptedKey); I hope this helps. -Raul

  • Anonymous
    August 05, 2015
    Great article! Thank you! As I understand from the article, DEK is decrypted only once - on boot page load, and then live in memory in decrypted form. Could you please confirm this? I also didn't find when the boot page is loaded, but I suppose that it should be loaded once on DB restore, attach and take online. Could you please confirm this too?

  • Anonymous
    December 02, 2015
    Hi, What will happen to DEK if we  unregister the EKM PRovider .Will it work if we again register the EKM Provider. What is the correct way if we need to upgrade the EKM Provider DLL . Is there any way we can skip decrypting the database

  • Anonymous
    March 04, 2016
    I've enabled TDE via EKM using Azure Key Vault and it's working swimmingly. I have however hit a complete block when it comes to restoring the DB to another server; think DR / Testing etc.I've got my asymmetric key (in the form of a .pfx file) and have uploaded it to a new Key Vault. I've created a new asymmetric key in SQL using the CREATION_DISPOSITION = OPEN_EXISTING as I did originally, and this works, however the thumbprint is different to the original value on the source server, so even though I've used the same asymmetric key in Key Vault, I still can't decrypt (i.e. restore) the DB backup on the new server. Is there any way to change the thumbprint value in my destination server, and/or tell it what value to have during creation? If I've understood it correctly, it's the actual asymmetric key (.pfx file in my case) which is doing the decryption, therefore if I can tell the destination server to use this, it should be able to decrypt the backup.