dcsimg
 

What is the State of My Transparent Data Encrypted Database?

Thursday Mar 1st 2018 by Greg Larsen

When using Transparent Data Encryption, you might wonder “What is the state of my transparent data encrypted database?” Read on to learn the many different states that a transparent data encrypted database might go through.

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

Home
Mobile Site | Full Site