Is there a way to verify the details of a backed up TDE certificate without actually restoring the certificate on a server?

  • We have a bunch of backed up TDE certificates and I've been asked to verify that the certificates being used by the server match what is in them. Is there any way to do this without restoring each certificate?

  • Are the certificate backup files encrypted with a private key?
    If so, that would prevent inspecting them externally with something like a text viewer.

    One thing you could do is write a PowerShell script to enumerate across the certificate files in a folder and restore each to a local or dev SQL Server instance and then run the following script to query details.

    SELECT
        @@SERVERNAME AS server_name
      , c.name AS cert_name
        , c.pvt_key_encryption_type_desc
        , c.thumbprint
        , c.subject
      , d.name AS database_name
        , d.is_encrypted
      , dek.encryptor_type
    FROM sys.certificates c
        LEFT JOIN sys.dm_database_encryption_keys dek ON dek.encryptor_thumbprint = c.thumbprint
            LEFT JOIN sys.databases d ON dek.database_id = d.database_id
    ORDER BY cert_name, d.name;

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Yes

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply