Referenz zu Systemsichten

-- 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);
GO