Recently, I had to troubleshoot a problem with SQL backups via Microsoft Data Protection Manager 2012 SP1 for a SQL Server 2008 system. DPM was alerting us that database auto-protection failed with error code ID 32511. The detailed errors showed that DPM could not enumerate SQL Server instances using Windows Management Instrumentation on the protected computer. This error was detailed in the DPMRACurr.errlog on the production server:

WARNING Failed: Hr: = [0x8007007e] : unable to execute the WQL query: SELECT * FROM ServerSettings

This pointed to a problem with the underlying WMI configuration for SQL, so I used wbemtest.exe from the remote DPM server to test WMI connectivity. If you are unsure of exactly what WMI namespaces are in use or what queries are being run, you can use WMI Tracing to see what’s happening under the hood.

Log Name: Microsoft-Windows-WMI-Activity/Trace
Source: Microsoft-Windows-WMI-Activity
Date: 10/22/2013 3:59:39 PM
Event ID: 1
Task Category: None
Level: Information
Keywords:
User: SYSTEM
Computer: SERVERNAME
Description:
GroupOperationId = 9283379; OperationId = 9300341; Operation = Start IWbemServices::ExecQuery – SELECT * FROM ServerSettings; ClientMachine = DPMSERVER; User = jeff; ClientProcessId = 2540; NamespaceName = \\.\root\Microsoft\SqlServer\ComputerManagement10

Once wbemtest is open, connect to the appropriate namespace:

SQL 2005
\\SERVERNAME\root\Microsoft\SqlServer\ComputerManagement

SQL 2008 & 2008 R2
\\SERVERNAME\root\Microsoft\SqlServer\ComputerManagement10

SQL 2012
\\SERVERNAME\root\Microsoft\SqlServer\ComputerManagement11

Once connected, try executing the WQL query that your application is using – in my case, it was SELECT * FROM ServerSettings. Doing this resulted in the error:

Number: 0x8007007e
Facility: Win32
Description: The specified module could not be found.

Some quick research shows this can most often be resolved by recompiling the WMI template for SQL with mofcomp:

http://support.microsoft.com/kb/956013

On 64-bit Windows with SQL 2008, the command is:

mofcomp “C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof”

You may need to adjust the command for bitness and version of SQL and then restart the WMI service for the changes to take effect. However, this did not resolve the issue on the specific system where I was encountering the problem. The same error was returned when trying to run a query in wbemtest after recompiling and restarting the service, the DPM console also displayed the same error when attempting to enumerate SQL instances. The 0x8007007e error typically means a DLL or registration is missing. Time to break out procmon and see what’s happening under the covers. Using filters to include only the wmiprvse.exe process and excluding entries with a SUCCESS result, I could see that there was a file it seemed to be looking for, but could not find:

It seemed to be scouring the path looking for sqlmgmprovider.dll and svrenumapi100.dll. I checked on disk, and sure enough, neither of those files existed under the path C:\Program Files\Microsoft SQL Server\100\Shared, however, their 32-bit counterparts were located under C:\Program Files\Microsoft SQL Server\100\Shared. Checking another  64-bit SQL 2008 server, I was able to find those files under that first path. After copying them from a known working system, the error was resolved. Also, the second file was only listed in procmon once I copied the first to the server and retested, so it make take several passes to completely resolve.

Note that this resolved this specific error for me, though it may not be the best solution. The reason those files were not on the server is because there was only a 32-bit instance of SQL Server on the system. By adding those two files and re-running wbemtest, an error was no longer returned, but the query also did not show any instances of SQL Server because it was querying for 64-bit instances.