共用方式為

DEK and the Log

  • 發行項

In my previous post I talked about DEK management and how it is stored in the database. In this post I will try to give an overview of how the database log file is encrypted by TDE and what are the implicataions of key rotations (DEK or encryptor changes) on the log file.

TDE encrypts the database log file along with the database to protect the entire database at rest. Also note that TDE also encrypts tempdb when one or more of the databases on the server are encrypted, this is to protect data leakage via temporary objects. Some basic knowledge of the Log and its logical structure will help in understanding this post.

Logical Structure of the Log file:

We will discuss the logical (not physical) structure of the log file since it is easier to understand log encryption that way. Logically, a log file consists of a series of virtual log files (VLF) and each VLF has its own header. The tricky part with the log file is that we cannot encrypt the entire log file in one single sweep like the database file, therefore each VLF is encrypted separately and the encryptor information is placed in the VLF header. When the log manager has to read a particular VLF (let's say for recovery) then it uses the encryptor information in the VLF header to locate the encryptor and decrypts the VLF.

To completely understand some of the implications of log encryption, we'll look at the state of the log as we enable encryption and then later on change the encryptor or the DEK. Finally, we'll see how the log manager uses these VLFs to recover a database.

Unencrypted Log:

Imagine the following series of blocks as the logical log file, where each block represents a VLF. Initially, we are in VLF1 and the current LSN is somewhere inside VLF1

Encryption Turned ON:

When TDE is enabled on the database, the current VLF is filled with non-operational commands and a new VLF (VLF2) is created. As mentioned earlier, each VLF has one header which contains the encryptor information, so anytime the encryptor information changes the log is rolled over to the next VLF boundary. The next VLF will have the new DEK (DEK_1) and the thumbprint of the encryptor of the DEK in the header. Any additions to the log file will be added to VLF2 and will be encrypted.

 

When VLF2 is full, a new VLF will be created as usual, but since encryption is on so the new VLF will have the DEK and its information in its header and it will be encrypted as well.

 

VLF Header

VLF header contains information very similar to the database boot page. Essentially, it contains the encrypted DEK, the encryptor type and the thumbprint of the encryptor:

 

DEK or encryptor change:

If a new DEK is generated or the encryptor of the DEK is changed, the log is rolled over to the next VLF boundary and the new VLF (VLF4) will have the new DEK and encryptor information. Let's assume a new DEK (DEK_2) was generated using ALTER DATABASE ENCRYPTION KEY REGENERATE… DDL. VLF3 will be filled again with non-operational commands and VLF4 will be created which will be encrypted by the new DEK.

 

Decrypting VLFs:

In case of recovery or rollback, SQL Server may have to traverse the log file, i.e. the VLFs. Since each VLF has its own header, it can be independently decrypted. Now let's assume that SQL Server has to decrypt all the VLFs from 1 to 4. VLF1 is unencrypted so it will be read as is. VLF2 is encrypted by DEK_1, even though the current DEK of the database is DEK_2. Since the VLF header contains the encrypted DEK along with the encryptor's information, SQL Server can decrypt the DEK and the VLF. Same thing happens for VLF3; finally for VLF4 the DEK's header contains DEK_2 and its encryptor's information, so VLF4 will be decrypted as well.

Comments

  • Anonymous
    December 07, 2010
    Is there any way to get the current VLF structure of a log file, and the VLF encryption status (ie: is it encrypted, encryptor thumbprint, etc.)?  I know I can get this information for databases from the sys.dm_database_encryption_keys view.
  • Anonymous
    March 25, 2011
    No, there are no DMVs that I'm aware of which expose this information about the Log.
  • Anonymous
    October 18, 2011
    very helpful information.
  • Anonymous
    October 22, 2014
    Hi,Do user need separate permission to access TDE database?
  • Anonymous
    December 18, 2014
    TDE Databases have no different permissions than any other database.