Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql-server

tde

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.

like image 783
Scott Newman Avatar asked Jan 30 '13 20:01

Scott Newman


People also ask

How do you check if database is encrypted or not?

If you query sys. dm_database_encryption_keys, the encryption state column will tell you whether database is encrypted or not.

How can check database encryption status in SQL Server?

How to Check if TDE is Enabled? After you're done, you need to confirm that Transparent Data Encryption in SQL Server is enabled for the “test” database. In the Database Properties section, go to the Options page. There, pay attention to the State area at the bottom of the window.

Which of the below encryption state indicates whether the database is encrypted?

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.


1 Answers

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.

like image 110
Rod Avatar answered Oct 06 '22 17:10

Rod