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;
