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: 0x80041003.
My first port of call was to verify the WMI query by using the WBEMTest utility.
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
Click on more actions and select properties. In the WMI Control (local) Properties dialog select the security tab and navigate to the namespace.
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)