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!

Filed under: SQL SERVER, TDE Tagged: Encryption, SQL server, SQL Server 2014, SQL Server vNext, TDE, TSQL