The sys.key_encryptions catalog view returns information about the mechanisms in use to protect the symmetric keys in the current database. Remember, a symmetric key can be protected in several ways, including certificates, asymmetric keys, other symmetric keys and passwords. Each symmetric key can be protected in many ways at the same time.
A symmetric key is just a string of random bits. How many bits is depending on the algorithm. A key for the AES_256 algorithm for example has 256 bits or 32 bytes. Those 32 bytes get stored by SQL Server as a VARBINARY value. However, they are not store in plain text. Instead, they are encrypted. If you specify a new symmetric key to be encrypted with a certificate, the actual 32 byte key is encrypted with that certificates public key and that crypt-value is then stored in the database. Before the symmetric key can be used for anything, SQL Server has to load it into memory and then decrypt it. That is the purpose of the OPEN SYMMETRIC KEY statement.
If a symmetric key is protected by more than one mechanism, SQL Server has to store an equal amount of encryptions of the key. These encryptions are all stored independent of each other and each one can be used without knowledge of the other ones. The sys.key_encryptions catalog view makes exactly these values visible.
The sys.key_encryptions catalog view contains the thumbprint column. This column identifies the key or certificate that was used to encrypt and therefore can be used to decrypt the symmetric key. In the case of a certificate or asymmetric key, finding the key is straight forward, as those have a thumbprint too that is available through the thumbprint column in their catalog views, sys.certificates and sys.asymmetric_keys. To get more information about those keys, you can just join on that column.
The sys.symmetric_keys catalog view also contains a thumbprint in the key_thumbprint column. However, that column is only valued for EKM provided keys and therefore is not usable for our purposes. The only other globally unique identifier of a symmetric key is the key_guid and that is actually the value that sys.key_encryptions.thumbprint is referencing for symmetric keys.
If you put that all together, you end up with a select statement like this:
SELECT SK.name, SK.symmetric_key_id, SK.key_length, SK.algorithm_desc, KE.crypt_type_desc, COALESCE(C.name,AK.name,PSK.name) AS protector_name, KE.crypt_property AS encrypted_key FROM sys.key_encryptions AS KE JOIN sys.symmetric_keys AS SK ON KE.key_id = SK.symmetric_key_id LEFT JOIN sys.certificates AS C ON KE.thumbprint = C.thumbprint LEFT JOIN sys.asymmetric_keys AS AK ON KE.thumbprint = AK.thumbprint LEFT JOIN sys.symmetric_keys AS PSK ON KE.thumbprint = CAST(PSK.key_guid AS VARBINARY(50));
The output of this query in my example database is shown below.
The crypt_property column of the sys.key_encryptions catalog view contains the actual encrypted key. Therefore, it should be possible to decrypt a symmetric key using that value, now that we know how to identify the protecting key.
For certificates and asymmetric keys this actually works. For example, to get the decrypted value for a certificate-protected key you can just add DECRYPTBYCERT(C.certificate_id,KE.crypt_property) to the query shown above. However, for password-protected and symmetric-key-protected keys this sadly does not work. The reason is that the values stored in crypt_property in those two cases are not compatible with the values produced by the ENCRYPTBYPASSWORD and ENCRYPTBYKEY functions. Therefore, any decryption attempt with their counterpart functions fails.
I have not been able to solve that riddle as of yet. However, if you can open the symmetric key, you can easily add another encryption by either a certificate or asymmetric key and get to the decrypted symmetric key value that way.
The final symmetric-key-decrypting query looks like this:
SELECT SK.name, SK.symmetric_key_id, SK.key_length, SK.algorithm_desc, KE.crypt_type_desc, COALESCE(C.name,AK.name,PSK.name) AS protector_name, KE.crypt_property AS encrypted_key, COALESCE(DECRYPTBYCERT(C.certificate_id,KE.crypt_property), DECRYPTBYASYMKEY(AK.asymmetric_key_id,KE.crypt_property)) AS decrypted_key FROM sys.key_encryptions AS KE JOIN sys.symmetric_keys AS SK ON KE.key_id = SK.symmetric_key_id LEFT JOIN sys.certificates AS C ON KE.thumbprint = C.thumbprint LEFT JOIN sys.asymmetric_keys AS AK ON KE.thumbprint = AK.thumbprint LEFT JOIN sys.symmetric_keys AS PSK ON KE.thumbprint = CAST(PSK.key_guid AS VARBINARY(50));
As you can see below, it actually works:
For all four crypt_property entries, the symmetric key decrypts to the same 32-byte value.
The crypt_property column of the sys.key_encryptions catalog view contains the encrypted key bits. After identifying the key they are encrypted with, you can decrypt any symmetric key by using built in T-SQL functions.