OPEN SYMMETRIC KEY scope in SQL Server

OPEN SYMMETRIC KEY scope in SQL Server

Rate This
  • Comments 3

  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.

 

  A few tips for modules that open symmetric keys on behalf of the caller:

·         Whenever is possible use the DecryptByKeyAuto* built-ins instead of the regular DecryptByKey one, that way the key is implicitly opened by the built-in and there is no need to explicitly open it on your module

·         Have as little code as possible between Opening and Closing the symmetric key to minimize logic errors that may lead to opening the key without closing it properly.

·         Make sure your code logic will handle errors properly. You can use TRY/CATCH in SQL to avoid an early termination of the module.

 

  I hope this information will be useful; as always feel free to ask any questions or send us feedback regarding this topic, we will be glad to help.

 

  -Raul Garcia

  SDE/T

  SQL Server Engine

Leave a Comment
  • Please add 7 and 8 and type the answer here:
  • Post
  • PingBack from http://msdnrss.thecoderblogs.com/2007/11/29/open-symmetric-key-scope-in-sql-server/

  • Great article, but I'm wondering why the following code seems to suggest that closing a key in a procedure even after an error still works.  In this code, no problem occurs before "No problem yet," but there is a "The key 's1' is not open" when it tries to close the key after calling the stored procedure.  This suggests that even without proper error handling, keys get closed.

    alter procedure TestFail

    as

    OPEN SYMMETRIC KEY S1 DECRYPTION BY

    ASYMMETRIC KEY a1 WITH PASSWORD = N'[password]';

    CLOSE SYMMETRIC KEY S1

    select 'No problem yet'

    OPEN SYMMETRIC KEY S1 DECRYPTION BY

    ASYMMETRIC KEY a1 WITH PASSWORD = N'[password]';

    select 1/0 --Force a divide-by-zero error

    CLOSE SYMMETRIC KEY S1

    go

    exec TestFail

    close symmetric key s1

    select 'It got here'

  • To answer Jonathan's question.

    Something to consider is that not all errors in SQL Server have the same behavior. Some errors such as division by 0 will not terminate the batch, so the following statement will be executed. Other errors will forcedly terminate the batch if not handled properly (i.e. TRY/CATCH).

    Here is a simpler example:

    print 'a'

    select 1/0

    print 'b'

    go

    This batch will result in printing “a”, a division by zero error, and printing “b”.

    On the other hand :

    print 'a'

    EXECUTE AS USER = 'Does not exist'

    print 'b'

    go

    This batch will result in printing “a”, an error 15517 (impersonation failed), and the batch terminating due to the error. Please notice that “print ‘b’” will not be executed in this case.

    Now let’s compare with a third batch, which does error handling:

    BEGIN TRY

    print 'a'

    EXECUTE AS USER = 'Does not exist'

    print 'b'

    END TRY

    BEGIN CATCH

    print 'b, second chance'

    print 'We got exception: "' + error_message() + '".'

    END CATCH

    go

    This time we will be printing “a”, then print “b, second chance”, and finally we will handle the error (in this case I simply printed it).

    If I replace “print ‘b’” with CLOSE SYMMETRIC KEY, I have a risk that the 2nd script will leave my symmetric key opened when the impersonation call fails and terminates the batch, while the 3rd script will handle the situation as I expected.

    I hope this helps to clarify the scenario.

    -Raul Garcia

Page 1 of 1 (3 items)