Well today I logged into a customer’s site to deal with a SCOM issue in there SCOM 2012 R2 environment. The SCOM environment itself appeared to be working, but the data warehouse wasn’t being populated. This occurred as they changed the service accounts from a Local Service to best practices of using domain accounts. The following events were constantly repeated through the Operations Manager event log (Applications and Services Log/Operations Manager)

Failed to store data in the Data Warehouse. The operation will be retried.Exception ‘SqlException’: Management Group with id ”ABCDEFAB-ABAB-1234-4567-ABCDEFABCDEF” is not allowed to access Data Warehouse under login ”DOMAIN\svc-ScomDW”One or more workflows were affected by this.

Event ID: 31557Provider Name: “Health Service Modules”Task Category Data WarehouseTime: 13:01Failed to obtain synchronization process state information from Data Warehouse database. The operation will be retried. Exception ‘SqlException’: Management Group with id ”ABCDEFAB-ABAB-1234-4567-ABCDEFABCDEF” is not allowed to access Data Warehouse under login ”DOMAIN\svc-ScomDW”One or more workflows were affected by this.

Event ID: 2115Provider Name: HealthServiceTask Category: NoneTime: 13:00A Bind Data Source in Management Group SCOM Management Group has posted items to the workflow, but has not received a response in 480 seconds. This indicates a performance or functional problem with the workflow.Workflow Id : Microsoft.SystemCenter.DataWarehouse.CollectPerformanceDataInstance : scom.domain.comInstance Id : {12312312-3434-4545-5656-123456789012}

So where did I start to fix this problem

To fix the issue, is quite simple, but in this blog post, I’ll show the way I found it. I solve a lot of issues using the techniques I describe below. Keep reading or jump to the solution section at the end.

Check the Event Log

There are errors that were constantly written to the Operations Manager log. So I thought, I’ll verify the changes that the customer did by following the instructions in the following links:

Re-Check the Event Log

As permissions all appeared to be correct, I keyed in on the following. In Event Id 31551 and 31569 errors (see above), it is complaining that the service account is not allowed to access Data Warehouse under login ”DOMAIN\svc-ScomDW” on the Management Group ”ABCDEFAB-ABAB-1234-4567-ABCDEFABCDEF”

I decided to make sure that the management group was correct. I used PowerShell on the SCOM Management Server to do this:
Import-Module OperationsManager
New-SCOMManagementGroupConnection
Get-SCOMManagementGroup | ft Name, Id, Isconnected -AutoSize
Name Id IsConnected
---- -- -----------
SCOM Management Group ABCDEFAB-ABAB-1234-4567-ABCDEFABCDEF True

Again things are looking correct. The Management GUID in the error log is the same as the Management Guid on the server.

SQL Server Verification

Ok, let’s put on our SQL Server hat and see what’s going on. From there I wanted to use SQL Server Profiler and see what was happening under the service account used for Operations Manager “Data Warehouse Report Deployment Account”.

Opened SQL Server Management Studio

Connected into the Database Engine of the SQL Server which hosted the Data Warehouse Database (OperationsManagerDW)

Clicked Tools, SQL Server Profiler

Again, Connected into the Database Engine that hosted the OperationsManagerDW database

On the trace properties window, I clicked the Events Selection tab

Clicked the column Filters button

Click NTUserName

Clicked the (+) on the + Like and entered the Data Warehouse Report Deployment Account service account name “%svc-ScomDW%” (Without the quotes)

Click OK

Click Run

While the profile was running, I clicked Edit, then Find. For the find criteria, I searched for the Management Group GUID: “ABCDEFAB-ABAB-1234-4567-ABCDEFABCDEF”. Instance ID should work too.

At this point I was content pretty content, I have now profiled the SQL Server for the account that was being denied access to do its work. Now I what I like to do in these cases is to see the raw error message that is passed back from SQL Server.

Open a Command Prompt

Type: runas /user:DOMAIN\ svc-ScomDW cmd

You’ll be prompted to type the password after you press enter

Now you have a command prompt running as the service account, let’s connect into SQL Server.

Type: osql.exe –E

Paste the SQL Statement that you found in SQL Server Profiler and press enter

Now that we’re armed with the raw SQL Server error message, we can dig under the covers a little more. In the error message above, there is a (Stored) Procedure (SP) called ManagementGroupAccessCheck and the error is on line 37 of this SP. Great this is looking positive.

Opened SQL Server Management Studio as yourself

Connected into the Database Engine of the SQL Server which hosted the Data Warehouse Database (OperationsManagerDW)

We need to cleanup this created script so that the line numbers match up a lot closer or exactly the same to when SQL Server executes the script. Remove the first 11 lines and have the CREATE PROECDURE statement on line 1.

Scroll down to line 37

Notice on line 36 it throws error 777970008. This matches our error number! Perfect! I’m glad this was a simple stored procedure.

How did we get into this part of the code where we through the error. Look up a little ways to Line 29.

Shouldn’t my service account login name match what was inside the variable @WriterLoginName?

Looking up to Line 17, you can see that @WriterLoginName is pulled from the vManagementGroup table.

Easy, let’s check the value of what is in that table. I then ran a new SQL Statement to see what was inside the database

Related

About The Author

Allan's IT career has moved him to several Canadian provinces and even to Sweden for four years. He has been nominated for the Cloud and Datacenter Management Microsoft MVP. Due to his Veeam experience in backup, restore, and DR scenarios, Veeam has accepted Allan into the Veeam Vanguard program.
Allan has always been a senior member and key player in implementing Windows infrastructure projects, virtualized platforms (Hyper-V and VMWare) and cloud projects. His love is building roadmaps and lifecycle of the Windows Server environments, DR, automation and the development of ITIL processes for OSD, configurations, and performance.
He is an expert at scripting solutions and has an uncanny ability to reduce complexity and maximize the functionality of PowerShell. Allan has recently rejoined the TriCon Elite Consulting team again as a Principal Consultant.
Allan can be found on twitter @AllanRafuse and blogs on http://www.checkyourlogs.net

About

Checkyourlogs is a community blogging platform that focuses on the most current Microsoft and surrounding technologies. All of our bloggers are real-world experts, Microsoft MVPs, Cisco Champions, Veeam Vanguards and more. All views expressed on this site are independent.