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
It “might” throw you off. Would you not expect to see is_encrypted set to 1 for TempDB?
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;
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
I much prefer that!