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

This post has been republished via RSS; it originally appeared at: MSDN Blogs.

A quick lesson learned from the field when dealing with TDE (Transparent Data Encryption).

After running something like


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



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

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.