Home » Blog » How to Create Temporary Symmetric Keys

How to Create Temporary Symmetric Keys

2014-07-25 - Cryptography, General, Security, Temporary Objects

In some situations, you might not want to keep the key to your encrypted data in your database, but rather re-create it every time you need access to the data. For that very purpose, SQL Server offers temporary keys.

A Temporary Symmetric Key Example

Temporary symmetric keys are created the same way you would create any other temporary object. You just designate the key as temporary by prefixing its name with a # sign:

[sql] CREATE SYMMETRIC KEY #temp WITH KEY_SOURCE = '%%%%%%%%%%', IDENTITY_VALUE = '1', ALGORITHM = AES_256 ENCRYPTION BY PASSWORD='**********';
[/sql]

Temporary keys, like to temporary tables, are not stored in the current database but instead in tempdb. To confirm that, the following script creates two similar keys, one temporary and one non-temporary. It then queries the sys.symmetric_keys catalog view in both the current database and tempdb for all keys that contain the four letters "temp" in their name:

[sql] IF(KEY_ID('#temp') IS NOT NULL)DROP SYMMETRIC KEY #temp;
IF(KEY_ID('temp') IS NOT NULL)DROP SYMMETRIC KEY temp;
GO
CREATE SYMMETRIC KEY #temp WITH KEY_SOURCE = '%%%%%%%%%%', IDENTITY_VALUE = '1', ALGORITHM = AES_256 ENCRYPTION BY PASSWORD='**********';
GO
CREATE SYMMETRIC KEY temp WITH KEY_SOURCE = '%%%%%%%%%%', IDENTITY_VALUE = '1', ALGORITHM = AES_256 ENCRYPTION BY PASSWORD='**********';
GO
SELECT 'tempdb' AS database_name,SK.symmetric_key_id,SK.name,SK.key_guid FROM tempdb.sys.symmetric_keys AS SK WHERE SK.name LIKE '%temp%'
UNION ALL
SELECT DB_NAME() AS database_name,SK.symmetric_key_id,SK.name,SK.key_guid FROM sys.symmetric_keys AS SK WHERE SK.name LIKE '%temp%';
[/sql]

When you execute this script, you will get a result like this:

As predicted, the temporary symmetric key is stored in tempdb whereas the "normal" symmetric key lives in the current database. You can also see that the name of the temporary key is augmented with some form of session information. The full name of the temporary key in this example is below.

[sourcecode] #temp___________________________________________________________________________________________________________000000010000F736
[/sourcecode]

How that number at the end of the name is generated in not documented and unclear to me. It is neither the SPID, the symmetric_key_id nor a part of the key_guid, but it might be something like a checksum over some or all of these values.

A Temporary Warning

Like temporary tables, a temporary key goes away when the connection is closed. That means you need to make sure that you can recreate the key, for example by using the KEY_SOURCE and IDENTITY_VALUE parameters, as in the example above.

SQL Server does not stop you from creating an auto-generated temporary symmetric key like this:

Because you can neither back that key up nor reliably recreate it, encrypting data with this key is about as good as deleting the data. You will never be able to get back to it as soon as the current connection closes.

Summary

We can create temporary symmetric keys in SQL Server. Temporary keys behave similar to temporary tables: They are stored in tempdb instead of the current database and they are automatically dropped as soon as the connection in which they were created closes. For that reason, care needs to be taken to only generate re-creatable temporary keys.