Tag Archives: MimboloveSCOM Reporting
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.
Today I needed to change some parameters in a linked performance report in one of the OpsMgr 2007 R2 management groups at work. When I opened the report property, the Data Aggregation field somehow became blank and greyed out:
As the result, when I tried to run the report, I get this error:
I found a blog article from Dieter Wijckmans “SCOM 2007: Scheduled reports failing”, it indicates it’s because there are duplicate management group ID specified in the report parameters. Dieter’s fix doesn’t really apply to me as the my report is not a scheduled report, however, my approach is much easier.
below is what I’ve done:
1. log on to the RMS box, and run below PowerShell script to get the management group ID:
$RMS = $env:computername [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.EnterpriseManagement.OperationsManager.Common") | Out-Null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.EnterpriseManagement.OperationsManager") | Out-Null $MGConnSetting = New-Object Microsoft.EnterpriseManagement.ManagementGroupConnectionSettings($RMS) $MG = New-Object Microsoft.EnterpriseManagement.ManagementGroup($MGConnSetting) $MG.Id
If RMS is clustered, replace “$env:computername” to the RMS cluster name in the first line.
2. export the management pack (assuming the linked report is stored in a unsealed MP), open the unsealed MP in a text editor
3. Go to
<LinkedReport ID= (where ID is the Id of the problematic report)
Find the ManagementGroupId parameter and delete the incorrect value
4. Save the XML and import the unsealed MP back to the management group.
After importing the MP back, the “Data Aggregation” field is populated:
I can also change the report parameter in SSRS web site:
However, by directly modifying report in SSRS, the fix is only temporary. the original MP is not fixed and it will over write the report definition in SSRS. I’ve tried to update SSRS directly, the report got changed back shortly after.
Previously I’ve blogged my experience in migrating from OpsMgr 2012 RTM to SP1 which involved moving all databases to a new database server running SQL 2012. Since then, I’ve noticed the instruction in moving Operational DB published in TechNet is actually missing a step, which I’ve blogged here.
Over the last few days, since it was the long weekend down here in Victoria, I spent some time setting up the Talking Heads web application to explorer APM in OpsMgr 2012. I was following the instructions in Chapter 9 in the Mastering System Center 2012 Operations Manager book. The instructions in this chapter is exactly the same (almost word to word) as the 3-part series in Kevin Greene’s blog (Part 1, Part 2, Part 3). – Looks like we know who’s the author for this chapter. – Thanks Kevin
Anyways, I’ve learnt a lot from this chapter as I’ve never dealt with Avicode in 2007 or APM in 2012. While I was exploring the App Diagnostic and App Advisor consoles, I’ve noticed my App Advisor console won’t run any reports! No matter which reports I tried to run, I always get this error:
and as suggested, I got this warning event (EventID 1309) from Windows Application log:
In the exception stack, it shows I cannot connect to the SQL server:
Exception message: An error has occurred during report processing. —> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. —> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source ‘AppMonitoringSource’. —> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
After spending time trying to fix this issue, I created a DNS Alias record with my old OpsMgr SQL server name pointing to the new SQL server, and it fixed the problem! By doing this, I knew there is one place that I haven’t updated the SQL server name yet. I went through the Technet instructions for moving Operational DB, DW DB and Reporting Server again, I couldn’t find which step was I missing.
Today, I had a second look in the SSRS instance for OpsMgr reporting, I noticed there is an additional data source called AppMonitoringSource under Application Monitoring –>.Net Monitoring. the connection string for this data source was still pointing to the old SQL server:
According to the Technet guide “How to Move the Data Warehouse Database”, as per step 8, only the Data Warehouse Main data source needs to be updated (and of course, if we’ve manually created another data source for operational DB, which is very common and lots of community report MPs requires this datasource, we’d also update it too.). The AppMonitoringSource was not mentioned in the instructions – which I found interesting because step 10 actually covered changing the DB server name in dbo. MT_Microsoft$SystemCenter$DataWarehouse$AppMonitoring, table, we can tell by name is used for APM.
As result, after I’ve changed the connection string in this data source, deleted the DNS alias and flushed DNS cache on my web console server, the problem is now fixed.
So, not sure if the AppMonitoringSource was overlooked by the author of the Technet guide. For anyone who has moved the DW DB in OpsMgr 2012, it’s probably worth checking this data source (by going to http://<reporting server>/reports). I only found out now only because I’ve started using APM in my environment.
Yesterday, I noticed I get this error when I tried to run some reports in the System Center Central Health Check Reports MP in my home SCOM 2007 R2 environment:
“Value of 01-Jan-01 12:00:00 AM” is not valid for ‘value’. ‘Value’ should be between ‘MinDate’ and ‘MaxDate’.
I then realised I get exactly the same error on any reports which contain datetime pickers in the report.
Long story short, after spending some time troubleshooting this issue, including updating my SCOM 2007 R2 environment from CU5 to CU6 as this blog post has suggested:
SCOM 2007 R2 – Reporting : Value of ’1/01/0001 12:00:00 AM’ is not valid for ‘Value’. ‘Value’ should be between ‘MinDate’ and ‘MaxDate’
CU6 update didn’t fix my issue. The issue end up being the regional setting on my SCOM all-in-one box (RMS, databases and reporting server).
I live in Australia, so normally, I would set the language format to English (Australia). However, when I create a new management pack using SCOM 2007 R2 Authoring Console on a computer set to use English (Australia) language format, by default, 2 language packs are created in the management pack: ENU (English United States) and ENA (English Australia) and default language pack is set to ENA. I don’t always remember to change the default and delete ENA language pack for every management pack I’m working on. So,to work around this issue, on the 2 machines that I run Authoring Console from (one being the all-in-one SCOM server), I set the language format to English (United States) and modify the short date format from default M/d/yyyy to dd-MMMM-yy so I’m not confused with the month and day when I read a date. this is the cause of the error in SCOM reports.
Default English (United States) formats:
After I changed the format back to default on my SCOM server, the reports started running! Since all the SCOM server roles are on the same box, I can’t confirm if which components (i.e. RMS, SSRS etc.) relies on the default language formats.
I ran into an issue today. I have updated a report in a management pack. After I updated the version number, sealed it and imported the updated management pack into SCOM, the report that I have modified did not get updated in SQL Reporting Service (SRS).
Generally, once a new MP is imported into a management group, within few minutes, the reports within the MP should be deployed to SRS. This was the case when I updated the very same MP in Development environment, but in Production, I waited few hours and nothing has happened.
After few hours, I finally fix the issue.
For any reports that have been deployed as part of a MP, there should be a .mp file in the SRS folder, like this one:
In the production environment, the .mp file name from my management pack folder in SRS is different than the one in development environment. I checked other management packs in both Prod and Dev and they all have the same .mp file name.
To fix the issue: I deleted the .mp file from SRS, restarted the SRS service. Within one minute, the updated report got deployed to SCOM SQL Reporting Service and the .mp file got recreated as well.