none
Why create a login from certificate or asymmetric key? RRS feed

  • Question

  • Greetings. Using both 2005 and 2008, I'm not seeing the benefit to doing either of these. Yes, I know I can create a DB user without the need to first create the login using the cert, but I dont really see that as any huge plus.

    I see lots on the web for proper syntax, etc., but really nothing about the true benefits of doing this.

    I'm clearly missing something here, can someone please assist?

    Thanks!

     


    TIA, ChrisRDBA
    • Moved by Tom Phillips Tuesday, December 21, 2010 9:21 PM Security question (From:SQL Server Database Engine)
    Tuesday, December 21, 2010 4:01 PM

Answers

  • Hi,

    Logins created from certificates or asymmetric keys are used for code signing purposes.

    They cannot be used to connect to SQL Server. Additionally, while creating a login from a certificate or asymmetric key, the certificate or asymmetric key needs to exist in the master database.

    To understand more on module signing check out the following links -

    http://msdn.microsoft.com/en-us/library/ms345102.aspx
    http://blogs.msdn.com/b/lcris/archive/2005/06/15/429631.aspx

    Hope this helps!


    Don Pinto, Microsoft, SQL Server Engine. ---------------------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights. ---------------------------------------------------------
    Wednesday, December 22, 2010 12:10 AM
  • To add on to Don's response, check out Erland's article on granting access through stored procedures at http://www.sommarskog.se/grantperm.html.  This article illustrates scenarios where principals created from certificates can extend module permissions without granting elevated permissions directly to users:

    1)  certificate logins for server-level permissions like ADMINISTER BULK OPERATIONS

    2)  certificate users for intra-database permissions when the ownership chaining is not possible (dynamic SQL, different owners)

    3)  certificate users for cross-database permissions instead of enabling cross-database chaining

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Wednesday, December 22, 2010 1:42 AM
    Moderator

All replies

  • Hi,

    Logins created from certificates or asymmetric keys are used for code signing purposes.

    They cannot be used to connect to SQL Server. Additionally, while creating a login from a certificate or asymmetric key, the certificate or asymmetric key needs to exist in the master database.

    To understand more on module signing check out the following links -

    http://msdn.microsoft.com/en-us/library/ms345102.aspx
    http://blogs.msdn.com/b/lcris/archive/2005/06/15/429631.aspx

    Hope this helps!


    Don Pinto, Microsoft, SQL Server Engine. ---------------------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights. ---------------------------------------------------------
    Wednesday, December 22, 2010 12:10 AM
  • To add on to Don's response, check out Erland's article on granting access through stored procedures at http://www.sommarskog.se/grantperm.html.  This article illustrates scenarios where principals created from certificates can extend module permissions without granting elevated permissions directly to users:

    1)  certificate logins for server-level permissions like ADMINISTER BULK OPERATIONS

    2)  certificate users for intra-database permissions when the ownership chaining is not possible (dynamic SQL, different owners)

    3)  certificate users for cross-database permissions instead of enabling cross-database chaining

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Wednesday, December 22, 2010 1:42 AM
    Moderator
  • ChrisR, for a thorough examination of the How / Why / When of signature-based Logins, granular permissions, and avoiding TRUSTWORTHY, Impersonation, and Cross-Database Ownership Chaining, please see the site I created to focus on this topic:

    Module Signing Info

    Friday, November 10, 2017 6:07 PM