Certifikáty a TDE – expirácia a zálohy kľúčov

/* DeadlockCafe | Certifikáty a TDE – expirácia a zálohy kľúčov
   Účel:  Všetky certifikáty v master aj v používateľských databázach s dátumom expirácie a poslednej zálohy privátneho kľúča, plus stav TDE šifrovania a ktorým certifikátom je chránené.
   Režim: READ ONLY | SQL Server 2016 – 2022, on-premises
*/
SET NOCOUNT ON;

DECLARE @WarnDays int = {{WarnDays:90|Upozorniť koľko dní pred expiráciou}};

IF OBJECT_ID(N'tempdb..#cert') IS NOT NULL DROP TABLE #cert;
CREATE TABLE #cert
(
    database_name            sysname        NOT NULL,
    certificate_name         sysname        NOT NULL,
    subject                  nvarchar(4000) NULL,
    start_date               datetime       NULL,
    expiry_date              datetime       NULL,
    pvt_key_encryption       nvarchar(60)   NULL,
    pvt_key_last_backup_date datetime       NULL,
    thumbprint               varbinary(32)  NULL
);

DECLARE @db sysname, @sql nvarchar(max);
DECLARE dbs CURSOR LOCAL FAST_FORWARD FOR
    SELECT name FROM sys.databases
    WHERE state = 0 AND HAS_DBACCESS(name) = 1 AND name NOT IN (N'tempdb', N'model')
    ORDER BY CASE WHEN name = N'master' THEN 0 ELSE 1 END, name;
OPEN dbs;
FETCH NEXT FROM dbs INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'USE ' + QUOTENAME(@db) + N';
INSERT INTO #cert (database_name, certificate_name, subject, start_date, expiry_date,
                   pvt_key_encryption, pvt_key_last_backup_date, thumbprint)
SELECT DB_NAME(), c.name, c.subject, c.start_date, c.expiry_date,
       c.pvt_key_encryption_type_desc, c.pvt_key_last_backup_date, c.thumbprint
FROM sys.certificates AS c
WHERE c.name NOT LIKE N''##%'';';
    BEGIN TRY
        EXEC sys.sp_executesql @sql;
    END TRY
    BEGIN CATCH
        PRINT N'Preskočená databáza ' + @db + N': ' + ERROR_MESSAGE();
    END CATCH;
    FETCH NEXT FROM dbs INTO @db;
END;
CLOSE dbs;
DEALLOCATE dbs;

-- 1) certifikáty
SELECT c.database_name, c.certificate_name, c.subject, c.start_date, c.expiry_date,
       DATEDIFF(DAY, GETDATE(), c.expiry_date) AS days_to_expiry,
       CASE WHEN c.expiry_date < GETDATE() THEN N'EXPIROVANÝ'
            WHEN c.expiry_date < DATEADD(DAY, @WarnDays, GETDATE()) THEN N'EXPIRUJE ČOSKORO'
            ELSE N'OK' END AS expiry_status,
       c.pvt_key_encryption,
       c.pvt_key_last_backup_date,
       CASE WHEN c.database_name = N'master'
             AND EXISTS (SELECT 1 FROM sys.dm_database_encryption_keys AS k WHERE k.encryptor_thumbprint = c.thumbprint)
            THEN 1 ELSE 0 END AS is_tde_protector
FROM #cert AS c
ORDER BY is_tde_protector DESC, c.expiry_date;

-- 2) TDE databázy (encryption_state_desc je až od 2019, preto CASE)
SELECT DB_NAME(k.database_id) AS database_name,
       CASE k.encryption_state
            WHEN 0 THEN N'bez DEK' WHEN 1 THEN N'nešifrovaná' WHEN 2 THEN N'šifruje sa'
            WHEN 3 THEN N'šifrovaná' WHEN 4 THEN N'mení sa kľúč' WHEN 5 THEN N'dešifruje sa'
            WHEN 6 THEN N'mení sa ochrana DEK' END AS encryption_state,
       k.percent_complete,
       k.key_algorithm, k.key_length, k.encryptor_type,
       c.certificate_name                 AS encryptor_certificate,
       c.expiry_date                      AS certificate_expiry,
       c.pvt_key_last_backup_date,
       CASE WHEN k.database_id = 2 THEN N'tempdb – šifrovaná automaticky'
            WHEN k.encryptor_type = N'CERTIFICATE' AND c.certificate_name IS NULL THEN N'certifikát v master nenájdený (chýbajú práva?)'
            WHEN k.encryptor_type = N'CERTIFICATE' AND c.pvt_key_last_backup_date IS NULL
                 THEN N'RIZIKO: kľúč certifikátu nebol zálohovaný – zálohu DB neobnovíš na inom serveri'
            WHEN k.encryptor_type = N'CERTIFICATE' THEN N'OK'
            ELSE N'iný encryptor (asymetrický kľúč / EKM) – over ručne' END AS key_backup_status
FROM sys.dm_database_encryption_keys AS k
LEFT JOIN #cert AS c ON c.database_name = N'master' AND c.thumbprint = k.encryptor_thumbprint
ORDER BY database_name;