How To: Share a Single EKM Credential among Multiple Users

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