Miscellaneous

What is the State of My Transparent Data Encrypted Database?

When using Transparent Data Encryption, you might wonder “What is the state of my transparent data encrypted database?”  There are many different states that a transparent data encrypted database might go through.  Those states go from not being encrypted to being completely encrypted, and a few others related to managing the encryption keys.

You probably already know that when you encrypt a database with transparent data encryption it takes a while for the entire database to be encrypted.  The same is true when you turn off transparent data encryption, it takes a while to decrypt your data.  Here is a list of all the different possible states that a database might have at any given time, as it relates to transparent database encryption:

Encryption State Description
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.)

To display the encryption state of your database, you use the dynamic management view  sys.dm_database_encryption_keys.  Here is an example SELECT statement that would display the encryption state of all databases that have a database encryption key on an instance of SQL Server:

SELECT db_name(database_id), encryption_state 
FROM sys.dm_database_encryption_keys;

Source link

Leave a Reply

Your email address will not be published. Required fields are marked *