sys.dm_database_encryption_keys (Transact-SQL)

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Returns information about the encryption state of a database and its associated database encryption keys. For more information about database encryption, see Transparent Data Encryption (TDE).

Column Name Data Type Description
database_id int ID of the database.
encryption_state int Indicates whether the database is encrypted or not encrypted.

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.)
create_date datetime Displays the date the encryption key was created.
regenerate_date datetime Displays the date the encryption key was regenerated.
modify_date datetime Displays the date the encryption key was modified.
set_date datetime Displays the date the encryption key was applied to the database.
opened_date datetime Shows when the database key was last opened.
key_algorithm nvarchar(32) Displays the algorithm that is used for the key.
key_length int Displays the length of the key.
encryptor_thumbprint varbinary(20) Shows the thumbprint of the encryptor.
encryptor_type nvarchar(32) Applies to: SQL Server ( SQL Server 2012 (11.x) through current version).

Describes the encryptor.
percent_complete real Percent complete of the database encryption state change. This will be 0 if there is no state change.

Permissions

On SQL Server, requires VIEW SERVER STATE permission.
On SQL Database, requires the VIEW DATABASE STATE permission in the database.

See Also

Security-Related Dynamic Management Views and Functions (Transact-SQL)
Transparent Data Encryption (TDE)
SQL Server Encryption
SQL Server and Database Encryption Keys (Database Engine)
Encryption Hierarchy
ALTER DATABASE SET Options (Transact-SQL)
CREATE DATABASE ENCRYPTION KEY (Transact-SQL)
ALTER DATABASE ENCRYPTION KEY (Transact-SQL)
DROP DATABASE ENCRYPTION KEY (Transact-SQL)