How to Crack the Symmetric Keys in the Database Wide Open

Introduction

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.

What does "Protected" actually mean?

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.

Identifying the Key Protecting the Key

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:

The output of this query in my example database is shown below.

Decrypting a Symmetric Key

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:

As you can see below, it actually works:

For all four crypt_property entries, the symmetric key decrypts to the same 32-byte value.

Summary

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.