OPEN SYMMETRIC KEY scope in SQL Server

  Recently I have heard a few questions regarding the scope of the SYMMETRIC KEY key-ring, especially when using modules (i.e. stored procedures) to open a key. One particular topic that got my attention is the impression that the OPEN SYMMETRIC KEY call may “leak outside the module” (i.e. the key will remain opened) if the SYMMETRIC KEY is not closed inside the module.

  In the OPEN SYMMETRIC KEY topic in BOL (under Remarks section) we documented that the opened key is bound to the session, not to the execution context (including a module frame) and that it will remain opened until the key is explicitly closed (using CLOSE SYMMETRIC KEY) or the session is terminated. This is indeed the designed behavior.

  What does this mean when opening a symmetric key on a module that is not supposed to “leak” the key? It means that it is necessary to make sure that the key is explicitly closed; there is no mechanism to bind the OPEN SYMMETRIC KEY call to the module in SQL Server 2005.

  For example:

CREATE CERTIFICATE [cert_keyring_demo]

  WITH SUBJECT = 'key ring demo'

go

CREATE SYMMETRIC KEY [symkey_keyring_demo]

  WITH ALGORITHM = AES_192

  ENCRYPTION BY CERTIFICATE [cert_keyring_demo]

go

CREATE USER [lowpriv_user] WITHOUT LOGIN

go

CREATE PROC [sp_openkey]

-- We will be runnign this module under an impersonated context

WITH EXECUTE AS OWNER

AS

  OPEN SYMMETRIC KEY [symkey_keyring_demo]

    DECRYPTION BY CERTIFICATE [cert_keyring_demo]

  -- Notice that the key is not being closed on purpose

  --

go

-- Grant minimum privielges

--

GRANT EXECUTE ON [dbo].[sp_openkey] TO [lowpriv_user]

GRANT VIEW DEFINITION ON SYMMETRIC KEY::[symkey_keyring_demo] TO [lowpriv_user]

go

EXECUTE AS USER = 'lowpriv_user'

go

SELECT * FROM sys.openkeys

go

-- fails with error 15151

--

OPEN SYMMETRIC KEY [symkey_keyring_demo]

     DECRYPTION BY CERTIFICATE [cert_keyring_demo]

go

-- This will succeed

--

EXEC [dbo].[sp_openkey]

go

-- And we can verify that the key is opened on our session.

SELECT * FROM sys.openkeys

go

-- and we can encrypt & decrypt

declare @blob varbinary(1000)

declare @pt varchar(1000)

SET @blob = encryptbykey( key_guid( 'symkey_keyring_demo'), 'data' )

SET @pt = convert( varchar(1000), decryptbykey( @blob ))

SELECT @pt, @blob

go

-- We can swithc context...

REVERT

go

-- and verify that the key ring is still opened

SELECT * FROM sys.openkeys

go

-- And the key remains opened until we close it

-- or we terminate the session

--

CLOSE SYMMETRIC KEY symkey_keyring_demo

go

Obviously in this example the intention was to keep the key opened after the module ends, but it is possible that this may happen by mistake and we “leak the key” as an undesired error (i.e. a bug in the application), for example:

CREATE TABLE [dbo].[tabl_keyring_demo]( id int IDENTITY PRIMARY KEY,

  data varbinary(1000), LastUsedDate datetime )

go

OPEN SYMMETRIC KEY [symkey_keyring_demo]

     DECRYPTION BY CERTIFICATE [cert_keyring_demo]

go

INSERT INTO [dbo].[tabl_keyring_demo]

  VALUES ( encryptbykey( key_guid( 'symkey_keyring_demo'), 'lowpriv_user' ), GetDate())

INSERT INTO [dbo].[tabl_keyring_demo]

  VALUES ( encryptbykey( key_guid( 'symkey_keyring_demo'), 'outdated_user' ), GetDate())

go

CLOSE SYMMETRIC KEY [symkey_keyring_demo]

go

CREATE PROC [sp_keyring_demo2]( @id int )

WITH EXECUTE AS OWNER

AS

-- The intention of this SP is to decrypt data, but close the key

-- before leaving the module frame

--

declare @username varchar(1000)

if( EXISTS(SELECT count(*) FROM [dbo].[tabl_keyring_demo] WHERE Id = @id))

BEGIN

     OPEN SYMMETRIC KEY [symkey_keyring_demo]

          DECRYPTION BY CERTIFICATE [cert_keyring_demo];