none
Data Source:Impersonate the authenticated user after....and SETUSER RRS feed

  • Question

  • Hi,

    I am setting up a SSRS 2008 R2 Data Source with following settings

    "Credentials stored securely in the report server"
    "Use as Windows credentials when connecting to the data source" (Checked)
    "Impersonate the authenticated user after a connection has been made to the data source"(Checked)

    The domain account used in the Data Source is a member of "db_owner" role of the database, which the connection string is pointing to.

    When I test the connection by pressing "Test Connection", the error I get:

    "Setuser failed because of one of the following reasons: the database principal 'Domain\user1'
    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."


    If I uncheck "Impersonate the authenticated user after a connection has been made to the data source", then the connection is successful, but I need this to be checked so that the reports get executed under the context of the users who run the reports (impersonation).

    Could someone shed some light? From BOL, I see that as long as the stored domain account is a member of the "db_owner" role of the given database, it should be able to impersonate another user (via SETUSER). I have made the domain account a member of the "db_owner", but still get the above error.

    Moreover, under SETUSER documentation, I see the following.

    Only use SETUSER with SQL Server users. SETUSER is not supported with 
    Windows users. When SETUSER has been used to assume the identity of 
    another user, any objects that the impersonating user creates are owned 
    by the user being impersonated.
    
    http://msdn.microsoft.com/en-us/library/ms186297%28v=sql.100%29.aspx



    • Edited by SuraMan Tuesday, June 26, 2012 6:07 AM
    Tuesday, June 26, 2012 6:03 AM

All replies

  • Hi James_M,

    From your description, the issue may occur if your data source doesn’t support impersonate. When configuring the data source properties, we can check the “Impersonate the authenticated user after a connection has been made to the data source” option to allow delegation of credentials only if the data source supports impersonation. For SQL Server databases, this option sets the SETUSER function.

    In the issue, if you want to configure the data source connection under the current user, you can use "Windows integrated security" instead of "Credentials stored securely in the report server".

    Reference:
    Data Sources Properties Page (Report Manager)

    Hope this helps.

    Regards,
    Mike Yin

    Sunday, July 1, 2012 11:58 AM
    Moderator
  • In the issue, if you want to configure the data source connection under the current user, you can use "Windows integrated security" instead of "Credentials stored securely in the report server".

    Reference:
    Data Sources Properties Page (Report Manager)

    Hope this helps.

    Regards,
    Mike Yin

    Hi Mike Yin,

    Due to double hop problem, I cannot use "windows integrated security" because Kerberos is not in place.

    Moreover, I noticed that if the domain account is given the "sysadmin" role, it can impersonate the report users at run time, however, giving sysadmin role is not acceptable due to security reasons.


    • Edited by SuraMan Sunday, July 1, 2012 2:54 PM
    Sunday, July 1, 2012 2:53 PM
  • Hi James_SM,

    Thanks for your posting.

    Based on my test, the error will occur if the domain user configured for stored credentials is not a member of the "sysadmin" server role. To get further help on the issue, I suggest that you create another thread on our SQL Server T-SQL forum:
    http://social.msdn.microsoft.com/Forums/en/transactsql/threads

    Meanwhile, you can also submite a feedback at https://connect.microsoft.com/SQLServer/.

    Regards,
    Mike Yin

    Monday, July 2, 2012 7:38 AM
    Moderator
  • Based on my test, the error will occur if the domain user configured for stored credentials is not a member of the "sysadmin" server role. To get further help on the issue, I suggest that you create another thread on our SQL Server T-SQL forum:

    http://social.msdn.microsoft.com/Forums/en/transactsql/threads

    Hi Mike Yin,

    According to MS Documentation (http://msdn.microsoft.com/en-us/library/ms186297%28v=sql.105%29.aspx), db_owner is enough, but it doesn't seem to be the case.

    I've already asked this in T-SQL forum.

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/6802fea4-b2df-4a86-9504-15e71c8b8bef

    Regards

    Monday, July 2, 2012 7:51 AM
  • I agree with James_SM, I cannot get this to work with only db_owner and have only been able to get setuser or reporting services impersonation to work using sysadmin.
    Wednesday, February 13, 2013 9:09 PM