none
EKM Credential Mapping and Impersonation Issues

    Question

  • We're currently evaluating a HSM device to allow us to use the EKM extensions built in to SQL Server 2008.

    The problems that we're experiencing are that it doesn't seem to be possible to:

    1. Map a single EKM credential to multiple logins.
    2. Perform encryption/decryption from SQL Server using an impersonated security context - i.e. using EXECUTE AS is not allowed when encrypting/decrypting using EKM as the encryption/decryption has to be performed under the primary security context.
    3. Map a credential to a login that has been created from a Windows group.

    So in a multi-user, multi-application environment where many different logins are in use (mostly Windows logins, but also some SQL Server logins) then the use of EKM seems to be somewhat limited.

    One solution could be to create one credential per login, but this is clumsy and would require regular access to one of the HSM's passwords in order to create the credential. I'm not particularly happy about automating the creation of a credential as the password would have to be stored and retrieved.

    Ideally we would like to have only one credential defined, mapped to a login that is impersonated when calling a single stored procedure that opens a native symmetric key that is protected by an EKM asymmetric key.

    There's currently practically no real-world experience of SQL Server 2008 EKM documented on the web. I am currently working with the supplier to find a workaround, but  I was wondering if anyone has come across the same obstacles and whether a workable solution had been found?

    Thanks
    Chris
    MCTS : SQL Server 2005 ; MCITP : Database Developer
    Friday, September 04, 2009 2:39 PM

All replies

  • Hi Chris,

    Have a try to create a Windows Group as a SQL Server Login, map a single EKM credential to that Windows Group, now you can add users to the Windows Group, in this way, and a single EKM credential could map multiple logins. I think you should do more tests on it.

    Here is a document about EKM: http://technet.microsoft.com/en-us/library/bb895340.aspx

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, September 08, 2009 9:46 AM
  • Hi Chris,

    Have a try to create a Windows Group as a SQL Server Login, map a single EKM credential to that Windows Group, now you can add users to the Windows Group, in this way, and a single EKM credential could map multiple logins. I think you should do more tests on it.

    Here is a document about EKM: http://technet.microsoft.com/en-us/library/bb895340.aspx

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thanks for the suggestion, but unfortunately it seems that you can't map an EKM credential to a login created from a Windows Group.

    I'll have a look at the document that you referenced and see if there's any useful advice.

    Thanks
    Chris

    MCTS : SQL Server 2005 ; MCITP : Database Developer
    Tuesday, September 08, 2009 9:29 PM
  • Hi Chris,

    I came across the same problem while trying to POC solution using EKM.

    Were you able to find a solution to it?

    thanks
    Roman

    Thursday, November 24, 2011 12:47 AM