Posts by Tao Yang:
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:
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.
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.
This is the 2nd part of 2-part blog series. Part 1 can be found HERE. In Part 1, I went through the issues I had with deploying OpsMgr 2012 R2 agent via ConfigMgr 2007. In this article, I will go through the steps I took to deploy OpsMgr 2012 R2 agent using ConfigMgr 2012 Application […]
By reading the title of this article, you may think, this practice is so common, is it worth blogging? Before I started this task, I thought it should be a quick one that I can knock off in 30 minutes. I had to say, I was wrong, I ended up spent few days on it. […]
I just came back to work this week after a 4-week holiday in China. Today I have upgraded work’s OpsMgr 2012 SP1 DEV management group to R2. I firstly upgraded all 3 management servers, they all went smoothly without problems. but when I tried to run the upgrade for the Report Server and Web Console […]
To Check if WinRM has been enabled on a Remote machine: To Check the Default HTTP listener port on a remote machine: To Check the Default HTTPS listener port on a remote machine:
OpsMgr 2012 Sp1 Update Rollup 3 has been released for about a month now. Today I had some time after dinner so I thought it’s time for me to get my lab environment updated. In the past, I’ve been updating the OpsMgr server roles manually and using ConfigMgr Software Update (SUP) to apply the agent […]
Today I had to create a report in ConfigMgr to list total number of packages that have been assigned to each Distribution Point. The SQL query is rather simple, a one-liner. Here’s the query: This query works on both ConfigMgr 2007 and 2012. ConfigMgr 2007 Report: ConfigMgr 2012 Report: