none
SETUSER and db_owner, but error RRS feed

  • Question

  • Hi,

    I have logged on to a SQL 2008R2 server as "domain\user1", which is a member of the "db_owner" role of the database "database_1".

    After USE-ing to "database_1", when I try to do

    SETUSER 'domain\user2'

    I get the following error.

    Setuser failed because of one of the following reasons: the database principal 'domain\user2' does not exist, its corresponding server principal does not have server access, this type of database principal cannot be impersonated, or you do not have permission.

    However, I can use SQL Management Studio and use either "domain\user1" or "domain\user2" to connect to the same database and run queries.

    Moreover, even if I do

    SETUSER 'domain\user1'

    I still get the same error (for domain\user1).

    Could someone shed some light?

    The original problem that caused this is SSRS, which uses "SETUSER" to do impersonation. So, the use of alternative impersonation techniques such as "EXECUTE AS" does not solve the problem.





    • Edited by SuraMan Tuesday, June 26, 2012 7:47 AM
    Tuesday, June 26, 2012 7:40 AM

Answers

All replies

  • Does EXECUTE AS LOGIN or EXECUTE AS USER work for you?

    In such case, change whatever in SSRS to use EXECUTE AS instead. SETUSER is deprecated, so I doubt that this is something intrinsic in SSRS that uses SETUSER. (Although Microsoft has a poor record adhering to their own deprecations.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, June 26, 2012 8:26 AM
  • Hi Erland,

    I will check whether EXECUTE AS will work when I get back to work tomorrow, but SSRS cannot be changed to use "EXECUTE AS" since it is a built in feature.

    When we tick a checkbox from the front end to do impersonation, SSRS internally converts it to "SETUSER".

    For more info on this:

    http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/50606c90-71a2-45ee-8a68-ff4437ce07f0

    • Edited by SuraMan Tuesday, June 26, 2012 11:32 AM
    Tuesday, June 26, 2012 11:30 AM
  • Hm, there is a small chance that he was confused or just sloppy. More so, since I found this in Books Online:

    Only use SETUSER with SQL Server users. SETUSER is not supported with Windows users.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, June 26, 2012 10:02 PM
  • Hi Erland,

    The actual error message SSRS produces says.

    "Setuser failed because of one of the following reasons:......."

    The error message is identical to the error message I get after issuing SETUSER command through SQL Management Studio.

    EXECUTE AS LOGIN also fails. Error message is slightly different.

    "Cannot execute as the server principal because the principal "DOMAIN\User2" does not exist, this type of principal cannot be impersonated, or you do not have permission."

    However, Execute AS works when I try to execute as self.
    Eg. After Login as Domain\User1;
    * Execute As Login = 'Domain\User1' is successful.
    * Setuser 'Domain\User1' fails.






    • Edited by SuraMan Tuesday, June 26, 2012 11:58 PM
    Tuesday, June 26, 2012 11:57 PM
  • I now have it confirmed that SSRS uses SETUSER. Shame on you Microsoft!.

    Does EXECUTE AS USER work? (I believe that SETUSER is more akin to EXECUTE AS USER, although it's not fully clear exactly what it does.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, June 27, 2012 7:16 AM
  • I also got confirmation that SETUSER does not support Windows authentication, so you are in a dead end.

    I found a Connect item,
    https://connect.microsoft.com/SQLServer/feedback/details/751014/reporting-services-should-use-execute-as-instead-of-setuser-for-impersonation
    that you could vote for.

    If this is major showstopper for your organisation, that is not being able to impersonate a Windows user from SSRS, I suggest that you open a case for Microsoft. I don't have much hope, and you would have to able to discuss in business terms to get anywhere.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi NModerator Wednesday, June 27, 2012 10:38 PM
    • Marked as answer by SuraMan Thursday, June 28, 2012 12:15 AM
    Wednesday, June 27, 2012 10:16 PM
  • On the SQL Server 2008 R2 running on my workstation, SETUSER and IWA works. I can SETUSER to other logins (belong to co-workers who also connect to the same server). I am suspecting this is because I have sysadmin role on this server.


    I also found the reference below.

    “It’s important to note that in the above example jdoe must have the sysadmin role for the SETUSER ‘jschmoe’ command to complete successfully. If jdoe does not have sysadmin, the following error is thrown.”

    http://www.databasejournal.com/features/mssql/article.php/3863516/SQL-Server-Impersonation.htm

     

    I will arrange to upgrade the the domain account (Domain\User1) to sysadmin and see how it goes. Even if this works, the production DBAs won't like to give sysadmin role to a service account used in SSRS Data Sources.



    • Edited by SuraMan Thursday, June 28, 2012 12:25 AM
    Thursday, June 28, 2012 12:23 AM
  • Hi Erland,

    Are you able to double check from your sources at Microsoft about SETUSER not supporting IWA? The tests that we've just done told us that SETUSER supports IWA.

    We have just completed some testing on SQL 2008 R2.

    The observations were that if the service account (that attempts to impersonate report users) is a member of the sysadmin role, SETUSER works fine and supports integrated windows authentication (IWA). Otherwise, not.

    Contrary to the documentation, db_owner did not make any difference.

    So, it is a dead end because it is not allowed for service accounts to be in the sysadmin role due to obvious security issues.





    • Edited by SuraMan Thursday, June 28, 2012 7:26 AM
    Thursday, June 28, 2012 7:16 AM
  • "Does not support" is not the same "does not work". If it works, consider yourself lucky. If it does not work, Microsoft will just shrug their shoulders.

    But they should not shrug their shoulders for using commands they have deprecated themselves.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, June 28, 2012 7:44 AM