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)

About these ads
This entry was posted in Security, SQL Server. Bookmark the permalink.

3 Responses to Quick tip: WMI alerts and SQL Server Agent permissions.

  1. Pingback: (SFTW) SQL Server Links 16/08/13 • John Sansom

  2. Scott says:

    Unfortunately I’ve run into this too many times. I end up running wmidiag modified to run on Windows 2008 and/or running PROCMON to find the permission issue. It normally occurs when DBAs or server administrators harden the DB server.

  3. Pingback: BPOTW 2014-06-06 | SQL Notes From The Underground

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