Post reply

How to find which cert is doing the TDE

  • October 19, 2011 at 2:41 pm

    #390660

    I've inherited an instance that contains two certificates in the [master] database to implement TDE. I'm guessing one is used to encrypt the databases on the instance and the other was used to encrypt a database from the TEST instance, then moved over when that database was moved from TEST to PRODUCTION.

    Is there a way to find which certificate is responsible for the database encryption key in the respective user databases? I've tried:

    sys.symmetric_keys

    sys.asymmetric_keys

    sys.key_encryptions

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.

  • October 19, 2011 at 3:17 pm

    #1396992

    Found it:

    sys.dm_database_encryption_keys

    'encryptor_thumbprint' can be joined to sys.certificates.thumbprint to find the certificate used for encryption:

    select db_name(a.database_id), b.name

    from sys.dm_database_encryption_keys a

    join

    sys.certificates b

    on a.encryptor_thumbprint = b.thumbprint

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.

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

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