Get certificate name by using column name or the symmetric key

Question

I am using SQL Server 2008/2012.In my database I have a table which has encrypted column.

I am getting encryption key of a encrypted column by using this query -

SELECT DISTINCT key_name(encryptedcol) FROM encryptedTable;

my encrypted column name is encryptedcol.

Now I want to get the certificate name which is used to create the encrypted key (means I need the certificate name which is used to open the above symmetric key to decrypt data).

For example -

Here highlighted text is my certificate name , which is here hardcoded but I want to get this from a sql query.

Michael Keleher Michael Keleher · Accepted Answer · 2016-11-10T16:36:33

You can use the system tables to determine the encryption hierarchy for a column. Assuming that the symmetric key was encrypted by a certificate, this tsql will work. If your symmetric key was encrypted by an asymmetric key, then substitute sys.certificates with sys.asymmetric_keys, which also has a thumbprint column that uniquely identifies it.

select
    c.name
from sys.symmetric_keys sk
join sys.key_encryptions ke
    on
    sk.symmetric_key_id= ke.key_id
join sys.certificates c
    on
    ke.thumbprint=c.thumbprint
where
     sk.name in 
        (SELECT DISTINCT key_name(encryptedssn) FROM HR.Employees)

Get certificate name by using column name or the symmetric key

1 Answers