none
Impersonation on server level doesn't work RRS feed

  • Question

  • Hi!
    We have DB with owner which is not sysadmin and stored procedure inside this DB which shoud be performed with sysadmin permissions
    (this sp performs sql server level operations). Why i can't do that with impersonating user as SA (execute as login = sa)?
    Seems permissions provided by impersonation are restricted on sql server level. Am I right?
    Thursday, August 1, 2013 10:21 AM

Answers

  • Hi!
    We have DB with owner which is not sysadmin and stored procedure inside this DB which shoud be performed with sysadmin permissions
    (this sp performs sql server level operations). Why i can't do that with impersonating user as SA (execute as login = sa)?
    Seems permissions provided by impersonation are restricted on sql server level. Am I right?

    A USER db_owner cannot just impersonate "sa" or any other sysadmin.

    That would create a major security risk, as then many applications could easily be exploited for an elevation of privilege attack.

    He can however impersonate any other User inside the database. But this does not help, because he would still be resticted to the database scope for security reasons.

    If he needs to impersonate a LOGIN (Not USER) you need to grant him that specific permission on a specific Login:

    USE master;
    GRANT IMPERSONATE ON LOGIN::SomeSysadmin to [User_db_owner];
    GO

    EDIT: but this only works for master-db!

    Andreas


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog:www.insidesql.org/blogs/andreaswolter

    Friday, August 2, 2013 2:56 PM

All replies

  • What error are  you getting? Make sure that 'sa' login is enabled. Please show us the code of stored procedure.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, August 1, 2013 1:31 PM
  • Some server-level actions require changing to the master database. Might be part of the problem.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Thursday, August 1, 2013 4:48 PM
  • Hi!
    We have DB with owner which is not sysadmin and stored procedure inside this DB which shoud be performed with sysadmin permissions
    (this sp performs sql server level operations). Why i can't do that with impersonating user as SA (execute as login = sa)?
    Seems permissions provided by impersonation are restricted on sql server level. Am I right?

    A USER db_owner cannot just impersonate "sa" or any other sysadmin.

    That would create a major security risk, as then many applications could easily be exploited for an elevation of privilege attack.

    He can however impersonate any other User inside the database. But this does not help, because he would still be resticted to the database scope for security reasons.

    If he needs to impersonate a LOGIN (Not USER) you need to grant him that specific permission on a specific Login:

    USE master;
    GRANT IMPERSONATE ON LOGIN::SomeSysadmin to [User_db_owner];
    GO

    EDIT: but this only works for master-db!

    Andreas


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog:www.insidesql.org/blogs/andreaswolter

    Friday, August 2, 2013 2:56 PM
  • You can use any login with sysadmin privilege in “with execute as” clause besides sa.

    It works if you make database TRUSTWORTHY. Please note that it is security hole.

    ALTER DATABASE Test SET TRUSTWORTHY ON
    
    go
    
    alter PROCEDURE dbo.testproc
    with execute as 'Login_with sysadmin_priv'
    as
    -- code that requires sysadmin access


    Monday, August 5, 2013 11:30 PM
  • You can use any login with sysadmin privilege in “with execute as” clause besides sa.

    It works if you make database TRUSTWORTHY. Please note that it is security hole.

    ALTER DATABASE Test SET TRUSTWORTHY ON
    
    go
    
    alter PROCEDURE dbo.testproc
    with execute as 'Login_with sysadmin_priv'
    as
    -- code that requires sysadmin access

     

    This actually only works under the condition that the database is also owned by a sysadmin.

    I would not like to get into details of another possibility, because as you are saying, this is a setup which opens for an elevation of privilege attack (in fact I  just demoed that several weeks ago at SQLSaturday)

    Anyways.. I guess we agree that this is not an advisable method.

    The clean way is to Grant the permission to do that (“Impersonate”) to just a specific user.

     


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


    Wednesday, August 7, 2013 9:07 AM
  • Andreas,

    Yes, dbo has to be sysadmin when TRUSTWORTHY set up.

    Does your solution work inside storage procedure? Please give example.

    Wednesday, August 7, 2013 8:50 PM
  • There is another way to execute storage procedure under sysadmin privileges.

    1. Create certificate in master database.
    2. Backup certificate and private key to a file system.
    3. Create login from certificate.
    4. Add that login to sysadmin role.
    5. Restore certificate with private key in Test database.
    6. Sign storage procedure by certificate in Test database.
    7. Drop private key from certificate to prevent other objects to be signed.

    It is possible to add private key to certificate when it is needed.

    Please note that certificate signature is lost when storage procedure is altered and it is not longer executed under sysadmin.  It prevents malicious code injection.

    use master
    go
    CREATE CERTIFICATE sysadmincert 
    ENCRYPTION BY PassWORD = 'PassW0rd'
    WITH SUBJECT = 'sysadmin'
    go
    
    BACKUP CERTIFICATE sysadmincert 
    TO FILE = 'c:\Temp\sysadmincert.crt'
        WITH PRIVATE KEY ( DECRYPTION BY PassWORD = 'PassW0rd',
        FILE = 'c:\Temp\sysadmincert.privkey' , 
        ENCRYPTION BY PassWORD = 'PassW0rd2' );
    
    go
    CREATE LOGIN sysadminlogin FROM CERTIFICATE sysadmincert;
    go
    EXEC sp_addsrvrolemember sysadminlogin, 'sysadmin';
    go
    
    USE Test
    go
    CREATE CERTIFICATE sysadmincert 
        FROM FILE = 'c:\Temp\sysadmincert.crt'
        WITH PRIVATE KEY (FILE = 'c:\Temp\sysadmincert.privkey', 
        ENCRYPTION  BY PassWORD = 'PassW0rd',
        DECRYPTION BY PassWORD = 'PassW0rd2')
    go
    
    ADD SIGNATURE TO dbo.StorageProcName
    BY CERTIFICATE sysadmincert WITH PassWORD = 'PassW0rd' 
    
    go
    
    ALTER CERTIFICATE sysadmincert REMOVE PRIVATE KEY
    

    Wednesday, August 7, 2013 9:24 PM

  • Does your solution work inside storage procedure? Please give example.

    sure

    just like that:

    CREATE PROC ServerLevelCode.RunOnServerAsSysAdmin
    AS
    -- Do stuff locally
    SELECT SUSER_SNAME()
    -- Go Sysadmin
    EXECUTE AS LOGIN = 'SomeSysadmin'
    SELECT SUSER_SNAME()
    REVERT
    -- Do stuff locally again
    GO

    And yes, using certificates is another approach and would give the same results.


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

    Wednesday, August 7, 2013 9:33 PM

  • Does your solution work inside storage procedure? Please give example.

    sure

    just like that:

    CREATE PROC ServerLevelCode.RunOnServerAsSysAdmin
    AS
    -- Do stuff locally
    SELECT SUSER_SNAME()
    -- Go Sysadmin
    EXECUTE AS LOGIN = 'SomeSysadmin'
    SELECT SUSER_SNAME()
    REVERT
    -- Do stuff locally again
    GO

    And yes, using certificates is another approach and would give the same results.


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


    It opens a possibility for db_owner and db_ddladmin to modify SP code and promote itself to sysadmin.

    I definitely advocate signing objects by certificate. But it is a matter of choice.

    Thursday, August 8, 2013 6:58 PM
  • And yes, using certificates is another approach and would give the same results.


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


    It opens a possibility for db_owner and db_ddladmin to modify SP code and promote itself to sysadmin.

    I definitely advocate signing objects by certificate. But it is a matter of choice.

    I aggree partly on that:

    Only the person which has been granted the appropriate permissions for impersonation could actually run this code.

    GRANT IMPERSONATE ON LOGIN::SomeSysadmin to [User_db_owner];

    All others should not be allowed to even see this procedure. Even if they would be able to execute it, they would get the usual deny.

    Of course, if someone has ALTER permissions, he could inject code.

    And there your certificate comes into play. Absolutely true and much better :-)

    But I was answering the original question which was/is about Impersonation and why and how it would work. And this is the only way I would dare to say.

    This other thread might also be helpful: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/9fa8ca12-237c-42e3-b3e8-4338756e8fc4/confusion-on-asymmetric-key


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

    Thursday, August 8, 2013 7:14 PM