Wednesday, 6 June 2012

The service manager data warehouse SQL reporting services is currently unavailable. You will be able to execute reports until this server is available. Please contact your system administrator. After the server becomes available please close your console and re-open to view reports SCSM 2012

Error: The service manager data warehouse SQL reporting services is currently unavailable. You will be able to execute reports until this server is available. Please contact your system administrator. After the server becomes available please close your console and re-open to view reports SCSM 2012
We encountered this error in our SCSM 2012 test environment. On further diagnosis figured out the SSRS box had some serious issues, after fixing one issue it was going in cycles other issue popped in. finally decided to reinstall SSRS but when you do that you lose SSRS configuration changes, encryption key.
I would suggest to take a backup of Report Server and Report Server Temp databases via management studio or scripts whatever you prefer and the encryption key from SSRS Configuration Manager before performing any steps
In our case the backup of encryption wasn’t available. Here is what we did to resolve the issue.
1)       Backup of Report Server and Report Server Temp Databases from SCSM 2012 Production environment
2)      Back up Encryption key from SSRS Configuration Manager Production Environment
3)      Restored the Report Server and Report Server Temp Databases as ReportServerNew and RepoertServerNewTemp DB’s on SCSM 2012 Test environment
4)      From SSRS Configuration manager, associated the new two databases
5)      Restored the encryption key from Production into Test environment via SSRS Configuration Manager
Note: This is the tricky part, when you restore the encryption key; it creates an entry of the Production and Test instance in your restored Report Server Database on Test Environment.
6)      To overcome this, Launch Management Studio, Connect to the Database Engine
Expand ReportServerNew Database, Navigate and Select KEYS Table
Expand KEYS table, edit and delete the entry mapped to Production Instance.
7)      Follow the steps from here to copy Microsoft.EnterpriseManagement.Reporting.Code.dll and edit Rssrvpolicy.config  to add segment code
8)      When that’s done, stop and Start the SSRS Service
Launch SCSM console you should see the reports there provided the ReportGroup has permissions on SSRS Report Manager URL.  In our case ReportGroup is an AD Group that contains the list of users who can run reports in SCSM 2012 Console.
This resolved the issue for me and happy jolly days.
Key thing is to have the backup of your report server databases and encryption , configuration file, without that it’s difficult to make this working.

1 comment:

  1. We encountered this error in our SCSM 2012 test environment. On further diagnosis figured out the SSRS box had some serious issues, after fixing one issue it was going in cycles other issue popped in. finally decided to reinstall SSRS but when you do that you lose SSRS configuration changes, encryption key. survival warehouse food

    ReplyDelete