sys.dm_pdw_nodes_database_encryption_keys (Transact-SQL)
Applies to:
Azure Synapse Analytics
Analytics Platform System (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
This syntax is not supported by serverless SQL pool in Azure Synapse Analytics.
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.
Analytics Platform System (PDW)
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;
Azure Synapse Analytics
--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)
Povratne informacije
https://aka.ms/ContentUserFeedback.
Kmalu na voljo: V letu 2024 bomo ukinili storitev Težave v storitvi GitHub kot mehanizem za povratne informacije za vsebino in jo zamenjali z novim sistemom za povratne informacije. Za več informacij si oglejte:Pošlji in prikaži povratne informacije za