Home » Blog » Debunking Symmetric Key Recreate-Ability

Debunking Symmetric Key Recreate-Ability

2014-07-18 - Cryptography, General, Security, Security Misconceptions

"You do not need to back up your symmetric key if it was created from a certificate, because you can just recreate it."

This advice I keep seeing and hearing around the internet and at conferences. Even many SQL Server experts believe this to be true. However, this is incorrect and dangerous advice.

Debunking the Certificate Derived Symmetric Key Myth

This misconception is based on the assumption that two symmetric keys that are created with the ENCRYPTION BY CERTIFICATE clause using the same certificate will be identical. Because you can back up a certificate, the assumption then is that you do not need an additional backup of your "derived" symmetric keys.

The first hint that this myth might lack some truth comes along when you look at the key_guid. While the key guid is not derived from the key (other than e.g. a thumbprint), it is used by SQL Server to identify the symmetric key to use when decrypting a value. As you might know, the DECRYPTBYKEY function does not have a parameter that allows you to specify the key to use.

To show you what I am referring to, when pointing out the key_guid value, let us start out by creating a few keys:

[sql] CREATE CERTIFICATE KeyProtection WITH SUBJECT = 'A Key Protecting Certificate';

CREATE SYMMETRIC KEY CertificateProtectedKey1 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE KeyProtection;
CREATE SYMMETRIC KEY CertificateProtectedKey2 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE KeyProtection;
CREATE SYMMETRIC KEY CertificateProtectedKey3 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE KeyProtection;
CREATE SYMMETRIC KEY CertificateProtectedKey4 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE KeyProtection;
[/sql]

The above T-SQL snippet creates a certificate and then four symmetric keys that are protected by that same certificate. Now we can run the following query to look at the key_guid for each of the four new keys:

[sql] SELECT SK.name,
SK.symmetric_key_id,
SK.algorithm_desc,
SK.key_guid
FROM sys.symmetric_keys AS SK
WHERE SK.name LIKE 'CertificateProtectedKey_';
[/sql]

The result of that query on my system looks like this:

You can immediately see that the Key GUIDs are different. While that does not give us any information on the keys themselves, it certainly would present a significant hurdle if you were to try to decrypt a value with a different key than the one that was used to encrypt it.

The Proof

To proof that the keys are indeed different we are going to have to decrypt the keys themselves. Luckily, that is not hard at all as you can assure yourself of here. The following query is a simplified form of the query you can find in that article:

[sql] SELECT SK.name,
DECRYPTBYCERT(C.certificate_id,KE.crypt_property) AS decrypted_key,
C.name AS protecting_certificate, KE.crypt_type_desc, SK.symmetric_key_id
FROM sys.key_encryptions AS KE
JOIN sys.symmetric_keys AS SK
ON KE.key_id = SK.symmetric_key_id
JOIN sys.certificates AS C
ON KE.thumbprint = C.thumbprint
WHERE SK.name LIKE 'CertificateProtectedKey_';
[/sql]

It uses the sys.key_encryptions catalog view to identify the certificate that protects the given symmetric key and then uses that certificate to decrypt the key.

The output of this query on my system is shown below:

As you can clearly see, the four keys are not even similar to each other. Clearly one does not help decrypting a value that was decrypted with another one.

But What About Symmetric Keys Protected by Asymmetric Keys?

Glad you asked. The first point to make here is that you cannot really backup an asymmetric key either, so even if they would behave differently from certificates it would be of limited use. However, let us quickly confirm anyway. First, we need a few symmetric keys protected by the same asymmetric key:

[sql] CREATE ASYMMETRIC KEY KeyProtection WITH ALGORITHM = RSA_2048;

CREATE SYMMETRIC KEY AsymmetricKeyProtectedKey1 WITH ALGORITHM = AES_256 ENCRYPTION BY ASYMMETRIC KEY KeyProtection;
CREATE SYMMETRIC KEY AsymmetricKeyProtectedKey2 WITH ALGORITHM = AES_256 ENCRYPTION BY ASYMMETRIC KEY KeyProtection;
CREATE SYMMETRIC KEY AsymmetricKeyProtectedKey3 WITH ALGORITHM = AES_256 ENCRYPTION BY ASYMMETRIC KEY KeyProtection;
CREATE SYMMETRIC KEY AsymmetricKeyProtectedKey4 WITH ALGORITHM = AES_256 ENCRYPTION BY ASYMMETRIC KEY KeyProtection;
[/sql]

Then we can run a very similar query to the one above to decrypt these symmetric keys:

[sql] SELECT SK.name,
DECRYPTBYASYMKEY(AK.asymmetric_key_id,KE.crypt_property) AS decrypted_key,
AK.name AS protecting_asymmetric_key, KE.crypt_type_desc, SK.symmetric_key_id
FROM sys.key_encryptions AS KE
JOIN sys.symmetric_keys AS SK
ON KE.key_id = SK.symmetric_key_id
JOIN sys.asymmetric_keys AS AK
ON KE.thumbprint = AK.thumbprint
WHERE SK.name LIKE 'AsymmetricKeyProtectedKey_';
[/sql]

The result of that query does not look a lot different from the one of the certificate query:

Again, all four symmetric keys are significantly different from each other.

Your Take-Away

You clearly cannot rely on certificates (or asymmetric keys) to recreate symmetric keys in your database. That means you need to find another way to back up your symmetric keys. Sadly, SQL Server does not provide a way to create a backup of such a key. The only two reliable ways of creating a recreate-able symmetric key are by using either an external key management solution (an EKM module), or by deriving the key from a passphrase. However, both have their own set of disadvantages.