BUG: WMI SQL Provider Does Not Work When Remotely Requesting Data from a Clustered SQL Server (323283)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q323283
BUG #: 357126 (SHILOH_BUGS)

SYMPTOMS

Windows Management Instrumentation (WMI) scripts that try to access the "root\MicrosoftSQLServer" namespace experience problems (no results are returned) when run against a clustered SQL Server from a remote client computer (including the inactive node of the cluster).

If the same scripts are run locally on the cluster, they return results without any problems.

Running the same scripts on a non-clustered SQL Server (both locally and remotely) also return the results.

For more information, see the "More Information" section of this article.

WORKAROUND

The WMI Provider for SQL Server uses SQL-DMO behind the scenes to retrieve the data from SQL Server.

Instead of relying on the WMI Provider to get this data, the workarounds are to:
  • Use the SQL-DMO object model directly to retrieve the relevant data.

    -or-

    Make a direct connection to SQL Server (by using ODBC or OLEDB), and then run a stored procedure to retrieve the data.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

The following example illustrates the problem: 
Filename: repro.vbs

Dim username, password, servername
Dim objLocator, objServices
Dim colInstances, objInstance

'******Change the values here to reflect the ClusterName, User and Pwd*******
servername = "VirtualServerName"    
username = "UserName"
password = "Password"

'******************************Part A: Connect to root\MicrosoftSQLServer namespace***************
wscript.echo "*****Getting Values from root\MicrosoftSQLServer namespace*****" 
Set objServices = objLocator.ConnectServer(servername, "root\MicrosoftSQLServer", username, password)

objServices.Security_.ImpersonationLevel = 3

Set colInstances = objServices.InstancesOf("MSSQL_SQLServer")
i = 0
For Each objInstance In colInstances
	i = i + 1
	strServer = strServer & objInstance.Name &  vbCRLF
	strClus = strClus & objInstance.Clustered &  vbCRLF
	strPackage = strPackage & objInstance.Package & vbCRLF
	strTrueLogin = strTrueLogin & objInstance.TrueLogin & vbCRLF
	strTruename = strTrueName & objInstance.TrueName & vbCRLF
	strUserProfile = strUserProfile & objInstance.UserProfile & vbCRLF

Next
wscript.echo "SQLServer Name is: " & strServer
wscript.echo "IS Clustered: "  & strClus
wscript.echo "Package: "  & strPackage
wscript.echo "TrueLogin: "  & strTrueLogin
wscript.echo "TrueName/NetworkName: "  & strTrueName
wscript.echo "UserProfile: "  & strUserProfile



'************************Part B: Connect to MSSQL_ConfigValue class*****************
wscript.echo "*****Getting Values from MSSQL_ConfigValue Class*****" 
Set colInstances = objServices.InstancesOf("MSSQL_ConfigValue")
i = 0
For Each objInstance In colInstances
	i = i + 1
	strTemp = strTemp & objInstance.SettingID & ": " & objInstance.CurrentValue &  vbCRLF
Next

wscript.echo "The number of SQL Configuration Values found is: " & i & vbCRLF & vbCRLF & strTemp

Set objServices = Nothing
Set objLocator = Nothing
You will observer the following behavior when you run the script:
  • If you run the Repro.vbs script locally, on a clustered computer (cscript repro.vbs), both Part A and B return results.
  • If you run the Repro.vbs script locally on a non-clustered computer (cscript repro.vbs), both Part A and B return results.
  • If you run the Repro.vbs script remotely, and then connect to a non-clustered computer (cscript repro.vbs), both Part A and B return results.
  • If you run the Repro.vbs script from a client computer (cscript repro.vbs), and you then connect to a clustered server, Part A only returns the SQLServerName. Everything else is blank and Part B is totally blank.
With the preceding four (4) bullet items, it is clear that the script only fails when it is run from a client computer and it requests information from a clustered SQL Server.
Modification Type:MajorLast Reviewed:9/25/2003
Keywords:kbbug kbpending KB323283
©2003 Microsoft Corporation. All rights reserved.