Lesson Learned #72: Is my database still encrypted (TDE)

First published on MSDN on Feb 11, 2019
A quick lesson learned from the field when dealing with TDE ( Transparent Data Encryption ).

After running something like
ALTER DATABASE [AdventureWorks] SET ENCRYPTION OFF
One quick spot where people usually will look for information if a database is encrypted or not is sys.databases
SELECT database_id, name, is_encrypted
FROM sys.databases




Even though it shows the state as 0 (Not Encrypted), it still might not be completely decrypted .





As documented at https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql

is_encrypted - Indicates whether the database is encrypted (reflects the state last set by using the ALTER DATABASE SET ENCRYPTIONclause). Can be one of the following values:
1 = Encrypted
0 = Not Encrypted
If the database is in the process of being decrypted, is_encrypted shows a value of 0 . You can see the state of the encryption process by using the sys.dm_database_encryption_keys dynamic management view.

You need to check using sys.dm_database_encryption_keys looking for encryption_state = 1
SELECT database_id, DB_NAME(database_id), encryption_state
FROM sys.dm_database_encryption_keys




https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-database-encryption-keys-transact-sql

encryption_state - Indicates whether the database is encrypted or not encrypted.
0 = No database encryption key present, no encryption
1 = Unencrypted
2 = Encryption in progress
3 = Encrypted
4 = Key change in progress
5 = Decryption in progress
6 = Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)