SQL Server 2005 helpful catalogs: crypt_properties and key_encryptions


A while ago, I answered a question about how to find out what keys are encrypted by a certificate. Since then, I compiled a list of useful queries that can be used to find out the encryptions and signatures made with a cryptographic entity. Before listing them, a brief description is required for the two catalogs that are used by these queries:


crypt_properties stores the information related to signatures. Cryptographic keys are identified by their thumbprint.


key_encryptions stores the information related to encryptions of symmetric keys. As in the case of crypt_properties, asymmetric keys and certificates are identified by their thumbprint; however, note that the symmetric keys are identified differently, using their guid.


And now for the list of queries… 


— signatures by a certificate – cert1
select object_name(cp.major_id) from sys.crypt_properties cp, sys.certificates c where cp.thumbprint = c.thumbprint and c.name = ‘cert1’;
— signatures by an asymmetric key – akey1
select object_name(cp.major_id) from sys.crypt_properties cp, sys.asymmetric_keys ak where cp.thumbprint = ak.thumbprint and ak.name = ‘akey1’;


— symmetric keys encrypted by a certificate – cert1
select sk.name from sys.key_encryptions ke, sys.certificates c, sys.symmetric_keys sk where ke.thumbprint = c.thumbprint and c.name = ‘cert1’ and ke.key_id = sk.symmetric_key_id
— symmetric keys encrypted by an asymmetric key – akey1
select sk.name from sys.key_encryptions ke, sys.asymmetric_keys ak, sys.symmetric_keys sk where ke.thumbprint = ak.thumbprint and ak.name = ‘akey1’ and ke.key_id = sk.symmetric_key_id
— symmetric keys encrypted by a symmetric key – skey1
select sk1.name from sys.key_encryptions ke, sys.symmetric_keys sk1, sys.symmetric_keys sk2 where ke.thumbprint = sk2.key_guid and sk2.name = ‘skey1’ and ke.key_id = sk1.symmetric_key_id


— certificates that encrypt a symmetric key skey1
select c.name from sys.key_encryptions ke, sys.certificates c, sys.symmetric_keys sk where ke.thumbprint = c.thumbprint and sk.name = ‘skey1’ and ke.key_id = sk.symmetric_key_id
— asymmetric keys that encrypt a symmetric key – skey1
select ak.name from sys.key_encryptions ke, sys.asymmetric_keys ak, sys.symmetric_keys sk where ke.thumbprint = ak.thumbprint and sk.name = ‘skey1’ and ke.key_id = sk.symmetric_key_id
— symmetric keys that encrypt a symmetric key – skey1
select sk2.name from sys.key_encryptions ke, sys.symmetric_keys sk1, sys.symmetric_keys sk2 where ke.thumbprint = sk2.key_guid and sk1.name = ‘skey1’ and ke.key_id = sk1.symmetric_key_id


 

Comments (0)