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;

See all articles by Greg Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles