Configuring SQL Reporting Services for Remote or different SCOM DW Database

This article mentions how to configure the SQL Reporting Services for generating reports from a remote SCOM Data Warehouse database (DW DB). This will also be of help for reconfiguring the Reporting Service when there is a change in the SCOM DW DB, Port or Server Name.

In a simple/test single server SCOM installation environment, there is no need to configure these settings since all the databases and reporting services are within the same server itself. However, in production environments where the Reporting Services and the Data Warehouse Database will be in separate environments, we will need to configure the reporting services to make use of the SCOM’s Data Ware house database. This database may be residing on a remote SQL Failover Cluster or part of an AlwaysOn Availability Group.

The below configuration is done on an environment where the Reporting services is installed on one of the SCOM Management Servers and the Data Warehouse DB resides on a remote SQL AlwaysOn Availability Group.

Steps:

Access the SQL Server Reporting Services Web Page and click on Details View.

Now, click on the Data Warehouse Main and select ‘Manage‘ from Dropdown menu.

Under the Data Source Type, Select “Microsoft SQL Server”

Under the connection String, Provide the connection string details in the following format:

data source=<DBINSTANCE or Listener Details>;initial catalog=<DW DB Name>;Integrated Security=SSPI

e.g.:

data source=SCOMListener01,14533;initial catalog=OperationsManagerDW;Integrated Security=SSPI

(where SCOMListener01 is the SQL AlwaysOn Listener listening on port 14533 and OperationsManagerDW is the Data Warehouse Database part of the Availability Group)

Select “Windows Integrated Security” under “Connect Using” options:

Click on “Test Connection” to make sure that the connection is successful as shown in the below image.

Click on Apply.

This will make sure that the Reporting Services is connected correctly to DW Database.

Also, when the SCOM Database or server is changed or the listening ports are changed due to any security or maintenance reasons the above steps can be performed by providing the new details in the Connection String.