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