5. Verifying TDE
Once the implementation of TDE is complete there are a few ways you can verify that these steps indeed succeeded.
5.1 Using Dm_Database_Encryption_Keys
Dynamic management views (DMV) are built-in views
that provide metadata regarding the settings, health and properties of
SQL Server instances and databases. The sys.dm_database_encryption_keys
DMV presents information about the database encryption keys used in a
given database, as well as the encryption state of the database.
NOTE
Database encryption keys are only utilized for
the benefit of the TDE feature of SQL Server 2008; therefore this DMV
is not available in SQL Server 2005.
Through the use of a query in which the sys.dm_database_encryption_keys DMV and the sys.databases catalog view are joined through the database_id column, we are able to determine the success of the TDE implementation, as demonstrated in Listing 8.
A return value of "1" for the is_encrypted column of the sys.databases catalog view indicates that the database has been encrypted through TDE.
The value of the encryption_state column from the sys.dm_database_encryption_keys
DMV reflects whether or not the encryption process is complete. A value
of "3" indicates that the encryption process is complete. A value of
"2" in this column indicates that the encryption process is in
progress. The percent_complete column from the same DMV
indicates the progress of the encryption process. This column only
reflects a value other than "0" when the database encryption state is
in the process of changing (being encrypted or decrypted).
In this sample query, I added the key_algorithm and key_length columns to illustrate an interesting dynamic in regard to the TempDB database, as shown in the results in Table 1.
Table 1. Results of TDE verification query.
| name | is_encrypted | encryption_state | percent_complete | key_algorithm | key_length |
|---|
| tempdb | 0 | 3 | 0 | AES | 256 |
| HomeLending | 1 | 3 | 0 | AES | 128 |
| model | 0 | NULL | NULL | NULL | NULL |
| master | 0 | NULL | NULL | NULL | NULL |
| msdb | 0 | NULL | NULL | NULL | NULL |
As previously noted, the encryption of the TempDB is a byproduct of implementing TDE on any given database within an instance of SQL Server. The is_encrypted column for our HomeLending database contains the value of "1" which indicates that it has been successfully encrypted; but the TempDB contains the value of "0", while the values in the other columns indicate that encryption has taken place. This is because the TempDB database is encrypted outside of the established TDE key hierarchy.
This is further emphasized by the algorithm that is used to encrypt the TempDB database. As you will recall, the creation of the database encryption key for the HomeLending database was designated as AES_128, which uses a key length of 128 bits. The results of this query show that the TempDB database is actually using a key length of 256 bits.
The reason for the separate encryption process lies in the inherent behavior of the TempDB database; when the SQL Server instance is stopped and started the TempDB database is dropped and recreated. This can be verified by performing the following steps:
Stop the SQL Server instance.
Start the SQL Server instance.
Execute SELECT * FROM SYS.DATABASES, using the Master database.
The result of the third step will reveal that the column titled CREATE_DATE for the TempDB database will be approximately the date and time that you restarted the SQL Server instance. When the sys.dm_database_encryption_keys DMV is executed, the database encryption key for the TempDB database will still be included in the results and the column titled CREATE_DATE will also reflect the time that the instance was restarted. This illustrates that when the TempDB database is recreated so is its database encryption key.
At first glance the comparison of the CREATE_DATE columns of the sys.databases and sys.dm_database_encryption_keys DMV may raise concern since they do not match; but consider that the sys.dm_database_encryption_keys DMV reflects the date and time in Greenwich Mean Time (GMT) while the sys.databases
catalog view reflects the date and time according to your time zone.
Depending on your location this may appear to be in the future or in
the past. In my case, being on Eastern Standard Time (EST) in the
United States the sys.dm_database_encryption_keys DMV CREATE_DATE is five hours into the future.
