How to Crack the Symmetric Keys in the Database Wide Open

2014-07-14 - Cryptography, DMVs & CVs, General, Security, Security, SQL Server Internals

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:

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 sys.key_encryptions Catalog View

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:

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:

Decrypted Symmetric Keys

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.

Categories: Cryptography, DMVs & CVs, General, Security, Security, SQL Server Internals
Tags: , , , ,

3 Responses to How to Crack the Symmetric Keys in the Database Wide Open

  1. Pingback: Debunking Symmetric Key Recreate-Ability - sqlity.net

  2. benjaminmathew says:

    For symmetric keys encrypted by asymmetric keys, it appears that the asymmetric key must be open (or able to be opened automatically, i.e. protected by the database master key) in order for this to produce a result. If you are able to open the asymmetric key, then you are able to open the symmetric key, in which case decrypting the key information itself is not necessarily of any additional value.

  3. @sqlity says:

    benjaminmathew  The point of the article was to show a way to get to the actual key. If that is of value is dependent on your situation. My needing to decrypt a SQL Server encrypted value outside of the database environment is what triggered the research that eventually led to this article.

Leave a Reply