SQL Server Extensible Key Management (EKM) requires the authentication information (user/password) to be stored in a credential mapped to the primary identity. This version of EKM cannot be used under an impersonated context; that is, you cannot access the EKM while running a module with the EXECUTE AS clause.

   However, some customers want to manage just one CREDENTIAL with the EKM authentication information and allow a group of users to access this credential.

   The most intuitive mechanism to allow this controlled escalation of permissions on the CREDENTIAL was to use a module marked with EXECUTE AS clause where the callers were impersonating a principal mapped to the EKM credential; unfortunately, because of the EKM restriction when running under an impersonated context, this approach failed.

   Below I present an alternative to work around this limitation. The idea in this small demo is to use an asymmetric key (stored in the HSM) to open a symmetric key (stored in SQL Server) and keep this symmetric key in the session key ring.

  Instead of changing the context (i.e. using EXECUTE AS clause), adding a secondary identity to a module using a digital signature works better since it doesn’t change the primary identity. 

  The signature will grant the caller permission to map the required EKM CREDENTIAL to the caller; once the EKM CERTIFICATE is mapped, it should be possible to open the SYMMETRIC KEY since the caller will have access to the asymmetric key private key (stored in the HSM) , finally the SP will un-map the CREDENTIAL. Once the SYMMETRIC KEY is opened in the session it can be used at any time.

  Thanks to Rick Byham, Sameer Tejani, Jack Richins & Il-Sung Lee for their feedback while writing this article and demo.

/*****************************************************************************************

*      This posting is provided "AS IS" with no warranties, and confers no rights.

*

* Authors:    Raul Garcia

* Date:       09/08/2009

* Date:       09/08/2009

* Description:

*

*   Workaround to allow different logins to share an EKM credential instead of creating

* individual CREDENTIAL objects for each user.

*

*   The demo uses a single CREDENTIAL, syncrhornized by an application lock

* digitally signed stored procedure to allow the authorized callers to open a SYMMETRIC KEY

* and leave the key open in the session by calling the SP.

*

*   The SYMMETRIC KEY is protected by an ASYMMETRIC KEY, the ASYMMETRIC KEY resides in the

* EKM and can be accessed by the CREDENTIAL.

*

*   The SP will add temporary the CREDENTIAL to the caller's login, which will allow to open

* the SYMMETRIC KEY, and immediately remove the CREDENTIAL from the login's control.

*

*

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

*

* Security note: 

*

*   The CREDENTIAL will be assigned for a very small window of time to the

* SP caller. This window is as little as possible, but the caller may attempt to abuse this window

*

*   Because of this window, it is highly recommended to only grant permission to

* objects protected directly by the EKM via digital signatures instead of granting permissions

* directly, and to audit cryptographic object creation/access related activity in all databases.

*

*     (c) 2009 Microsoft Corporation. All rights reserved.

*

*****************************************************************************************/

 

 

------------------------------------------------------------------------

-- Preparation for demo (demo specific):

-- Create Cryptographic provider

-- Create CREDENTIAL with login information for the EKM

-- Create ASYMMETRIC & SYMMETRIC keys in the hosting DB

--

CREATE CRYPTOGRAPHIC PROVIDER [Demo_Provider]

    FROM FILE = '<<Provider.dll>>';

go

 

CREATE CREDENTIAL [CredentialEkmDemo]

    WITH IDENTITY = '<<EkmLoginName>>',

    SECRET = '<<EkmPassword>>'

    FOR CRYPTOGRAPHIC PROVIDER [Demo_Provider];

GO

 

-- Move to the DB hosting the SYMMETRIC KEY

--

USE db_EkmDemo ;

go

 

CREATE ASYMMETRIC KEY [AsymKeyEkmHosted]

   FROM PROVIDER AsKeyProv--[Demo_Provider]

   WITH ALGORITHM = RSA_2048,

   PROVIDER_KEY_NAME = '<<Ekm_AsymkeyName>>';

go

 

CREATE SYMMETRIC KEY [key_EkmProtected]

   WITH ALGORITHM = AES_128

   ENCRYPTION BY ASYMMETRIC KEY [AsymKeyEkmHosted];

go

 

-- Move to the DB hosting the SYMMETRIC KEY

--

USE db_EkmDemo;

go

 

-- Create schema & loginless owner for the necessary objects

--

CREATE USER [EkmHelperObjOwner] WITHOUT LOGIN;

go

CREATE SCHEMA [EkmHelper] AUTHORIZATION [EkmHelperObjOwner];

go

 

-- In this table we can track down when the EKM credential is in use

-- Assuming only one credential for simplicity.

--

CREATE TABLE [EkmHelper].[EkmAsymKeyProviderAux](

   Id int PRIMARY KEY,

   CredentialName sysname ); -- EKM CREDENTIAL with sufficient privileges to access the ASYMMETRIC KEY needed

go

 

--------------------------------------------------------

-- TODO: Replace the value for the CREDENTIAL name

--------------------------------------------------------

-- Insert the name of the EKM CREDENTIAL to be used, Id=1 and state=0

--

INSERT INTO [EkmHelper].[EkmAsymKeyProviderAux] VALUES

   ( 1, N'CredentialEkmDemo');

go

 

----------------------------------------------------------------------------------------

-- [EkmHelper].[sp_OpenSymKeyViaEkmAsymKey] will change the table with the state of the key,

-- Assign temporay ownership of the EKM CREDENTIAL to the caller,

-- open the SYMMETRIC KEY (using the EKM-protected ASYMMETRIC KEY)

-- Remove the CREDENTIAL from the caller and finally reset the state of the key

--

-- This SP will be signed and additional permissions will be added to the

-- execution context via the signature.

--

-- The caller of this function would need VIEW DEFINITION on the SYMMETRIC KEY being opened

--

ALTER PROC [EkmHelper].[sp_OpenSymKeyViaEkmAsymKey]

   (@SymKeyName sysname, -- For this demo, the SYMMETRIC KEY name is user-defined

   @AsymKeyName sysname) -- For this demo, the ASYMMETRIC KEY name is user-defined

AS

   -- Set the name of the authorized DB.

   -- NOTE: @authorizedDbName value must be set when creating the SP

   --       That way the value will be part of the digital signature

   --------------------------------------------------------

   -- TODO: Replace the value for the Authorized DB name

   --------------------------------------------------------

   DECLARE @authorizedDbName sysname = N'db_EkmDemo';

 

   -- Verify that the SP is being executed from the authorized DB

   -- This should prevent the module from being copied to a DB autside the

   -- control of the author/DBA

   --

   IF( db_name() <> @authorizedDbName )

   BEGIN

      RAISERROR( N'This module can only be called from database %s.', 16, 1, @authorizedDbName);

      RETURN;

   END

  

   -- OPTIONAL (Not in demo)

   -- Add ad-hoc validation for caller's rights

   -- Add ad-hoc validation for the parameters

 

 

   -- Optional: Early check on keyring

   --

   -- Verify if the key is already on the keyring,

   -- if so, do not attempt to reopen the key

   -- NOTE: print statements on this demo are for informational puposes only

   --       In production, you may want to silence them.

   IF((SELECT COUNT(*) FROM sys.openkeys WHERE key_name = @SymKeyName AND database_id = DB_ID()) > 0)

   BEGIN

      PRINT N'SYMMETRIC KEY ' + quotename(@SymKeyName) + ' is already opened in this session';

      RETURN;

   END

 

 

   BEGIN TRANSACTION;

 

   -- Lock the application  

   --

   DECLARE @result int;

   DECLARE @LockName nvarchar(255) = N'sp_OpenSymKeyViaEkmAsymKey';

   EXEC @result = sp_getapplock @Resource = @LockName, @LockMode = 'Exclusive';

 

   -- verify that we were able to aquire the lock

   --

   IF(@result < 0)

   BEGIN

      EXEC sp_releaseapplock @LockName;

      ROLLBACK;

      RAISERROR( N'Unable to aquire the application lock for resource "%s", error code %d. Please contact your administrator.', 16, 2, @LockName, @result);

      RETURN;

   END

 

   DECLARE @currentLogin sysname;

   SET @currentLogin = suser_sname();

  

   -- Attempt to update the row in the helper table to set the current caller & SPID

   -- as users of the CREDENTIAL.

   -- NOTE: If more than one caller attempt to access the CREDENTIAL at the same time

   -- the call amy fail.

   --

  

   -- Get the ASYMMETRIC KEY name from the table

   -- The SPID & AssignedLoginName should match this one.

   --

   DECLARE @cmd nvarchar(max);

   DECLARE @CredentialName sysname;

   SELECT @CredentialName = CredentialName

      FROM [EkmHelper].[EkmAsymKeyProviderAux]

      WHERE Id = 1;

   IF( @CredentialName is null )

   BEGIN

      EXEC sp_releaseapplock @LockName;

      ROLLBACK;

      RAISERROR( N'Unexpected error gathering the CREDENTIAL name.', 16, 3);

        RETURN;

   END

 

   -- Assign the CREDENTIAL to the caller

   --

   BEGIN TRY

      SET @cmd = N'ALTER LOGIN ' + QUOTENAME( @currentLogin) + N' ADD CREDENTIAL ' + QUOTENAME( @CredentialName );

      PRINT 'Assigning CREDENTIAL';

      EXECUTE( @cmd );

   END TRY

   BEGIN CATCH

      EXEC sp_releaseapplock @LockName;

      ROLLBACK;

      DECLARE @errorNumber int = error_number();

      DECLARE @errorMessage nvarchar(max) = error_message();

      RAISERROR( N'Unexpected error while modifying the login to assign the CREDENTIAL. Last Error: %d. Last Message: %s', 16, 4, @errorNumber, @errorMessage);

      RETURN;

   END CATCH

 

   -- Try to open the SYMMETRIC KEY

   -- If we fail on thi soperation, record the error number & message,

   -- but do not raise an error until the CREDENTIAL has been reset.

   --

   DECLARE @OpenKeyStatus int = 0;

   DECLARE @OpenKeyErrorNumber int = 0;

   DECLARE @OpenKeyErrorMessage nvarchar(max);

   BEGIN TRY

      SET @cmd = N'OPEN SYMMETRIC KEY ' + QUOTENAME(@SymKeyName) + N' DECRYPTION BY ASYMMETRIC KEY ' + QUOTENAME( @AsymKeyName );

      PRINT 'Opening SYMMETRIC KEY';

      EXECUTE( @cmd );

      SET @OpenKeyStatus = 1;

   END TRY

   BEGIN CATCH

      SET @OpenKeyStatus = 0;

      SET @OpenKeyErrorNumber = error_number();

      SET @OpenKeyErrorMessage = error_message();

   END CATCH

 

   -- Reset the CREDENTIAL

   --

   BEGIN TRY

      PRINT 'Resetting CREDENTIAL';

      SET @cmd = N'ALTER LOGIN ' + QUOTENAME( @currentLogin) + N' DROP CREDENTIAL ' + QUOTENAME( @CredentialName );

      EXECUTE( @cmd );

   END TRY

   BEGIN CATCH

      EXEC sp_releaseapplock @LockName;

      ROLLBACK;

      SET @errorNumber = error_number();

      SET @errorMessage = error_message();

      RAISERROR( N'Unexpected error while modifying the login to assign the CREDENTIAL. Last Error: %d. Last Message: %s', 16, 5, @errorNumber, @errorMessage);

      RETURN;

   END CATCH

 

   -- Verify if the SYMMETRIC KEY was successfully opened

   --

   if( @OpenKeyStatus <> 1 )

   BEGIN

      EXEC sp_releaseapplock @LockName;

      ROLLBACK;

      RAISERROR( N'Unexpected error was detected when opening the SYMMETRIC KEY. Error Number: %d. Message: %s', 16, 6, @OpenKeyErrorNumber, @OpenKeyErrorMessage);

      RETURN;

   END

 

   EXEC sp_releaseapplock @LockName;

   COMMIT;

go

 

-- Create a CERTIFICATE to sign the module

-- Optional (not shown): Delete the private key after signing to prevent its use

--

CREATE CERTIFICATE [cert_EkmAsymKey]

   ENCRYPTION BY PASSWORD = '^&rv375#$cwswo8'

   WITH SUBJECT = 'EKM Demo';

go

 

ADD SIGNATURE TO [EkmHelper].[sp_OpenSymKeyViaEkmAsymKey]

   BY CERTIFICATE [cert_EkmAsymKey]

   WITH PASSWORD = '^&rv375#$cwswo8';

go

 

-- Make a copy of the CERTIFICATE in order to re-create it on master DB

-- NOTE: This statement only backs up the public part of the certificate, not the private key.

--

BACKUP CERTIFICATE [cert_EkmAsymKey] TO FILE = 'cert_EkmAsymKey.cer';

go

 

-- Create user from CERTIFICATE and grant the needed permissions

--

CREATE USER [cert_EkmAsymKey] FROM CERTIFICATE [cert_EkmAsymKey];

go

 

GRANT UPDATE ON [EkmHelper].[EkmAsymKeyProviderAux] TO [cert_EkmAsymKey];

GRANT SELECT ON [EkmHelper].[EkmAsymKeyProviderAux] TO [cert_EkmAsymKey];

GRANT CONTROL ON ASYMMETRIC KEY::[AsymKeyEkmHosted] TO [cert_EkmAsymKey];

go

 

 

-- Move to master DB, recreate the CERTIFICATE

-- create a login from the cert & grant the required permission

--

USE master

go

CREATE CERTIFICATE [cert_EkmAsymKey] FROM FILE = 'cert_EkmAsymKey.cer';

go

CREATE LOGIN [cert_EkmAsymKey] FROM CERTIFICATE [cert_EkmAsymKey];

go

GRANT ALTER ANY CREDENTIAL TO [cert_EkmAsymKey];

GRANT ALTER ANY LOGIN TO [cert_EkmAsymKey];

go

 

-- Back in the hosting DB, create a ROLE to manage access to the SP &

-- grant additional permissions

--

USE db_EkmDemo;

go

 

CREATE ROLE [EkmUsers];

go

GRANT VIEW DEFINITION ON SYMMETRIC KEY::[key_EkmProtected] TO [EkmUsers];

GRANT EXECUTE ON [EkmHelper].[sp_OpenSymKeyViaEkmAsymKey] TO [EkmUsers];

go