When my clients contact me for any issue, I always try to help them or redirect them to someone who can help them better than me. Once a client contacted me for an issue with SQL Server Reporting Services. As usual, I didn’t give up, but I investigated error message and applied a logical approach to fixing it. Let us see how to fix the error related to report server database.
THE PROBLEM
They have a project in Visual Studio and from where they are calling the report and viewing it on using a report viewer. Below is the error message they were receiving while accessing the report.
Could not connect to the report server http://localhost/ReportServer. Verify that the TargetServerURL is valid and that you have the correct permissions to connect to the report server.
ADDITIONAL INFORMATION:
System.Web.Services.Protocols.SoapException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. —> Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing.
As we can see in the error message, they are using http://localhost/reportserver to access reporting services. As a next step, asked them to open the URL using a web browser and they received the error:
The report server cannot open a connection to the report server database. The log on failed. (rsReportServerDatabaseLogonFailed)
The user name or password is incorrect. (Exception from HRESULT: 0x8007052E)
WORKAROUND/SOLUTION
Based on the error, it is clearly visible that SSRS was not able to connect to SQL Server. Here is the page in SSRS Configuration Manager to set the database connection.
I checked their database settings and tried connecting SQL Server using SSMS and it was working fine. So, I re-applied the database setting from the report server configuration manager and tested the report server URL. Strangely, that fixed the issue. Looks like there was some mismatch in username password somewhere.
What kind of error have you fixed as a DBA related to SSRS?
Reference: Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
Thanks Pinal
Hi. Once I saw a problem that after applying service pack Report server was updated partly and was not running, saying that version of server is out of date. Rebuilding its databases just didn’t slowed problem. Eventually I found out that records in dbo.Upgrade history doesn’t match records in dbo.DbUpgradeHistory. After fixing this, server started to work.
Instead of re-applied the database setting from the report server configuration manager, I had simply restarted the SQL Server Reporting Service. After that the issue has been fixed.
Thanks. This helped me now… In our case, it was the server had been restored from an image to a newer, faster machine and was using the same IP address, but the reporting services configuration specified the computer name in the connection string. Simply pointing it to the right DB fixed the issue.
Thanks, Please make sure the SQL server service is running on the report server
I am getting this or similar error but I am not using SSRS server. The report is running using ReportViewer in Local mode.