Cannot connect to a datasource error | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Cannot connect to a datasource error

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
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..
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
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?
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
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
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
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?
No there is no logon screen, it just goes straight into the reports menu. Regards
Toni Chaffin
aka Toni
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?
Morning smy. I shall go to that right now and carry out your suggestions Thanks Toni Chaffin
aka Toni
No problem <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /> <br />Do let me know the outcome then.
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |