SCOM 2012 R2 – Data Warehouse Service Account is not allowed to access Data Warehouse database

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)

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:

1. How to Change the Credentials for the Action Account

   https://technet.microsoft.com/en-ca/library/hh456432.aspx?f=255&MSPPError=-2147217396

2. Account Information for Operations Manager

   https://technet.microsoft.com/en-ca/library/hh457003.aspx?f=255&MSPPError=-2147217396

Everything looked correct, so onto the next setp.

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”.

1. Opened SQL Server Management Studio
2. Connected into the Database Engine of the SQL Server which hosted the Data Warehouse Database (OperationsManagerDW)
3. Clicked Tools, SQL Server Profiler
4. Again, Connected into the Database Engine that hosted the OperationsManagerDW database
5. On the trace properties window, I clicked the Events Selection tab
6. Clicked the column Filters button
7. Click NTUserName
8. Clicked the (+) on the + Like and entered the Data Warehouse Report Deployment Account service account name “%svc-ScomDW%” (Without the quotes)
9. Click OK
10. Click Run
11. 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.
12. The entry that it first found was (Copy it into your clipboard):

    exec ManagedEntityRowIdResolve @ManagementGroupGuid=’ABCDEFAB-ABAB-1234-4567-ABCDEFABCDEF’,@ObjectKeyXml=N'<Keys><Key><ManagementGroupGuid>ABCDEFAB-ABAB-1234-4567-ABCDEFABCDEF</ManagementGroupGuid><ManagedEntityGuid>1f84c939-21b4-dc24-11b9-dc4deb24883e</ManagedEntityGuid></Key></Keys>’

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.

1. Open a Command Prompt
2. Type: runas /user:DOMAIN\ svc-ScomDW cmd
   1. You’ll be prompted to type the password after you press enter
3. Now you have a command prompt running as the service account, let’s connect into SQL Server.
4. Type: osql.exe –E
5. Paste the SQL Statement that you found in SQL Server Profiler and press enter
6. Type GO and press enter
7. The raw SQL Server Error message that I got was:

   Msg 777970008, Level 16, State 1, Server SCOM, Procedure ManagementGroupAccessCheck, Line 37 Management Group with id ”ABCDEFAB-ABAB-1234-4567-ABCDEFABCDEF” is not allowed to access Data Warehouse under login ”DOMAIN\svc-ScomDW”

1. We’re done with this window for now, but keep it open!

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.

1. Opened SQL Server Management Studio as yourself
2. Connected into the Database Engine of the SQL Server which hosted the Data Warehouse Database (OperationsManagerDW)
3. Expand the server, expand Databases, Expand OperationsManagerDW, Expand Programmability, Expand Stored Procedures

   

4. Scroll down and find dbo.ManagementgroupAccessCheck
5. Right click it and choose Script Stored Procedure as, Create To, New Query Editor Window

   

6. 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.

   

7. Scroll down to line 37

   

8. Notice on line 36 it throws error 777970008. This matches our error number! Perfect! I’m glad this was a simple stored procedure.
9. How did we get into this part of the code where we through the error. Look up a little ways to Line 29.
10. Shouldn’t my service account login name match what was inside the variable @WriterLoginName?
11. Looking up to Line 17, you can see that @WriterLoginName is pulled from the vManagementGroup table.
12. 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

    SELECT
    *
    FROM
    vManagementGroup
    WHERE
    ManagementGroupGuid=‘ABCDEFAB-ABAB-1234-4567-ABCDEFABCDEF’
    

     

13. This only returned one row as there is no connected management groups. Scrolling over to the WriterLoginName, I noticed it was their old service account name!

Solution

Well now that we’ve found yet another spot where we need to update SCOM when we change service account names, let’s do it.

1. Opened SQL Server Management Studio as yourself
2. Connect into the Database Engine of the SQL Server which hosted the Data Warehouse Database (OperationsManagerDW)
3. Run the following SQL Statement

   UPDATE OperationsManagerDW.dbo.vManagementGroup SET WriterLoginName=‘DOMAIN\svc-ScomDW’ WHERE ManagementGroupRowId=1
   

4. Verify that it took the update

   SELECT *FROM vManagementGroup WHERE ManagementGroupRowId =1

5. Return to the command prompt window that is connected to OSQL.exe as DOMAIN\ svc-ScomDW
6. Press the UP arrow and rerun the command. It should work!

That’s the end of this blog post. I hope that it helps someone out or at least gives them some troubleshooting ideas on how to solve other problems.

As always, please leave positive/negative comments so that I know I’m writing helpful and relevant information. Until next time, happy teching!