Home » Blog » The Unexpected Scope of Temporary Symmetric Keys

The Unexpected Scope of Temporary Symmetric Keys

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

Just as we can create temporary tables, SQL Server allows us to create temporary symmetric keys. Those temporary keys are only accessible from the session that created them and they are removed automatically when the session is closed. They seem to behave just like temporary tables, so you would expect to see the same scoping behavior too. Well, you might be in for a surprise.

Session Scope of Temporary Symmetric Keys

Let us start out by confirming that a temporary symmetric key is really only accessible within the current session. For that, we first need to create a brand new temporary symmetric key:

[sql] CREATE SYMMETRIC KEY #temp
WITH KEY_SOURCE = '%%%%%%%%%%',
IDENTITY_VALUE = '2',
ALGORITHM = AES_256
ENCRYPTION BY PASSWORD='**********';
GO
SELECT SK.symmetric_key_id,SK.name,SK.key_guid FROM tempdb.sys.symmetric_keys AS SK WHERE SK.name LIKE '%temp%';
[/sql]

This script consists of two statements. The first creates the temporary key. The second is a SELECT against the sys.symmetric_keys catalog view in tempdb to prove that the key indeed was created. Executing this script will yield a result similar to this:

Note that the SPID of the connection is 51.

Now, let us try to use that key from another session. The following few lines attempt to encrypt a string with this key and then decrypt it again:

[sql] OPEN SYMMETRIC KEY #temp DECRYPTION BY PASSWORD='**********'
DECLARE @cipher VARBINARY(MAX) = ENCRYPTBYKEY(KEY_GUID('#temp'),'Hello World!');
SELECT CAST(DECRYPTBYKEY(@cipher) AS VARCHAR(MAX)) AS [decrypted];
[/sql]

As we expected, this does not work:

This proves that a temporary symmetric key is only accessible in the same session that created the key.

It also means that the key should be cleaned up automatically when the session is ended and the connection is closed. To show that, I am just going to disconnect and reconnect the query window and rerun the SELECT against sys.symmetric_keys:

You can see that the SPID changed and that the temporary symmetric key is gone. So far, the temporary keys behave just like temporary tables. However, that is going to change in a moment.

The Call Stack Scope of Temporary Symmetric Keys

With "Call Stack Scope", I am referencing the visibility of temporary objects when it comes to stored procedures and dynamic SQL. Remember that any temporary table that is created within a stored procedure, is not accessible anymore, once the procedure exits. The same is true for temporary tables created within a batch of dynamic SQL. As soon as that batch finishes, those temporary tables are automatically dropped.

Temporary symmetric keys are different.

First, let us try to create a temporary symmetric key using dynamic SQL:

[sql] EXEC('CREATE SYMMETRIC KEY #temp WITH KEY_SOURCE = ''%%%%%%%%%%'', IDENTITY_VALUE = ''1'', ALGORITHM = AES_256 ENCRYPTION BY PASSWORD=''**********'';');
GO
SELECT SK.symmetric_key_id,SK.name,SK.key_guid FROM tempdb.sys.symmetric_keys AS SK WHERE SK.name LIKE '%temp%';
[/sql]

The dynamically executed SQL statement creates a new temporary symmetric key. The following SELECT then queries the sys.symmetric_keys catalog view to see if that key is still available. Based on the behavior of temporary tables we would expect the key to be gone at that time. Let us try:

The key is still very much existing. So, while a temporary table created in a dynamic SQL batch is dropped automatically, once the batch finishes, a temporary symmetric key survives.

But what about procedures? Let us check:

[sql] IF OBJECT_ID('dbo.CreateTempKey') IS NOT NULL DROP PROCEDURE dbo.CreateTempKey;
GO
CREATE PROCEDURE dbo.CreateTempKey
AS
CREATE SYMMETRIC KEY #temp WITH KEY_SOURCE = '%%%%%%%%%%', IDENTITY_VALUE = '1', ALGORITHM = AES_256 ENCRYPTION BY PASSWORD='**********';
GO

IF(KEY_ID('#temp') IS NOT NULL)DROP SYMMETRIC KEY #temp;
GO
EXEC dbo.CreateTempKey
GO
SELECT SK.symmetric_key_id,SK.name,SK.key_guid FROM tempdb.sys.symmetric_keys AS SK WHERE SK.name LIKE '%temp%';
[/sql]

The procedure dbo.CreateTempKey is very simple. Its only function is to create a temporary symmetric key. The script above first creates that procedure and then calls it. After the procedure finishes, the same SELECT we have encountered before is used to see if the key is still there or if it is gone. And again, the behavior is different than we are used to from temporary tables:

Here too the temporary symmetric key continues its existence after the procedure it was created in exits.

Summary

While there are similarities between the scoping behavior of temporary tables and temporary symmetric keys, there are also some differences. Particularly, temporary symmetric keys that were created within a dynamic SQL batch or a stored procedure will live on after the batch or procedure finishes.