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


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.)

Comments (0)

Skip to main content