none
SSRS 2016 How do avoid the error "Cannot impersonate user for data source" for a corporate SSRS installation RRS feed

  • Question

  • I have been working on this for over a week now and I can't believe there isn't a best practice for configuring this. If I used stored credentials it seems like a nightmare to have to create a service account for each SQL Server role in order to pass the rights a user would have based on the AD group they are a member of.

    So if I create a folder named "Finance on my SSRS server"

    I give the AD role "Finance Dept" the SSRS role type "Browser" on the folder

    Then on SQL Server let's assume that AD role "Finance Dept" has access to a some stored procs, views.. on the SQL server.

    Am I supposed to create a new service account in AD "svc_SSRS_Finance" and give that account log on locally to the SSRS server? That seems like a ton of work when you figure I might have to request these parts to completed by several teams; security for AD account, server engineers to allow log on locally...

    Thoughts.

    Wednesday, November 16, 2016 6:56 PM

Answers

  • Hi Developer_46038,

    Based on error message, if you would like to use Stored Credentials, please don’t check the option: Log in using these Credentials. See: Specify Credential and Connection Information for Report Data Sources.

    And if you want to grant “Allow log on locally” Permissions to domain user accounts, please refer to following steps:

    1.On the report server computer, in Administrative Tools, open Local Security Policy.
    2.Under Security Settings, expand Local Policies, and then click User Rights Assignment.
    3.In the details pane, right-click Allow log on locally and then right-click Properties.
    4.Click Add User or Group.
    5.Click Locations, specify a domain or other location that you want to search, and then click OK.
    6.Enter the Windows account for which you want to allow interactive login, and then click OK.
    7.In the Allow log on locally Properties dialog box, click OK.
    8.Verify that the account you selected does not also have deny permissions:
    a. Right-click Deny log on locally and then right-click Properties.
    b. If the account is listed, select it and then click Remove.

    But if the user need to access the database, it’s necessary to grant the user corresponding permission. You can also try to grant a group permission to access in the SQL Server. See below:

    If I misunderstand your requirements, please correct me and share more information.
    Best Regards,
    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 17, 2016 9:48 AM

All replies

  • The user needs to have log on locally for the stored windows login account.

     


    http://technet.microsoft.com/en-us/library/cc161372.aspx

     Refer in following thread

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e2d44eea-a333-4720-9430-e93a2d534058/cannot-impersonate-user-for-data-source?forum=sqlreportingservices


    Please click Mark As Answer if my post helped.

    Thursday, November 17, 2016 12:24 AM
  • Hi Developer_46038,

    Based on error message, if you would like to use Stored Credentials, please don’t check the option: Log in using these Credentials. See: Specify Credential and Connection Information for Report Data Sources.

    And if you want to grant “Allow log on locally” Permissions to domain user accounts, please refer to following steps:

    1.On the report server computer, in Administrative Tools, open Local Security Policy.
    2.Under Security Settings, expand Local Policies, and then click User Rights Assignment.
    3.In the details pane, right-click Allow log on locally and then right-click Properties.
    4.Click Add User or Group.
    5.Click Locations, specify a domain or other location that you want to search, and then click OK.
    6.Enter the Windows account for which you want to allow interactive login, and then click OK.
    7.In the Allow log on locally Properties dialog box, click OK.
    8.Verify that the account you selected does not also have deny permissions:
    a. Right-click Deny log on locally and then right-click Properties.
    b. If the account is listed, select it and then click Remove.

    But if the user need to access the database, it’s necessary to grant the user corresponding permission. You can also try to grant a group permission to access in the SQL Server. See below:

    If I misunderstand your requirements, please correct me and share more information.
    Best Regards,
    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 17, 2016 9:48 AM
  • I understand that. AS I stated in the OP I want to avoid having to login to a server in order to add users to the "Allow Log on Locally" security point.

    Thursday, November 17, 2016 7:31 PM
  • Thanks, and that is what I have done. 

    Does anybody give "Domain\Everyone" the "Allow log on locally" rights assignment?

    Thursday, November 17, 2016 7:35 PM
  • Hi Developer_46038,

    Hi based on my research, to grant a user or group the right to log on locally to the domain controllers in the domain, please see here.

    And, if the issue just about Allow log on locally for ”Domain\Everyone”, please close this topic and recreate a thread in Windows Server Forum. You will get more professional information about this issue.

    Thanks for your understanding and support.

    Best Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Pirlo Zhang Friday, November 18, 2016 10:09 AM
    Friday, November 18, 2016 10:08 AM
  • I am not sure how this went the way of the domain controller. I am talking about granting everyone access to log on locally to the Report Server. 

    Friday, November 18, 2016 10:55 PM