| description | sys.dm_database_encryption_keys (Transact-SQL) | ||||
|---|---|---|---|---|---|
| title | sys.dm_database_encryption_keys (Transact-SQL) | Microsoft Docs | ||||
| ms.custom | |||||
| ms.date | 03/27/2019 | ||||
| ms.prod | sql | ||||
| ms.prod_service | database-engine, sql-database | ||||
| ms.reviewer | |||||
| ms.technology | system-objects | ||||
| ms.topic | language-reference | ||||
| f1_keywords |
|
||||
| dev_langs |
|
||||
| helpviewer_keywords |
|
||||
| ms.assetid | 56fee8f3-06eb-4fff-969e-abeaa0c4b8e4 | ||||
| author | markingmyname | ||||
| ms.author | maghan | ||||
| monikerRange | =azuresqldb-current||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDE SQL Server SQL Database]
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 (in UTC) the encryption key was created. |
| regenerate_date | datetime | Displays the date (in UTC) the encryption key was regenerated. |
| modify_date | datetime | Displays the date (in UTC) the encryption key was modified. |
| set_date | datetime | Displays the date (in UTC) the encryption key was applied to the database. |
| opened_date | datetime | Shows when (in UTC) 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: [!INCLUDEssNoVersion] ([!INCLUDEssSQL11] 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. |
| encryption_state_desc | nvarchar(32) | Applies to: [!INCLUDEsql-server-2019] and later. String that indicates whether the database is encrypted or not encrypted. NONE UNENCRYPTED ENCRYPTED DECRYPTION_IN_PROGRESS ENCRYPTION_IN_PROGRESS KEY_CHANGE_IN_PROGRESS PROTECTION_CHANGE_IN_PROGRESS |
| encryption_scan_state | int | Applies to: [!INCLUDEsql-server-2019] and later. Indicates the current state of the encryption scan. 0 = No scan has been initiated, TDE is not enabled 1 = Scan is in progress. 2 = Scan is in progress but has been suspended, user can resume. 3 = Scan was aborted for some reason, manual intervention is required. Contact Microsoft Support for more assistance. 4 = Scan has been successfully completed, TDE is enabled and encryption is complete. |
| encryption_scan_state_desc | nvarchar(32) | Applies to: [!INCLUDEsql-server-2019] and later. String that indicates the current state of the encryption scan. NONE RUNNING SUSPENDED ABORTED COMPLETE |
| encryption_scan_modify_date | datetime | Applies to: [!INCLUDEsql-server-2019] and later. Displays the date (in UTC) the encryption scan state was last modified. |
On [!INCLUDEssNoVersion_md], requires VIEW SERVER STATE permission.
On [!INCLUDEssSDS_md] Premium Tiers, requires the VIEW DATABASE STATE permission in the database. On [!INCLUDEssSDS_md] Standard and Basic Tiers, requires the Server admin or an Azure Active Directory admin account.
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)