Sometimes you may get this error when trying to launch your report server webpage:
The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable)
While the error is descriptive enough to let you know that Reporting Services could not find the report server database, it may be a challenge to some who have no idea where this database is and what is it used for. To your surprise, there is more than one database involved.
Reporting Services requires two databases to run:
- ReportServer
- ReportServerTempDB
In short, the Report Server database is needed to store report definitions, report history and snapshots, report models, shared data sources, resources, scheduling and delivery information, metadata and several other objects. ReportServerTempDB is where Reporting Services stores session and execution data, cached reports, and work tables that are generated by the report server. For more information about Reporting Services database visit: http://msdn.microsoft.com/en-us/library/ms156016.aspx.
To solve the error mentioned above you need the following information:
- What is the SQL Server Reporting Services instance you are trying to reach?
- Where are the SQL Server Reporting Services databases hosted?
Assuming you have the right permissions, you will need to run the Reporting Services Configuration Manager and point to the Reporting Services instance as seen on the image below. You would normally find Reporting Services Configuration Manager under Start->Programs->Microsoft SQL Server 2008 R2->Configuration Tools
Once you connect to your Reporting Services instance through Reporting Services Configuration Manager, go to the Database tab located on the left pane to view the name of the SQL Server instance where the ReportServer database is being hosted and the name of the ReportServer database as seen in the image below.
the Database Name and SQL Server Name is the main point from where the system will take the connection for reports deployment.
Keep in mind that in some environments the ReportServer database may be hosted in a separate SQL Server database instance and the ReportServer database may have been renamed to other than the default “ReportServer” database name. You will usually find this type of configuration in a scale-out architecture. For more information on scale-out architecture visit: http://sqlcat.com/technicalnotes/archive/2008/06/05/reporting-services-scale-out-architecture.aspx.
Once you located the SQL Server database instance where the ReportServer and ReportServerTempDB database are hosted you need to follow this checklist:
- Is the SQL Server database instance running? Is the SQL Server service started?
- Can you verify connectivity to the SQL Server database instance?
- Are the ReportServer and ReportServerTempDB databases attached and online?
- Does the Reporting Services service account have read/write access to the ReportServer and ReportServerTempDB databases?
Once all of this is verified to be true and issues have been fixed, than the last step would be to restart the Reporting Services instance. If everything is right you should be able to access your reports. It is highly recommended to frequently backup both the ReportServer and ReportServerTempDB databases. If you lose you ReportServer database you may re-deploy reports, data sources and datasets, but you will lose all subscriptions, schedules and report parts that users may have created.
Also, keep in mind that the ReportServerTempDB database does not behave like the SQL Server TempDB. Per MSDN Books on Line:
“Reporting Services does not re-create the temporary database if it is missing, nor does it repair missing or modified tables. Although the temporary database does not contain persistent data, you should back up a copy of the database anyway so that you can avoid having to re-create it as part of a failure recovery operation.” For more details visit:http://msdn.microsoft.com/en-us/library/ms156016.aspx
