SQL Server Performance

Cannot connect to a datasource error

Discussion in 'SQL Server Reporting Services' started by Toni, May 30, 2006.

  1. Toni New Member

    I have been working on a report for a while now and just when I go to deploy it, the bloody thing stops working.

    I have been testing it mainly under my account (Admin + Domain Admin) and peridocally under another admins account. I have also been testing it under a dummy normal account (domain user only) and up until now it has worked fine.

    Originally it did not work although I found that if I Rebuilt & Redeployed the project to the server and then started Reporting services and selected the datasource1 and supplied my credentials and checked Use as Windows Credentials and also the Impersonate check, it all worked perfectly.

    However now this does not appear to be the case

    The exact error I get is

    'An error has occurred during report processing. (rsProcessingAborted)
    Cannot create a connection to data source 'DashboardSQL-2K5'. (rsErrorOpeningConnection)
    For more information about this error navigate to the report server on the local server machine, or enable remote errors'

    The connection string under the properties of datasource1 are

    INTEGRATED SECURITY=SSPI;INITIAL CATALOG=DashboardSQL-2K5;DATA SOURCE=XXXXXXXX;

    XXXXXXXXX is obviously the name of my server blanked out

    Please help as I wish to deploy this report and it is just sods law that it stops working today.

    Regards


    Toni Chaffin
    aka Toni
  2. smy New Member

    Can you accesshttp://XXXXXXXXX/reports where XXXXXXXXX is the name of your server without any error at the workstation which you are using to access the reports?

    If that's ok, maybe you can try changing data source=XXXXXXXXX to data source=(local) and see if you get the same error..
  3. Toni New Member

    I personally can access the reports as an administrator as can another user who is also an administrator.

    When a normal user logs ontohttp://XXXXXXXX/Reports/ they can see the reports and navigate through the infrastructure, it is when they click on a specific report that is when the error occurs. It is not only my reports, the sample one throws a similar errors too which is somewhat confusing as they normal work fine.

    Regards


    Toni Chaffin
    aka Toni
  4. smy New Member

    At thehttp://XXXXXXXX/Reports/, you got another folder that is your report folder. You click in and you should see the datasource in there. Click in the datasource and see if the credentials are correct.

    Also, are the users in the same domain as the server?

    When the users openhttp://XXXXXXXX/Reports/ link, is there a popup login screen for them to login before the page can be seen?
  5. Toni New Member

    Almost managed to screw it up totally then, although this may assist in trying to resolve the problem.

    I checked in Dev Studio the crednetials of datasource named :- DasboardSQL-2K5.rds and set it to use a specific username etc. I placed my details in the box tonic and password as appose to Intergrated Security option and then redeployed etc and go the same error myself. I tried to prefix the tonic with domainname onic etc and got the same error in report manager. I then went back to it, reselected intergrated, opened SQL Server Management Studio - Reporting Services. Selected the report concerned and then expanded the options under datasoucres. The first thing to mention is that I have two datasources listed, the one we have already talked about and then one called 'Datasource1'.

    Historically, I have always had to, once I have rebuilt and redeployed a report, changed the options under datasource1 to show 'Credentials stored securly on the report server' and then enter my username and password before I could run the report. Also the 'Use as Windows credentials when connecting to the data source' and also 'Impersonate the authenticated user after a connection has been made to the data source'.

    I am 99.9% sure that I use to be able to run the reports on my test logon once I had added this user data etc in this location historically (two weeks ago plus).

    So, I am back to where I was, although I have yet to solve the non admin user issue.

    Regards


    Toni Chaffin
    aka Toni
  6. Toni New Member

    If it helps, I have just created the user toxic under SQL Security and copied the profile of another user. It does not resolve the problem but it does get me through to the next screen which has the error message of

    An error has occurred during report processing. (rsProcessingAborted)
    Query execution failed for data set 'srcqry_weeklydatadate'. (rsErrorExecutingCommand)
    For more information about this error navigate to the report server on the local server machine, or enable remote errors

    So it looks like it is getting the connection now, it is however failing on the first parameter query that populates the serach criteria. There are no permissions are there when it comes to select queries etc?

    If it makes a difference, the select query is

    SELECT DISTINCT datadate AS Weekly_Date
    FROM src_terrier
    ORDER BY Weekly_Date DESC

    but I can't see that makes a difference.

    Regards



    Toni Chaffin
    aka Toni
  7. Toni New Member

    If needed, and I certainly do not mind the security implications of this solution, is it possible to change the security so that 'No Credentials are required to connect to the Datasource', that way it is open season for everyone initially until such time as I can resolve the issue.

    This would also help me with my lack of memory when it comes to having to go into Reporting Services everytime and resetting the credentials.

    Regards



    Toni Chaffin
    aka Toni
  8. smy New Member

    Would like to know...When the users openhttp://XXXXXXXX/Reports/ link, is there a popup login screen for them to login before the page can be seen?
  9. Toni New Member

    No there is no logon screen, it just goes straight into the reports menu.

    Regards


    Toni Chaffin
    aka Toni
  10. smy New Member

    ok..go to control panel where the sql report server is installed
    expand to default web site of the Internet Information Service (IIS) and you will see the ReportServer.
    right click the ReportServer folder and select properties.
    Go to the directory security and check on the enable anonymous access. Change the username to administrator.
    Do the same to Reports Folder at the IIS.
    Make sure administrator has the rights for reporting services by going tohttp://XXXXXXXX/Reports/ link.
    Click on Properties tab and make sure the administrator role assignment is in there. Else, do create and gives rights to the administrator.

    by the way, your reports do have any custom code/class file to link to?
  11. Toni New Member

    Morning smy. I shall go to that right now and carry out your suggestions

    Thanks



    Toni Chaffin
    aka Toni
  12. smy New Member

    No problem <img src='/community/emoticons/emotion-1.gif' alt=':)' /> <br />Do let me know the outcome then.

Share This Page