Encryption status in sys.dm_database_encryption_keys not consistent with sys.databases

Question

If I query sys.dm_database_encryption_keys, it comes back with an encryption_state of 3 (encrypted), percent_complete of 0. If I query sys.databases, the is_encrypted column has a value of 0 (not encrypted). These two seem to counter each other to me.

Is the database encrypted? sys.dm_database_encryption_keys says so, but is_encrypted in sys.databases disagrees.

Under database properties, the property Encryption Enabled is false.

I'm confused.

Elbertina

Answer 1

It seems you've got a case where a DB got encrypted by the SQL server automatically, such as in case of tempdb, once Transparent Data Encryption (TDE) was enabled. I am seeing exactly same case in my test instance of SQL Server 2012 with tempdb. MSDN: The tempdb system database will be encrypted if any other database on the instance of SQL Server is encrypted by using TDE.

is_encrypted = 0 I believe reflects the fact that DB was encrypted automatically, not by issuing ALTER command. Here is what MSDN says about this parameter: Indicates whether the database is encrypted (reflects the state last set by using the ALTER DATABASE SET ENCRYPTION clause).

percent_complete = 0 is expected when there is no ongoing encryption. From MSDN: Percent complete of the database encryption state change. This will be 0 if there is no state change.

encryption_state = 3 looks to be the ultimate hint in answering the question. MSDN: Indicates whether the database is encrypted or not encrypted, 3 = Encrypted.

MSDN TDE page itself suggests to use sys.dm_database_encryption_keys to verify if DB is encrypted or not.

And finally, here is a really handy script from John Magnabosco's post showing which DBs are encrypted with TDE and which are not (encryption_state = 3 is the teller):

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

Hopefully this makes it less confusing now.

Abisia

I know this is old, but your comment regarding tempdb is a good hint. MSDN says that you need to RESTART SQL-Server after disabling encryption on ALL databases. I ran into this myself. tempdb showed a weird state after disabling encryption on my Adventureworks sample. The MSDN trouble-shooting doc helps resync the state, and lists 3 assumptions... first one being "restarting SQL Server" I did that, and tempdb status cleared up. – Sauropod