After moving the SCOM database, DW database and Reporting database to a SQL 2012 cluster I was no longer able to run reports. I would get a Cannot initialize report error as shown in the following screenshot.
More of the error:
Microsoft.Reporting.WinForms.ReportServerException: An error has occurred during report processing. (rsProcessingAborted) —> Microsoft.Reporting.WinForms.ReportServerException: Cannot create a connection to data source ‘DataWarehouseMain’. (rsErrorOpeningConnection) —> Microsoft.Reporting.WinForms.ReportServerException: Cannot open database "OperationsManagerDW" requested by the login. The login failed.
To fix this I did the following:
#1 Checked the user mappings on the ReportServer database per step 14 on this link:
http://technet.microsoft.com/en-us/library/hh268492.aspx
Also steps 8 through 11 can be used as a reference on this link as it has more details for working in SQL management studio to check permissions:
http://technet.microsoft.com/en-us/library/cc540368.aspx
#2 My permissions looked fine so the next thing I checked was the Data Warehouse Main data source connection string in SSRS. Sure enough this was set wrong. I pointed this to the SQL cluster and reports worked again. To check this use the following steps:
Go to http://SSRSSERVER/Reports
Click on Details View
The Data Warehouse Main data source is hidden by default so the details view is the only way to see it.
Click the arrow next to the Data Warehouse Main data source and select Manage.
Now you should be in the properties of the Data Warehouse Main data source. Here you can review the connection string settings and fix anything that is wrong. Click apply when done and reports should work.
