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.
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.
The bootpage image got resized during publishing. To see the full sized image either copy the image and paste it in MS Paint, MS Word, etc. OR open this link:
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?
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.
Good information about the encryption scan.
I want to know which query can i execute to see informations about the DEK in SQL Server ?
like, when i want to see an asymmetric key i put :
select * from sys.asymmetric_keys
which one for the DEK ????
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.thumbprint
SELECT 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,
SQL Server Engine
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?
Very nice representation of the dependencies of the key in TDE.
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
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.
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?
> 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?
A: Basically it is correct. It will be available in plaintext for the process, but it should be protected in memory using DPAPI.
> 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?
A: Correct. The boot page is loaded when the system needs to open the DB.
I am using EKM Asymmetric key to encrypt the DEK, asymmetric key is on HSM and when we create any table in TDE database and insert a row SQL Server call the decryption of DEK and it get decrypted by asymmetric key on HSM. Till now everything is working as expected. After the DEK is decrypted once and I am not closing the HSM session, If again any row is inserted SQL Server call the decryption of DEK whereas DEK was already get decrypted at first call.
Can you please tell me why SQL Server Decrypt call is executing at every insert command. As per my knowledge if DEK is once decrypt, it is available for use till we restart the SQL Server then why it is requesting the HSM asymmetric key every time. Can you please help me to understand the exact behavior.