The report server can not open a connection to the report server database while opening reports in sql server database

Below is the error which I faced at one of my client while opening SQL server reports.

Error:

  • While opening reports from browser,faced the below error.

Error

Issue Reported:

  • The report server can not open a connection to the report server database.While opening reports from url.

Analysis:

  • The report server database or user database which was used for report server that was not present on sql server due to database rebuild operation had been performed by some DBA.
  • Below is the screen shot for the same while configuring database from Report server configuration manager.

Report doen not exist

  • Here,the master database was got corrupted.In this case we have two options either we can replace the master from its backup if taken any else rebuild it.
  • When we rebuild the system database,all user database and report server,report server tempdb database get detached from server also the new system database get generated.
  • You can get the more info as well as how to rebuild the system databases from below Microsoft link

http://blogs.msdn.com/b/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx

Solution:

  1. First attach the all user databases along with report server and report server tempdb database.
  2. Now open Report server configuration manager.
  3. Open database page in Report server configuration manager.
  4. Click on change database.

change database

5.Choose an exist report server database and click next.

choose an existing

6.Test the connection and click next.

test connection

7.

next

8.Now specify the credential of an existing account that the report server will use to connect to the report server database click next and finish.

specify credential

9.Now open the reports from url.You will get the below error because after rebuild the data source has been lost.

data source error

10.Now go to home folder of report server from url itself and create a new data source.Put the proper connection string and hit okay button.

new data source

11.Okay now open the reports and you will get your reports back.

final reports

  • This is how you can resolve when you face the issue described above.
  • Please do comments when you find this useful or any suggestion on the same will be highly accepted.