Temporary Symmetric Key Conflicts

One of the big advantages of temporary tables is that each process has its own private copy of it. A procedure can create a temporary table and store data in it and you do not have to worry about this data being visible or being tainted with by other processes, not even processes that execute that exact same procedure at the same time. Temporary tables are therefore a great tool to deal with larger amounts of transient data in concurrent environments.

Temporary symmetric keys are set up in a very similar way and you would expect the same concurrency advantages from them. But, as you will soon see, there is a minor problem.

Temporary Symmetric Keys and Re-Creatable Keys

SQL Server allow symmetric keys to be created from a KEY_SOURCE parameter. Every time the CREATE SYMMETRIC KEY statement is called with the same KEY_SOURCE parameter, the same symmetric key emerges. While I have a few questions about the algorithms involved, this is a great way to encrypt data based on a passphrase while still being able to choose the algorithm used. The ENCRYPTBYPASSPHRASE function on the other hand always uses the by now a little dated Triple-DES algorithm.

Now, it would be a great workflow for a CRUD procedure to create a temporary symmetric key based on the provided @passphrase parameter, encrypt or decrypt the data and then drop the key. That way, no information required to decrypt the stored data is persisted within the database (not even in encrypted form), which is a requirement in certain circumstances.

The Problematic Identity Value

SQL Server does not provide a method to specify the symmetric key on decryption. Instead, the key GUID is stored within every encrypted value. When an encrypted value is passed to the DECRYPTBYKEY function, that embedded GUID is used to find the required key. One bit in the GUID value is reserved to indicate that the key is a temporary key, so SQL Server know to look in tempdb for those keys.

To make that lookup work, the GUID needs to be unique. That uniqueness is enforced within the SQL Server Key Storage on a per database basis. And that particular unique constraint is now providing a problem to us.

To make the temporary key scenario described above work, the temporary key has to not only stem from the same passphrase, but also has to have the same GUID. The GUID is calculated based on the supplied IDENTITY_VALUE in the CREATE SYMMETRIC KEY statement and as long as you pass the same value there, the created key will have the same GUID. That works all as expected as long as only one of those temporary keys is created at any given time. But if you try to execute the same CREATE SYMMETRIC KEY statement from within two separate connections at the same time, one of them will error.

To see that happening, you can use this example statement:

When executed in two query windows at the same time, you will get a result like this:

The exact error message is below:

That this problem is in fact caused by the identity value, which is not immediately obvious from the error, you can see when you run the same test again with different IDENTITY_VALUE parameters:

Summary

While temporary symmetric keys are in theory a great tool for keys that cannot be permanently stored in the database, the requirement to have matching GUIDs makes them unusable in concurrent environments.

Let me know you thoughts on this in the comments below.