sys.dm_pdw_nodes_database_encryption_keys (Transact-SQL)

APPLIES TO: noSQL Server noAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

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.

Examples: Azure Synapse Analytics (SQL DW) and Parallel Data Warehouse

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, AS UserDatabaseName,   
PD.pdw_node_id AS NodeID, DM.physical_name AS PhysDBName,   
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;  

See Also

SQL Data Warehouse and Parallel Data Warehouse Dynamic Management Views (Transact-SQL)