none
Confusion on asymmetric key RRS feed

  • Question

  • Hi!

     Please help me to eliminate some confusion on usage of asymmetric keys.

     The case is the following:

     1. There is a server login "myLogin", it has public server role
     2. This login is "db_owner" of "myDatabase"
     3. I want to allow this login to execute stored procedure "mySp" of "myDatabase" that requires server-level permissions, let’s say "set trustworthy on other database"

     4. To achieve this I am going to sign the procedure:

        Under "sysadmin" user, using "master" database

        a) Create asymmetric key "myKey" from  file
        b) Create login "keyLogin" from this key
        c) Grant "Control Server" permission to this "keyLogin"  

       Using "myDatabase"
                  

       d) Create the same asymmetric key from the same file
       e) Add Signature to "mySp" by asymmetric key "myKey"
     

    After that everything works file.
    But under login "myLogin" (because I am "db_owner") I can modify the signed procedure,
    and resign it with the asymmetric key.
     
    After that everything again works file. So as “sysadmin” I don’t have protection against this procedure.  What have I missed? 

    Off course I can add signature with option "WITH PASSWORD", that will probably solve the problem.

    But to me it would have more sense if I under "myLogin"  I could not resign the procedure?

    Please comment


    Wednesday, August 7, 2013 1:44 PM

Answers

  • 1)

    And is has to be the exact same key I suppose..?

    2)

    No, the key has to be in the same database

    3)

    Depending on the real needs, you could deny this user all ddl Actions within a certain schema, so he cannot alter this Procedure at all.

    You can also just prevent using that key by denying him the Control permission on it like that:

    DENY CONTROL ON ASYMMETRIC KEY::[YourKeyName] TO [DBOwner_User_Or_Role];

    4)

    If you make sure that the key is not protected by the database master key as well, this would in fact prevent the user from using the key as well.

    It might actually be easier for your needs.

    5)

    I am not sure what you mean by b)

    c) is what we are talking about here. In general I would use a custom server-role (SQL Server 2012 onwards) and either grant it the necessary permissions (for example that could be “Alter Any Database”.

    For certain actions I would create stored Procedures which reside in either a special database or even master. - just like you are doing here.

    I would not have this user be a db_owner though, so certificates would not be needed.

    There are lots of aspects to it though, and different needs lead to different architectures.

    If Server Roles are not available this works just the same with a standard Login.


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Wednesday, August 7, 2013 5:34 PM
  • You’re welcome.

    4)

    You got me there. I had Certificates in the back of my head when I wrote this I figure. An asymmetric key can only be encrypted by either the database master key or a password, not both. So you are good here :-)

    3)

    dbo <> (member of) db_owner From all I read your user is not dbo, but “just” member of db_owner. And that’s an important difference as db_owner CAN be denied.

    Another option just came to my mind, but I guess it does not work for you either:

    You could remove the private key from the Key after having used it for signing. But I guess to want to re-use it for other things. (Which again, I personally would not do)

    5)

    You are right, but that's not all :)

    For Server-Level actions these procedures would need to be in msdb, or that database would need to be trustworthy. For a specific database where I have control of the exact code inside, that setting (TRUSTWORTHY) is actually meant for. For other databases (which contain code that the administrator didn’t write himself) I would usually advise against doing that and recommend certificates (or asymmetric key if you like). I guess you see why I like to take things apart..

    This other thread might also be of interest for you:

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/79391dd7-9afb-4bfb-aa90-1fc1cc82b305/impersonation-on-server-level-doesnt-work


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Wednesday, August 7, 2013 9:09 PM

All replies

  • A db_owner can of course do ANYTHING inside his database. Under circumstances even on the whole server, if "his" database is Trustworthy and at the same time owned by lets say sa.

    So this is something to watch out for.

    If you do not want him to be able to re-sign a proc, then this proc + key must not be under his control (!).

    Either deny him those actions, or put the proc into a different database altogether. That will certainly also keep the "borders" clearer.


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Wednesday, August 7, 2013 2:00 PM

  • 1. Put the proc into a different database altogether, is not appropriate solution, 
    because I want to use this key to sign clr library in my database as well.


    2. Is it possible to sign stored procedure in my database by asymmetric key that is located only in "master" database?

    3. What exactly permissions should I deny my login of ?

    4. If I add signature with password, will not it be a good enough protection?

    5. More generally, what is the best practice for such case:
      
       a) There is a sql server host
       b) The installation will be done under "sa" user. (the installation will be signed with VeriSign certificate, so I can trust it)
       b) The installation will create "limited" server login and a database. 
    This login will be db_owner of this database. All operations will be done under this login. This login needs to execute some operations requiring high server-level permissions. 
    How can I achive this?
    Wednesday, August 7, 2013 3:03 PM
  • 1)

    And is has to be the exact same key I suppose..?

    2)

    No, the key has to be in the same database

    3)

    Depending on the real needs, you could deny this user all ddl Actions within a certain schema, so he cannot alter this Procedure at all.

    You can also just prevent using that key by denying him the Control permission on it like that:

    DENY CONTROL ON ASYMMETRIC KEY::[YourKeyName] TO [DBOwner_User_Or_Role];

    4)

    If you make sure that the key is not protected by the database master key as well, this would in fact prevent the user from using the key as well.

    It might actually be easier for your needs.

    5)

    I am not sure what you mean by b)

    c) is what we are talking about here. In general I would use a custom server-role (SQL Server 2012 onwards) and either grant it the necessary permissions (for example that could be “Alter Any Database”.

    For certain actions I would create stored Procedures which reside in either a special database or even master. - just like you are doing here.

    I would not have this user be a db_owner though, so certificates would not be needed.

    There are lots of aspects to it though, and different needs lead to different architectures.

    If Server Roles are not available this works just the same with a standard Login.


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Wednesday, August 7, 2013 5:34 PM
  • Andreas,  
    thank you for your answers, they really help to gain the understanding

    4)
    >>If you make sure that the key is not protected by the database master key as well, this would in fact prevent the user from using the key as well. 
    But what if the key is protected by the database key as well? It seems protection by password has preference over protection by database master key?
    If my database has the master key, I can create asymmetric key without specifying password.  But if I have specified it, I am not allowed to use this key without specifying the password. 

    3)
    >> DENY CONTROL ON ASYMMETRIC KEY::[YourKeyName] TO [DBOwner_User_Or_Role];
    Unfortunately this will not work out, because according to msdn:
    Permission check is bypassed if the user is the dbo user in the current database.

    5)
    >>I would not have this user be a db_owner though, so certificates would not be needed.
    I am not sure I understand how I can execute stored procedure under "limited" login with "sysadmin" rights. 
    As I understand it's possible only using impersonation or using certificates/asymmetric keys.

    Alexander


    Wednesday, August 7, 2013 7:56 PM
  • You’re welcome.

    4)

    You got me there. I had Certificates in the back of my head when I wrote this I figure. An asymmetric key can only be encrypted by either the database master key or a password, not both. So you are good here :-)

    3)

    dbo <> (member of) db_owner From all I read your user is not dbo, but “just” member of db_owner. And that’s an important difference as db_owner CAN be denied.

    Another option just came to my mind, but I guess it does not work for you either:

    You could remove the private key from the Key after having used it for signing. But I guess to want to re-use it for other things. (Which again, I personally would not do)

    5)

    You are right, but that's not all :)

    For Server-Level actions these procedures would need to be in msdb, or that database would need to be trustworthy. For a specific database where I have control of the exact code inside, that setting (TRUSTWORTHY) is actually meant for. For other databases (which contain code that the administrator didn’t write himself) I would usually advise against doing that and recommend certificates (or asymmetric key if you like). I guess you see why I like to take things apart..

    This other thread might also be of interest for you:

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/79391dd7-9afb-4bfb-aa90-1fc1cc82b305/impersonation-on-server-level-doesnt-work


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Wednesday, August 7, 2013 9:09 PM
  • After storage procedure is signed by myKey you can drop private key. It prevents anyone from signing any other object in database. You can restore private key from the file any time.

    ALTER ASYMMETRIC KEY myKey  REMOVE PRIVATE KEY

    Thursday, August 8, 2013 6:45 PM
  • After storage procedure is signed by myKey you can drop private key. It prevents anyone from signing any other object in database. You can restore private key from the file any time.

    ALTER ASYMMETRIC KEY myKey  REMOVE PRIVATE KEY

    which is what I was saying under (3)

    But watch out, I think there is a trap:

    As far I know there is no way to export the private key of an asymetric key to a file

    this only works for certificates. (Part of why I prefer those)

    Please tell me if I am wrong.


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com


    Thursday, August 8, 2013 7:05 PM
  • Hi!

    Thank you, removing private key is exactly what I need. 
    After database has been deployed and setup, the key shouldn't be used any more. 

    As I understand I will be able to restore private key only by deletion the whole asymmetric key and re-loading it again.
    It will probably require deletion of all signed procedures, and re-assigning them again?

    One more question, is it possible to create (load) asymmetric key from binary format?

    For now I am loading it in such way:

    CREATE ASYMMETRIC KEY MyKey
    FROM FILE = N'path to my ConfirmitKeyPair.snk'

    It would be nice to load it something like this

    CREATE  ASYMMETRIC KEY MyKey
        FROM BINARY = 0x308201B53082011E

    It seems to be impossible, at least I haven't found out how do it.


    • Edited by Alexander-z Thursday, August 8, 2013 8:53 PM
    Thursday, August 8, 2013 8:53 PM
  • After storage procedure is signed by myKey you can drop private key. It prevents anyone from signing any other object in database. You can restore private key from the file any time.

    ALTER ASYMMETRIC KEY myKey  REMOVE PRIVATE KEY

    which is what I was saying under (3)

    But watch out, I think there is a trap:

    As far I know there is no way to export the private key of an asymetric key to a file

    this only works for certificates. (Part of why I prefer those)

    Please tell me if I am wrong.


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com



    You are right. There is no way to export asymetric key from SQL server. It has to be dropped and recreated from file again. That is too much work.
    Thursday, August 8, 2013 8:59 PM
  • Thank you, removing private key is exactly what I need. 
    After database has been deployed and setup, the key shouldn't be used any more. 

    As I understand I will be able to restore private key only by deletion the whole asymmetric key and re-loading it again.
    It will probably require deletion of all signed procedures, and re-assigning them again?

    One more question, is it possible to create (load) asymmetric key from binary format?

    In that case you can in fact remove the private key.

    But be aware, that before re-creating the key, you have to remove the signatures from all procedures and other entities possibly using it. - which is what Fedor Pustovachenko just said as well.

    You are probably better of by considering it th same as "crating and using a completely new key" instead of "reusing the old key"

    A Key cannot be created from binary. Only from a file and of course in place. That's another difference against certificates.

    You have the complete syntax here: http://technet.microsoft.com/en-us/library/ms174430.aspx


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Thursday, August 8, 2013 10:08 PM