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.
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.
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)
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)