Tuesday, December 15, 2009

Repost: EventID 31552

15-12-2009 Update:
A visitor of this blog had a good comment on duplicate accounts, found in OpsMgr. He is totally right so I will repeat his comment here: ‘I would be careful deleting "Duplicate" accounts. If you look at your Run-As Profiles, and then look at the properties for "Data Warehouse SQL Server Authentication Account" and "Reporting SDK SQL Server Authentication Account", under the Run As Profiles you'll see these "Duplicate" Accounts have a path of each Management Server (the RMS and any additional Management Servers you may have).

An OpsMgr environment had an issue with the Data Warehouse. This EventID was frequently logged:
 image

This is serious. When an event like this is logged only once it is not a real problem. Somewhere along the line of storing data in the DW a hiccup occurred and that’s it. But when this event keeps on coming back, it really needs attention.

Whatever I checked all seemed to be OK. The SQL server wasn’t busy at all so it had all the resources available to do it’s job, the permissions weren’t changed. The network was OK, no DNS issues either. Nor was there any other process (like a backup) locking this database. Also the related OpsMgr MP was OK (Data Warehouse Internal Library, version 6.1.7221.0).

Than something came to my mind. I had experienced a similar issue before at a customers site. OK, it was long ago (in the days SP1 had to come out), but what the heck. It solved my issue then so why not now?

So I opened the OpsMgr Console, went to Administration> Run As Configuration > Accounts. In the Accounts pane there are – among other account types – also Simple Authentication Accounts defined. Two of them are directly related to the Data Warehouse: Data Warehouse SQL Server Authentication Account and Reporting SDK SQL Server Authentication Account.
image

By default these accounts are set in such a manner that the account AND password are nothing but a single space. So is the case in this environment as well. But just to be sure, I ‘reset’ both accounts, using the same account and password (a single space):
image

and
image

Applied the changes and checked the OpsMgr eventlog. Even though the EventID’s 31552 were still appearing, the frequency was far less. So I was on the right track. Time for a second step.

Even though the OpsMgr databases are maintained automatically, it doesn’t hurt to run every now an then a Stored Procedure to update the statistics: sp_updatestats. I ran it against the Data Warehouse database (OperationsManagerDW) and the output showed that many tables were updated.

When checking the OpsMgr eventlog, EventID 31552 didn’t occur anymore. The next day, I double checked and nope, no more errors like those (nor others as well :) ).

7 comments:

Richard said...

I would be careful deleting "Duplicate" accounts.

If you look at your Run-As Profiles, and then look at the properties for "Data Warehouse SQL Server Authentication Account" and "Reporting SDK SQL Server Authentication Account", under the Run As Profiles you'll see these "Duplicate" Accounts have a path of each Management Server (the RMS and any additional Management Servers you may have).

Marnix Wolf said...

Hi Richard.

Thanks for visiting my blog AND your comment. You are totally right there! I will adjust my posting based on that information.

Thanks again.

Best regards,
Marnix

Previa_Owner said...

Hi Marnix,
I am running SCOM 2007 R2 and I am seeing 31552 getting logged every 10 mins and when I look under Administration - Run-As Configuration - Accounts pane I see three Data Warehouse SQL Authentication Account and three Reporting SDK SQL Server Authentication Account. Is this normal why do I have 3 accounts each? Is this because I have SQL 2000/2005/2008 MPs installed and each MP relies on one account? If not why do I have 3 of each? If I were to reset these account should I reset them all?

Thanks - Murad

Marnix Wolf said...

Hi Murad.

Thanks for visiting my blog. All these accounts are not related to the SQL MPs present in your Management Group.

Do not remove any of these accounts. What you could do however is to reset every single one of them.

Let me know whether this helped you.

Cheers,
Marnix

Marnix Wolf said...

Hi Murad.

Check out this webpage. Perhaps it helps you as well? http://www.eggheadcafe.com/software/aspnet/30551401/event-31552--31551.aspx

Cheers,
Marnix

Lava said...

Marnix Wolf
You Are The MAN!!!

Thank you

Catalina Orrego said...

En este documento indican que no se deben cambiar las cuentas ,

Data Warehouse SQL Server Authentication Account" and "Reporting SDK SQL Server Authentication Account

http://technet.microsoft.com/en-us/library/bb735408.aspx

que pasa si se hace el cambio indicado?