November, 2007

  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];

     SELECT @username = convert( varchar(1000), decryptbykey( data ))

       FROM [dbo].[tabl_keyring_demo] WHERE Id = @id;

     -- For demonstration purposes, I will add a DDL statetemnt that I know

     -- will fail the second time I execute the module

    if( @username is not null )

    BEGIN

          EXECUTE AS USER = @username

          --... do something interesting under this context

          REVERT

    END

     -- Updating LastUsedDate column

     UPDATE [dbo].[tabl_keyring_demo] SET LastUsedDate = GetDate() WHERE Id = @id;

     CLOSE SYMMETRIC KEY [symkey_keyring_demo];

END

go

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

go

--Let's give it a try...

EXECUTE AS USER = 'lowpriv_user'

go

-- This one will work as expected

--

EXEC [dbo].[sp_keyring_demo2] 1

go

-- and no keys in the key ring

--

SELECT * FROM sys.openkeys

go

-- But this one will fail with error 15517

-- This error will terminate the current batch,

-- and the key will not close properly

--

EXEC [dbo].[sp_keyring_demo2] 2

go

SELECT * FROM sys.openkeys

go

--Clean up

CLOSE SYMMETRIC KEY [symkey_keyring_demo];

go

REVERT

go

 Notice that this particular SP was not properly designed, and it keeps the key opened far longer than needed and that the error handing for the code segment that may fail. The following modification in the code is an improvement over the original one:

ALTER 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

     SELECT @username = convert( varchar(1000), DecryptByKeyAutoCert( cert_id('cert_keyring_demo'), null, data ))

       FROM [dbo].[tabl_keyring_demo] WHERE Id = @id;

     -- For demonstration purposes, I will add a DDL statetemnt that I know

     -- will fail the second time I execute the module

    if( @username is not null )

    BEGIN

          BEGIN TRY

              EXECUTE AS USER = @username

              --... do something interesting under this context

              REVERT

          END TRY

          BEGIN CATCH

              print 'Unexpected error'

              print error_message()

              -- add code to handle error properly here

              --

              RAISERROR( 'Error in impersonated context', 16, 1 )

          END CATCH

    END

     -- Updating LastUsedDate column

     UPDATE [dbo].[tabl_keyring_demo] SET LastUsedDate = GetDate() WHERE Id = @id;

END

go

-- succeeds

--

EXEC [dbo].[sp_keyring_demo2] 1

go

-- fails gracefully

--

EXEC [dbo].[sp_keyring_demo2] 2

go

  As you can see, the current design decision of binding the key ring to the session may affect the way you need to design modules that open symmeric keys, especially during scenarios where the key should only be opened on behalf of the caller exclusively during a module execution. It is very important to understand the limitations and behavior of the system in case of an error and taking it into consideration when designing an application that uses OPEN SYMMETRIC KEY.