Rahman Agoro, the frontline SQL DBA

Just another WordPress.com weblog

Setting up WMI query and getting error SQLServerAgent Error: WMI error: 0x80041003.

Posted by rahmanagoro on June 13, 2011


I was trying to setup database mirroring alerts on one of my production database servers and I looked into the article on the MS site, see http://technet.microsoft.com/en-us/library/cc966392.aspx on mirroring alerting best practices. I then proceeded in trying to setup same, I ran the script below.

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Mirroring_Alert',
  @enabled=1,
  @delay_between_responses=1800,
  @include_event_description_in=0,
  @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
  @wmi_query=N'SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 5 ',
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Mirroring_Alert', @operator_name=N'DBA_GROUP', @notification_method = 1
GO

 

I then got the error message below.


Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: WMI error: 0x80041003.
Msg 14511, Level 16, State 1, Procedure sp_verify_alert, Line 300
The @wmi_query could not be executed in the @wmi_namespace provided. Verify that an event class selected in the query exists in the namespace and that the query has the correct syntax.

 

I ran thesame code on my desktop and it was fine, but I was thinking what could be the problem here.

I checked everything to make sure that the syntax was right and there wasnt anything wrong, bumped across a few KB’s about thesame problem, but it was primarily being caused by server names being longer than 15 characters and this applied to SQL 2005, my SQL instance was SQL 2008 and I then thought what could be going wrong. After spending the hour battling this, and I have a rule that any problem taking longer than an hour to fix, I need to get my head cooled out, pop out for a 10 minute break and fresh air and then give it another go. It seem to work for me time and time again, I came back, thought !! Hang on a minute, lets check the SQL error log to see if there are any pointers to the root cause, on there I saw errors as follows.


Date  6/13/2011 11:45:33 AM
Log  SQL Server (Current - 6/13/2011 10:01:00 AM)

Source  Logon

Message
Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: The account is disabled. [CLIENT: <local machine>]


Ahh, this could be it. When I setup SQL servers I often disable the builtin/Administrators login as this is a security risk. It looks like the WMI service is using the local system account and this is obviously disabled and thus causing the problem highlighted.

I changed the WMI service account such that its a domain account and permissioned it on SQL server and pesto !! The problem is solved.

Advertisements

One Response to “Setting up WMI query and getting error SQLServerAgent Error: WMI error: 0x80041003.”

  1. Serge DBA said

    It worked for me as well!
    Sometimes you want to secure soo much your loosing features.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: