Creating a Data Source for the Operational DB on OpsMgr 2012 R2 Reporting Server

Creating a data source to for the OpsMgr Operational DB is a very common practice. This is required for many 3rd party OpsMgr reports. Kevin Holman blogged the instruction here.

In my case, I’m creating a data source called OperationalDataBaseMain for my favourite report MP SCC Health Check Management Pack. Other than the data source name difference between Kevin’s blog and what’s required for SCC Health Check MP, Kevin configured the data source with the option “Credentials are not required”, which is essentially using the SSRS service account (DW Reader account). In the SCC Health Check MP documentation, it suggests to use the option “Windows integrated security”. Back then when I configured this data source in OpsMgr 2007, both options worked.

Today I was trying to configure this data source on our newly built OpsMgr 2012 R2 test management group where the Operational DB is hosted by SQL server, Data Warehouse and Reporting Server is hosted on SQL server B. Both SQL servers are running SQL 2012 SP1. I tried both authentication methods, none of them worked straightaway.

“Windows integrated security” option

When I chose this option, the test connection was successful, but when I tried to run a report that’s targeting the operational DB, I got this error (and my user ID is a member of the OpsMgr admin group):

“Credentials are not required” option

When I chose this option, the Test Connection was unsuccessful and I got this error:

Login failed. the login is from an untrusted domain and cannot be used with Windows authentication.

Both SQL servers and the DW reader service account is located in the same domain.

Furthermore, an event was logged on the Operational DB SQL server’s security log:

This log entry indicates the DW reader account does not have “logon from network” (logon type 3) rights.

Then I found out “Access this computer from the network” rights is restricted by a GPO to the following groups and the DW reader account is not a member of any of them:

So I added the DW reader service account to the local “Users” group on the SQL server hosting operational DB, tried to establish the connection again in the data source, this time, I got another error:

Login failed for user <DW Reader service account>.

It’s different message, so I checked SQL security, the DW Reader account was not listed as a user in SQL. I checked the SQL server for the OpsMgr 2007 environment, this account is added in SQL, been given public role and it’s not mapped to the operational DB. So I replicated this configuration on the SQL server hosting OpsMgr 2012 R2 operational DB, tested connection again in SSRS, now I have another different error:

Cannot open database “OperationsManager” requested by the login. the login failed. Login failed for user <DW Reader service account>.

Also, I got a similar message in SQL log:

Login failed for user ‘<DW Reader account>’. Reason: Failed to open the explicitly specified database ‘OperationsManager’. [CLIENT: <SSRS Server’s IP address>]

So I modified the user mapping for the DW reader account, given it “public” role for the OperationsManager DB

After this modification, I was able to connect the data source:

However, after I tried to run a report targeting the operational DB, I got another error:

I then modified the permission for DW Reader account within the Operational DB again. this time I’ve given the “db_datareader” role:

Finally, after this, the report successfully ran:

Summary

To summarise, In my environment, the DW Reader account needs to be given the following rights on the remote SQL server hosting operational DB to be able to setup the data source in SCOM reporting server’s SSRS instance:

• Logon from the network rights
• the "public" role for the SQL DB Engine
• "public" and "db_datareader" role for the OpsMgr operational DB.

Note:

Although I have checked 2 identical OpsMgr 2012 R2 test management groups we have here and DW reader account does not have any SQL rights on the operational DB SQL server by default in both management groups, I cannot verify / confirm if this scenario is by design. In my home lab, because both operational and DW databases are on the same SQL, I did not have to modify permissions for DW reader account at all.

I did not consult with any Microsoft people. This is purely just my personal experience. If you have any doubts, please do not implement this in your environment.