| title | sys.dm_pdw_nodes_database_encryption_keys (Transact-SQL) | Microsoft Docs | |
|---|---|---|
| ms.custom | ||
| ms.date | 03/07/2017 | |
| ms.prod | sql-non-specified | |
| ms.reviewer | ||
| ms.suite | ||
| ms.technology |
|
|
| ms.tgt_pltfrm | ||
| ms.topic | language-reference | |
| dev_langs |
|
|
| ms.assetid | e7fd02b2-5d7e-4816-a0af-b58ae2ac3f7a | |
| caps.latest.revision | 9 | |
| author | barbkess | |
| ms.author | barbkess | |
| manager | jhubbard |
[!INCLUDEtsql-appliesto-xxxxxx-xxxx-asdw-pdw_md]
Returns information about the encryption state of a database and its associated database encryption keys. sys.dm_pdw_nodes_database_encryption_keys provides this information for each node. For more information about database encryption, see Transparent Data Encryption (SQL Server PDW).
| Column Name | Data Type | Description |
|---|---|---|
| database_id | int | ID of the physical database on each node. |
| encryption_state | int | Indicates whether the database on this node 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 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 | varchar(?) | Displays the algorithm that is used for the key. |
| key_length | int | Displays the length of the key. |
| encryptor_thumbprint | varbin | Shows the thumbprint of the encryptor. |
| percent_complete | real | Percent complete of the database encryption state change. This will be 0 if there is no state change. |
| node_id | int | Unique numeric id associated with the node. |
Requires the VIEW SERVER STATE permission on the server.
The following example joins sys.dm_pdw_nodes_database_encryption_keys to other system tables to indicate the encryption state for each node of the TDE protected databases.
SELECT D.database_id AS DBIDinMaster, D.name AS UserDatabaseName,
PD.pdw_node_id AS NodeID, DM.physical_name AS PhysDBName,
keys.encryption_state
FROM sys.dm_pdw_nodes_database_encryption_keys AS keys
JOIN sys.pdw_nodes_pdw_physical_databases AS PD
ON keys.database_id = PD.database_id AND keys.pdw_node_id = PD.pdw_node_id
JOIN sys.pdw_database_mappings AS DM
ON DM.physical_name = PD.physical_name
JOIN sys.databases AS D
ON D.database_id = DM.database_id
ORDER BY D.database_id, PD.pdw_node_ID;
SQL Data Warehouse and Parallel Data Warehouse Dynamic Management Views (Transact-SQL)
CREATE DATABASE ENCRYPTION KEY (Transact-SQL)
ALTER DATABASE ENCRYPTION KEY (Transact-SQL)
DROP DATABASE ENCRYPTION KEY (Transact-SQL)