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;See all articles by Greg Larsen