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.
If you query sys. dm_database_encryption_keys, the encryption state column will tell you whether database is encrypted or not.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With