The tempdb system database can be found in any SQL Server instance. It is a database that is utilized by all user databases that are created within the instance. This system database is where temporary user objects such as temporary tables, variables, cursors, spools, sorting and row version activities are performed. There is no doubt that this system table is extremely valuable in the functionality of the entire SQL Server instance.

When Transparent Data Encryption (TDE) is enabled on any user database the primary elements of the user database that is encrypted is the physical database (.mdf) and log (.ldf) files. A byproduct of this feature being implemented is the encryption of the tempdb system database.

On the surface the automatic encryption of the tempdb system database might be a bit confusing and raise some concerns about how the non-TDE enabled user databases might interact with tempdb. To clarify this, let's start at the beginning:

On a fresh SQL Server instance, TDE is enabled on a user database by performing the following steps:

1. Create a Database Master Key (DMK) using the master database.
2. Create a Certificate using the master database.
3. Create a Database Encryption Key (DEK) using the target database.
4. Set Encryption to "ON" using the target database.

We will explore these four steps in depth in later blog entries; but for now, let's now review some system views that reveal what has transpired as the result of these four steps using the master database.

SELECT * FROM SYS.SYMMETRIC_KEYS
The execution of this statement reveals the master keys that exist in the database. The one that we are focusing on, as the result of the steps listed above, is the one that contains the value of "##MS_DatabaseMasterKey##" in the column titled "name". This indicates the DMK that was created in the first step above.

SELECT * FROM SYS.CERTIFICATES
The execution of this statement reveals the certificates that exist in the database. The certificate that contains the certificate name that was used in the second step above in the column titled "name" is the one that we are focusing on in this example. Notice that the column titled "pvt_key_encryption_type_desc" contains the value of "ENCRYPTED_BY_MASTER_KEY". This value is in reference to the DMK noted above. Also, note the binary value that is contained within the column titled "thumbprint". We will refer to this in the next query.

SELECT * FROM SYS.DM_DATABASE_ENCRYPTION_KEYS
The execution of this statement reveals the database encryption keys (DEK) that exist for the instance. You will notice that even though we created only one DEK there are two listed in the query results. If you cross reference the value within the column titled "database_id" to the sys.databases view you will notice that one of the DEKs is for the target database in which we enabled TDE and the other is for the tempdb system database. The dm_database_encryption_keys view offers some interesting insight to these DEKs; specifically the column titled "encryptor_thumbprint".

The encryptor_thumbprint column reveals the binary reference to the certificate that protects the DEK. The value that has been captured for the target database in which TDE was enabled can be directly referenced to the "thumbprint" column revealed in the sys.certificates view.

The encryptor_thumbprint value for the tempdb system database contains the value of "0x", which is the binary equivalent to a zero-length value (not null). This means that the DEK that has been created or the tempdb system database is completely independent from the key hierarchy that was established for the target database in which TDE was enabled. In addition, a review of the columns titled "key_algorithm" and "key_length" contain the values of "AES" and "256" for the tempdb system database regardless of the algorithm utilized for the target database.

Since the tempdb system database is referenced by all user databases within the instance, regardless of whether TDE as been enabled or not, all databases must perform the encryption and decryption process when using the tempdb system database utilizing its DEK.

The inherent behavior of the tempdb system database is that when the SQL Server instance is stopped and started that the tempdb is dropped and recreated. This can be verified by performing the following steps:

1. Stop the SQL Server instance.
2. Start the SQL Server instance.
3. Execute "SELECT * FROM SYS.DATABASES" using the master database.

The result of the third step of this process will reveal that the column titled "create_date" for the tempdb system database will be approximately the date and time of the start of the SQL Server instance. When the dm_database_encryption_keys view is executed, the DEK for the tempdb will still be included in the results and the column titled "create_date" will reflect a recent date and time. This illustrates that when the tempdb system database is recreated so is its DEK.

There is a lot that can be discussed on this specific aspect of TDE alone and it certainly presents some interesting questions in regard to performance, mirroring, backup and recovery strategies. While TDE's purpose is to provide protection of a specific databases' files there are affects to the instance as a whole and requires careful consideration and testing before implementing into a production environment.