SQL Server DMVs and catalog views for monitoring DuoKey SQL EKM
Returns information about registered cryptographic providers.
SELECT
provider_id,
name,
guid,
version,
dll_path,
is_enabled
FROM sys.cryptographic_providers;
GO| Column | Data Type | Description |
|---|---|---|
provider_id | int | Unique identifier for the provider |
name | sysname | Name of the cryptographic provider |
guid | uniqueidentifier | Provider GUID |
version | nvarchar(256) | Provider version string |
dll_path | nvarchar(512) | Path to the provider DLL |
is_enabled | bit | Whether the provider is enabled (1) or disabled (0) |
Returns information about asymmetric keys in the database.
SELECT
name,
principal_id,
asymmetric_key_id,
pvt_key_encryption_type_desc,
algorithm_desc,
key_length,
sid,
thumbprint,
provider_id
FROM sys.asymmetric_keys;
GO| Column | Data Type | Description |
|---|---|---|
name | sysname | Key name |
asymmetric_key_id | int | Unique identifier for the key |
algorithm_desc | nvarchar(60) | Algorithm description (e.g., RSA_2048) |
key_length | int | Key length in bits |
thumbprint | varbinary(32) | Key thumbprint for identification |
provider_id | int | EKM provider ID (NULL for non-EKM keys) |
provider_id are EKM-managed keys.Returns information about SQL Server credentials.
SELECT
credential_id,
name,
credential_identity,
create_date,
modify_date,
target_type,
target_id
FROM sys.credentials
WHERE name LIKE '%EKM%' OR name LIKE '%DuoKey%';
GO| Column | Data Type | Description |
|---|---|---|
credential_id | int | Unique identifier |
name | sysname | Credential name |
credential_identity | nvarchar(4000) | Identity (Client ID for DuoKey) |
create_date | datetime | When credential was created |
target_type | nvarchar(100) | Type of target (CRYPTOGRAPHIC PROVIDER) |
target_id | int | ID of the target provider |
SECRET value is never exposed in system views for security reasons.Returns the encryption state for all databases. This is the primary DMV for monitoring TDE status.
SELECT
database_id,
encryption_state,
create_date,
regenerate_date,
modify_date,
set_date,
opened_date,
key_algorithm,
key_length,
encryptor_thumbprint,
encryptor_type,
percent_complete
FROM sys.dm_database_encryption_keys;
GO| Column | Data Type | Description |
|---|---|---|
database_id | int | Database identifier |
encryption_state | int | Current encryption state (0-6) |
key_algorithm | nvarchar(32) | Algorithm used (e.g., AES) |
key_length | int | Key length in bits (128, 192, 256) |
encryptor_thumbprint | varbinary(20) | Thumbprint of protecting key |
percent_complete | real | Percentage complete for state changes |
regenerate_date | datetime | Last key rotation date |
| State | Name | Description |
|---|---|---|
| 0 | No DEK | No database encryption key present |
| 1 | Unencrypted | DEK exists but encryption not enabled |
| 2 | Encrypting | Encryption in progress |
| 3 | Encrypted | Database fully encrypted |
| 4 | Key Changing | Key rotation in progress |
| 5 | Decrypting | Decryption in progress |
| 6 | Protection Changing | Protector change in progress |
Returns I/O statistics for data and log files. Useful for monitoring TDE performance impact.
SELECT
database_id,
file_id,
num_of_reads,
num_of_writes,
io_stall_read_ms,
io_stall_write_ms,
size_on_disk_bytes
FROM sys.dm_io_virtual_file_stats(NULL, NULL);
GO| Column | Data Type | Description |
|---|---|---|
num_of_reads | bigint | Number of reads issued |
num_of_writes | bigint | Number of writes issued |
io_stall_read_ms | bigint | Total time waiting for reads (ms) |
io_stall_write_ms | bigint | Total time waiting for writes (ms) |
size_on_disk_bytes | bigint | Size of file on disk |
-- Verify EKM provider is operational
DECLARE @result INT;
BEGIN TRY
SELECT @result = COUNT(*)
FROM sys.cryptographic_providers
WHERE name = 'DuoKeyProvider' AND is_enabled = 1;
IF @result = 1
PRINT 'EKM Provider: HEALTHY';
ELSE
PRINT 'EKM Provider: NOT FOUND OR DISABLED';
END TRY
BEGIN CATCH
PRINT 'EKM Provider: ERROR - ' + ERROR_MESSAGE();
END CATCH
GO-- Track key creation and modification for rotation planning
SELECT
ak.name AS KeyName,
ak.create_date AS Created,
ak.modify_date AS Modified,
DATEDIFF(DAY, ak.create_date, GETDATE()) AS AgeDays,
CASE
WHEN DATEDIFF(DAY, ak.create_date, GETDATE()) > 365
THEN 'Rotation Recommended'
WHEN DATEDIFF(DAY, ak.create_date, GETDATE()) > 270
THEN 'Rotation Soon'
ELSE 'OK'
END AS RotationStatus
FROM sys.asymmetric_keys ak
WHERE ak.provider_id IS NOT NULL
ORDER BY ak.create_date;
GO-- Monitor ongoing encryption operations
SELECT
DB_NAME(database_id) AS DatabaseName,
encryption_state,
CASE encryption_state
WHEN 2 THEN 'Encrypting'
WHEN 4 THEN 'Key Rotating'
WHEN 5 THEN 'Decrypting'
WHEN 6 THEN 'Changing Protector'
END AS Operation,
percent_complete AS [Progress %],
modify_date AS LastUpdate,
DATEDIFF(MINUTE, modify_date, GETDATE()) AS MinutesSinceUpdate
FROM sys.dm_database_encryption_keys
WHERE encryption_state IN (2, 4, 5, 6);
GOThis query provides a complete overview of your TDE encryption status, suitable for dashboards and monitoring tools:
-- Comprehensive TDE encryption status dashboard
SELECT
DB_NAME(dek.database_id) AS [Database],
d.state_desc AS [DB State],
CASE dek.encryption_state
WHEN 0 THEN 'No DEK'
WHEN 1 THEN 'Unencrypted'
WHEN 2 THEN 'Encrypting...'
WHEN 3 THEN 'Encrypted'
WHEN 4 THEN 'Key Changing...'
WHEN 5 THEN 'Decrypting...'
WHEN 6 THEN 'Protection Changing...'
END AS [Encryption State],
dek.percent_complete AS [Progress %],
dek.key_algorithm + '_' + CAST(dek.key_length AS VARCHAR) AS [Algorithm],
ak.name AS [Protecting Key],
cp.name AS [EKM Provider],
dek.create_date AS [DEK Created],
dek.regenerate_date AS [Last Rotated],
DATEDIFF(DAY, ISNULL(dek.regenerate_date, dek.create_date), GETDATE())
AS [Days Since Rotation],
CASE
WHEN DATEDIFF(DAY, ISNULL(dek.regenerate_date, dek.create_date), GETDATE()) > 365
THEN 'OVERDUE'
WHEN DATEDIFF(DAY, ISNULL(dek.regenerate_date, dek.create_date), GETDATE()) > 270
THEN 'DUE SOON'
ELSE 'OK'
END AS [Rotation Status]
FROM sys.dm_database_encryption_keys dek
INNER JOIN sys.databases d ON dek.database_id = d.database_id
LEFT JOIN sys.asymmetric_keys ak ON dek.encryptor_thumbprint = ak.thumbprint
LEFT JOIN sys.cryptographic_providers cp ON ak.provider_id = cp.provider_id
ORDER BY d.name;
GO