SQL Server TDE – Is TempDB Encrypted?

A really quick one today, something that made me think for a minute and I thought it might make others think too. So you have enabled TDE  – Transparent Data Encryption (you can see these previous posts here: https://blobeater.blog/?s=tde&submit=Search) on your SQL Server database and in the back of your mind you know TempDB gets encrypted too.

If you query sys.databases, such as:

SELECT is_encrypted,name,user_access_desc FROM sys.databases
WHERE database_id = 2 OR database_id = 7

tempdnencrypt
It “might” throw you off. Would you not expect to see is_encrypted set to 1 for TempDB?

huh

Official documentation states the following for this field:

Indicates whether the database is encrypted (reflects the state last set by using the ALTER DATABASE SET ENCRYPTION clause). Can be one of the following values:
1 = Encrypted
0 = Not Encrypted

For more information about database encryption, see Transparent Data Encryption (TDE).
If the database is in the process of being decrypted, is_encrypted shows a value of 0

Some may interpret  the “reflects the state last set by using the ALTER DATABASE SET ENCRYPTION clause” sentence incorrectly. Either way the true confirmation is via this query:

 SELECT
    d.name,
    d.is_encrypted,
    dmk.encryption_state,
    dmk.percent_complete,
    dmk.key_algorithm,
    dmk.key_length
FROM
    sys.databases d
    LEFT OUTER JOIN sys.dm_database_encryption_keys dmk
    ON d.database_id = dmk.database_id;

yesitis

I actually never specifically stated to enable encryption ON for TempDB, it is not part of my actual code. So even though is_encrypted = 0, the encryption state = 3 which definitely means that TempDB is fully encrypted (notice the key_algorithm and length too).

So if you think you have missed something, don’t worry you haven’t.

Did Microsoft Change Something?

I ran all the above tests on SQL Server 2014, HOWEVER I enabled TDE on a database (called BBQ because it is 27 degrees Celsius and I should be outside) that is on Microsoft SQL Server vNext (CTP2.0) and I ran the above queries and guess what?

TempDB will show as is_encrypted = 1

vnextTDE

vnext

I much prefer that!

 

 

One thought on “SQL Server TDE – Is TempDB Encrypted?

  1. Pingback: TempDB Encryption With TDE – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s