Home
%3CLINGO-SUB%20id%3D%22lingo-sub-369199%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%2372%3A%20Is%20my%20database%20still%20encrypted%20(TDE)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-369199%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Feb%2011%2C%202019%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20A%20quick%20lesson%20learned%20from%20the%20field%20when%20dealing%20with%20TDE%20(%20%3CSTRONG%3E%20Transparent%20Data%20Encryption%20%3C%2FSTRONG%3E%20).%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20After%20running%20something%20like%20%3CBR%20%2F%3E%20ALTER%20DATABASE%20%5BAdventureWorks%5D%20SET%20ENCRYPTION%20OFF%20%3CBR%20%2F%3E%20One%20quick%20spot%20where%20people%20usually%20will%20look%20for%20information%20if%20a%20database%20is%20encrypted%20or%20not%20is%20%3CSTRONG%3E%20sys.databases%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20SELECT%20database_id%2C%20name%2C%20is_encrypted%20%3CBR%20%2F%3E%20FROM%20sys.databases%20%3CBR%20%2F%3E%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89119i1688AF2BD28794D4%22%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Even%20though%26nbsp%3Bit%20shows%20the%20state%20as%200%20(Not%20Encrypted)%2C%20%3CSTRONG%3E%20it%26nbsp%3Bstill%20might%20not%20be%20completely%20decrypted%20%3C%2FSTRONG%3E%20.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20As%20documented%20at%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fsystem-catalog-views%2Fsys-databases-transact-sql%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fsystem-catalog-views%2Fsys-databases-transact-sql%3C%2FA%3E%20%3CBR%20%2F%3E%3CP%3E%3CEM%3E%20%3CSTRONG%3Eis_encrypted%20%3C%2FSTRONG%3E%20-%20Indicates%20whether%20the%20database%20is%20encrypted%20(reflects%20the%20state%20last%20set%20by%20using%20the%26nbsp%3BALTER%20DATABASE%20SET%20ENCRYPTIONclause).%20Can%20be%20one%20of%20the%20following%20values%3A%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%201%20%3D%20Encrypted%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%200%20%3D%20Not%20Encrypted%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20If%20the%20database%20%3CSTRONG%3E%20is%20in%20the%20process%20of%20being%20decrypted%2C%26nbsp%3Bis_encrypted%20shows%20a%20value%20of%200%20%3C%2FSTRONG%3E%20.%20You%20can%20see%20the%20state%20of%20the%20encryption%20process%20by%20using%20the%26nbsp%3Bsys.dm_database_encryption_keys%20dynamic%20management%20view.%20%3C%2FEM%3E%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20You%20need%20to%20check%20using%20%3CSTRONG%3E%20sys.dm_database_encryption_keys%20%3C%2FSTRONG%3E%20looking%20for%20%3CEM%3E%20%3CSTRONG%3E%20encryption_state%20%3D%20%3C%2FSTRONG%3E%20%3C%2FEM%3E%20%3CSTRONG%3E%201%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20SELECT%20database_id%2C%20DB_NAME(database_id)%2C%20encryption_state%20%3CBR%20%2F%3E%20FROM%20sys.dm_database_encryption_keys%20%3CBR%20%2F%3E%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89120iCDCDEC137AAD0012%22%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fsystem-dynamic-management-views%2Fsys-dm-database-encryption-keys-transact-sql%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fsystem-dynamic-management-views%2Fsys-dm-database-encryption-keys-transact-sql%3C%2FA%3E%20%3CBR%20%2F%3E%3CP%3E%3CEM%3E%20%3CSTRONG%3Eencryption_state%20%3C%2FSTRONG%3E%20-%20Indicates%20whether%20the%20database%20is%20encrypted%20or%20not%20encrypted.%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E0%20%3D%20No%20database%20encryption%20key%20present%2C%20no%20encryption%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3E%20%3CEM%3E%201%20%3D%20Unencrypted%20%3C%2FEM%3E%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CEM%3E%202%20%3D%20Encryption%20in%20progress%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%203%20%3D%20Encrypted%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%204%20%3D%20Key%20change%20in%20progress%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%205%20%3D%20Decryption%20in%20progress%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CEM%3E%206%20%3D%20Protection%20change%20in%20progress%20(The%20certificate%20or%20asymmetric%20key%20that%20is%20encrypting%20the%20database%20encryption%20key%20is%20being%20changed.)%3C%2FEM%3E%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-369199%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Feb%2011%2C%202019%20A%20quick%20lesson%20learned%20from%20the%20field%20when%20dealing%20with%20TDE%20(Transparent%20Data%20Encryption).%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-369199%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Edmv%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Etde%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETSQL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Not applicable
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-tra...

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

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