What certificate is being used to encrypt each of the databases on the instances.

I can get the data using the following but how do I write the queries

USE master

-- this provides the list of certificates
SELECT * FROM sys.certificates

-- this provides the list of databases (encryption_state = 3) is encrypted
SELECT * FROM sys.dm_database_encryption_keys
 WHERE encryption_state = 3;

I noticed that columns sys.certifcates.thumbprint and sys.dm_database_encryption_keys.encryptor_thumbprint contain the same data.

2 Answers 2


You can join on the certificate thumbprint:

use master;

    database_name = d.name,
    cert_name = c.name
from sys.dm_database_encryption_keys dek
left join sys.certificates c
on dek.encryptor_thumbprint = c.thumbprint
inner join sys.databases d
on dek.database_id = d.database_id;

My sample output:

database_name           encryptor_type    cert_name
=============           ==============    =========
tempdb                  ASYMMETRIC KEY    NULL
AdventureWorks2012TDE   CERTIFICATE       TdeCert
  • 1
    Note the encryptor_type field is only available on SQL 2012+. Commented Jan 22, 2018 at 16:21
  • This query does the same thing, but also includes non-encrypted DBs for comparison: SELECT d.name AS database_name, dek.encryptor_type, c.name AS cert_name FROM sys.databases AS d LEFT OUTER JOIN sys.dm_database_encryption_keys AS dek ON dek.database_id = d.database_id LEFT OUTER JOIN sys.certificates AS c ON dek.encryptor_thumbprint = c.thumbprint
    – CJBS
    Commented Feb 2, 2021 at 23:41

For a more in-depth query that shows which databases are encrypted or not, their certificate and IMPORTANTLY if the encryption setup has actually completed or not. The encryption can sometimes take a long time to complete or get stuck.

SELECT D.name AS 'Database Name'
,c.name AS 'Cert Name'
,E.encryptor_type AS 'Type'
    when E.encryption_state = 3 then 'Encrypted'
    when E.encryption_state = 2 then 'In Progress'
    else 'Not Encrypted'
end as state,
E.encryption_state, E.percent_complete, E.key_algorithm, E.key_length, E.* FROM sys.dm_database_encryption_keys E
right join sys.databases D on D.database_id = E.database_id
left join sys.certificates c ON E.encryptor_thumbprint=c.thumbprint

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.