Query to List Encryption Certificate for Databases
You can join on the certificate thumbprint:
use master;
go
select
database_name = d.name,
dek.encryptor_type,
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
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'
,case
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