Post reply

Report server and database server on two different m/c

  • March 6, 2012 at 5:21 am

    #258125

    Hi All,

    I have a report server installed on one server and database on other server.

    I have depolyed reports on the report server. The datasource pointing to the database which on other server.

    But users are not able to view the report. It give datasource not accessible / Inpersonation failed.

    I have given all the required rights in report manager and in database.

    If I put the user in local admin group then it works fine.

    This is a production server so I can not put every required user in the local admin group.

    Need help to resolve this issue.

    Thanks in Advance

  • March 6, 2012 at 6:05 am

    #1455240

    what account is being used as the data source account?

    from what you are saying it seems like its using windows authentication with the users account?

    this is the most secure way but will require anyone who has/needs access to SSRS to have a login in SQL.

    Best option would be to create an AD Security group, add in the people, then give that group the access it needs in SQL, that way you just add people to the group instead of adding them into SQL and configuring the access every time a new user joins up.

  • March 6, 2012 at 11:25 pm

    #1455744

    The account is a domain user account.

    The user have select rights which are required to populate data for the reports.

    As you said even though if I create a AD group I will have to add it in local admin group only. This scenario is already here.

    Even I tried to create same type of scenario in my test environment. I have report server on one server and db on different server.

    Here is the error

    An error occurred during client rendering.

    An error has occurred during report processing.

    Cannot create a connection to data source 'Dstest2'.

    For more information about this error navigate to the report server on the local server machine, or enable remote error

    I have the full access on the db side.

    In the report manager I have sysadmin previleges.

  • March 6, 2012 at 11:53 pm

    #1455751

    please post a screenshot of the settings for the datasource dstest2

  • March 7, 2012 at 2:42 am

    #1455815

    Thanks for you quick response.

    Please find the attached screen shot.

    I tried all option but after googling I found to use store credential in report server DB. Still its not working.

  • March 7, 2012 at 2:46 am

    #1455819

    i am guessing its down to having the impersonation box checked as once its connected as domain\user it then tries to run the query as domain\user1, so if domain\user1 doesnt have access thats why you getting the error.

  • March 7, 2012 at 4:53 am

    #1455890

    The domain\user1 is the admin there.

    This is solved some how I dont know I added my acount in local admin group on the server where the report server is installed. As I said on production I can not put every user or group in local admin group b'coz of low security.

    Still I am stuck to the point, is there any way that without adding the domain user in local admin group to access the report just as if the report server and DB on the same server then we dont need to add user or group to local admin.

    Other way is create a AD group and again add it to admin group again it is low security issue.

    Any thought on this please advice.

    Thanks

  • March 7, 2012 at 5:23 am

    #1455902

    the data source is set to connect as domain\username, once the connection is made, it then switches from domain\username to domain\vinod.saraswat for example

    if that second account doesnt have the access it needs to run the report you will get permissions issues.

    so there are two options, untick impersonation or create an ad group which can do what it needs to do. it doesnt have to be a local admin or sysadmin, but must be able to execute whatever you have told the report to do.

    so if your using stored procs, it must be able to execute that stored proc, if its directly selecting from a table, it must be able to select from that table

  • March 7, 2012 at 9:43 pm

    #1456345

    Okay will try this.

    Thanks for your support and quick response.

  • March 8, 2012 at 9:59 am

    #1456652

    You may be running into Kerberos authentication issues.

    See this article for more information: http://www.sqlservercentral.com/articles/Security/65169/

    It looks very similar to your situation.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply