OPEN SYMMETRIC KEY – Avoid the NULL Ciphertext Trap

Introduction

When implementing encryption in SQL Server you need to keep track of many moving parts. Some mistakes can make your entire encrypted data unrecoverable.

Today I would like to cover one of those mistakes. It makes your encryption so secure, not even you can decrypt the data.

The Devilishly Silent ENCRYPTBYKEY function

The ENCRYPTBYKEY function can be used to encrypt data with a symmetric key. To use it you first have to create a symmetric key in the current database. Then you can just call the function like this:

In this example, "Key_C" is the name of the key I previously created and now want to use to encrypt the "Hello World!" string. However, the output is somehow not what we would expect:

There is no error message, so one would assume that we did everything correctly. But the output of the function is just plain NULL. Maybe this is the result of a new security feature, introduced with SQL Server 2014, that makes sure that nobody can ever break the encryption?

This behavior is actually not new. In fact, ENCRYPTBYKEY has behaved this way since it was introduced in SQL Server 2005. The return value being NULL is due to the fact that the key has not been opened. Any time you want to use a symmetric key in SQL Server for any cryptographic action, you have to open it first. If you forget that step, SQL Server will raise an error in some cases. But in other cases, like here when using the ENCRYPTBYKEY function, the failure will be silent. It is up to you to make sure that the appropriate key is not only available, but has also been opened before encrypting data. Otherwise you might end up with a table full of NULLs were you had hoped to find securely encrypted data.

The OPEN SYMMETRIC KEY Statement

To open a symmetric key, you can use the aptly named OPEN SYMMETRIC KEY statement like this:

A symmetric key can be protected by either a password, a certificate, an asymmetric key or another symmetric key. (Check out The SQL Server Encryption Hierarchy for details on this.) When you attempt to open a symmetric key, you have to specify the protection mechanism that was used at the time the key was created. The above example shows how to specify a certificate for this purpose.

With the key opened, ENCRYPTBYKEY now returns usable data:

Using OPEN SYMMETRIC KEY with Password Protected Protection Mechanisms

Certificates and asymmetric keys are also protected. The certificate in the above example was protected by the database master key which in turn is protected by the service master key. In that situation, we do not need to do anything special to use the certificate. However, if the certificate or asymmetric key is protected with a password instead, we need to specify that password when using the private key. That includes opening a symmetric key that is protected by e.g. a password protected asymmetric key. You can specify the password in the WITH PASSWORD clause of the OPEN SYMMETRIC KEY statement:

The result is shown below:

OPEN SYMMETRIC KEY for Keys Directly Protected with a Password

While it is not necessarily a good practice, you also can protect a symmetric key with a simple password. To open such a key you can simply specify that password in the DECRYPTION BY PASSWORD clause:

The reason this is not a good idea is that you cannot chose the algorithm used to encrypt your key when using a password. The algorithm is always triple DES. That algorithm is weaker than the new AES algorithms. Therefore, if you create a key to use it with AES but protect it with a password, you are unnecessarily weakening your encryption from the get-go.

OPEN a SYMMETRIC KEY with Another Symmetric Key

The last way to protect a symmetric key is to use another symmetric key. The OPEN SYMMETRIC KEY statement in that use case is simple again:

However, remember that a symmetric key needs to be open before it can be used. That is true for the protecting key too. So make sure it is open before executing this statement.

If you forget to open that key, SQL Server is however nice enough, to actually respond with a meaningful error message:

If you open the key first, everything works as desired:

By the way, the OPEN SYMMETRIC KEY statement will not throw an error if the key is open already. In that case, the statement just becomes a No-Op. That means it is safe (and a good practice) to open a symmetric key anytime you want to use it.

Summary

A symmetric key has to be opened before it can be used. SQL Server does however not always complain about a key that has not been opened yet. This can lead to nasty surprises, including data loss, if you are not careful. So, make sure to always open you keys.