Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Ctrl-alt-geek

Matt Bowler is a DBA at Trade Me, and teaches database design and administration courses at the local institute of technology. A recent but avid discoverer of SQL Server, Matt is a regular contributor to forums at MSDN, Experts Exchange and SSC and he blogs at mattsql.wordpress.com.

Quick tip: WMI alerts and SQL Server Agent permissions.

I came across an issue today while trying to create a WMI alert for a mirroring state change. The error was something along the lines of WMI Access denied. Error: 0×80041003.

My first port of call was to verify the WMI query by using the WBEMTest utility.

WBEMTest

This is a great little utility for testing out WMI queries.

In this case the WMI namespace is: root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER 

(MSSQLSERVER is for a default instance, replace this with the instance name for a named instance. )

and the WMI query is looking for one of the database mirroring state changes:

SELECT * from DATABASE_MIRRORING_STATE_CHANGE WHERE State = 5 AND DatabaseName = ‘Mirrored database’

(State change 5 is Connection with Principal Lost. The full list of possible state changes is here)

My first clue as to the issue that I was facing came when I was unable to connect to the SQL Server namespace unless I ran WBEMTest as an administrator.

It turned out that the SQL Server Agent service account did not have sufficient permissions to query the WMI – once the account was granted explicit permissions on the namespace the alerts were created successfully. (My suspicion is that UAC was at play somehow here and if anyone can shed light on this I would be interested to hear it)

To manage the WMI permissions run the WMI control – wmimgmt.msc

wmicontrol

Click on more actions and select properties. In the WMI Control (local) Properties dialog select the security tab and navigate to the namespace.

wmisecurity

Click the security button and add the SQL Agent service account. Grant the account Execute Methods, Enable Account and Read Security permissions. (WMI permissions are listed here)


Comments

Leave a comment on the original post [mattsql.wordpress.com, opens in a new window]

Loading comments...