sys-dm-pdw-nodes-database-encryption-keys-transact-sql.md

title	sys.dm_pdw_nodes_database_encryption_keys (Transact-SQL)
description	sys.dm_pdw_nodes_database_encryption_keys (Transact-SQL)
author	WilliamDAssafMSFT
ms.author	wiassaf
ms.date	03/07/2017
ms.service	sql
ms.subservice	data-warehouse
ms.topic	reference
dev_langs	TSQL
monikerRange	>=aps-pdw-2016||=azure-sqldw-latest

sys.dm_pdw_nodes_database_encryption_keys (Transact-SQL)

[!INCLUDEapplies-to-version/asa-pdw]

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).

Note

[!INCLUDEsynapse-analytics-od-unsupported-syntax]

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.

Permissions

Requires the VIEW SERVER STATE permission on the server.

Examples

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.

[!INCLUDEssPDW]

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;  

[!INCLUDEssazuresynapse-md]

--Query provides underlying distribution encryption status
SELECT keys.database_id AS DBIDinPhysicalDatabases,   
PD.pdw_node_id AS NodeID, PD.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  
ORDER BY keys.database_id, PD.pdw_node_ID;

--Query provides the DW encryption status
SELECT D.database_id AS DBIDinMaster, D.name AS UserDatabaseName,   
PD.pdw_node_id AS NodeID, PD.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.databases AS D  
    ON D.database_id = PD.database_id  
ORDER BY D.database_id, PD.pdw_node_ID;

See Also

Azure Synapse Analytics 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)