How to tell if Database Certificate Used?

Question

I plan to migrate a SQL Server database to another server. There is a certificate listed under the Security > Certificates view in SSMS Object Explorer.

I ran the below query to check if database is encrypted and it is not...

select name, is_encrypted from sys.databases

How can I find if this certificate is used? And how do I ensure successful migration?

Thanks!

Answer 1

As far as I know, the places where a Certificate will be linked -- whether by id or thumbprint -- can be found via the following three queries:

SELECT N'key_encryptions' AS [Where?],
       crt.[name], crt.[principal_id], crt.[pvt_key_encryption_type_desc],
       N'---' AS [---], ke.*
FROM   sys.certificates crt
INNER JOIN sys.key_encryptions ke
        ON ke.[thumbprint] = crt.[thumbprint]

SELECT N'database_mirroring_endpoints' AS [Where?],
       crt.[name], crt.[principal_id], crt.[pvt_key_encryption_type_desc],
       N'---' AS [---], dme.*
FROM   sys.certificates crt
INNER JOIN sys.database_mirroring_endpoints dme
        ON dme.[certificate_id] = crt.[certificate_id]

SELECT N'service_broker_endpoints' AS [Where?],
       crt.[name], crt.[principal_id], crt.[pvt_key_encryption_type_desc],
       N'---' AS [---], sbe.*
FROM   sys.certificates crt
INNER JOIN sys.service_broker_endpoints sbe
        ON sbe.[certificate_id] = crt.[certificate_id]

AND, the query shown in this answer:

Find signed procedures, functions, triggers, assemblies and by which certificates / asymmetric keys

---

You can migrate the Certificate by backing it (and its private key or the Database Master Key) up to files and then restoring those files into the new Database. Please see the MSDN documentation for the following commands:

• BACKUP CERTIFICATE
• CREATE CERTIFICATE (for the restore)

And, if any Certificates are ENCRYPTED_BY_MASTER_KEY (or whatever), then you will likely also need to look at:

• BACKUP MASTER KEY
• RESTORE MASTER KEY

Answer 2

This will provide details about the certificates encrypting any database that has TDE encryption enabled:

use master 
select 
    db_name(db.database_id) DatabaseName,
    c.name,
    * 
from sys.databases db
join sys.dm_database_encryption_keys dek
    on
    dek.database_id=db.database_id
join sys.certificates c
    on
    dek.encryptor_thumbprint=c.thumbprint
where
    db.is_encrypted=1

For all other column level encryption within a user database, backup the Database Master Key, migrate it to the other environment and restore it after restoring the user database.