BUG: WMI MSSQL_Table.ExportData Always Uses Standard Security (295021)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q295021
BUG #: 353378 (SHILOH_BUGS)
BUG #: 353377 (SHILOH_BUGS)

SYMPTOMS

If you call the MSSQL_TABLE:ExportData SQL Server Windows Management Instrumentation (WMI) Provider method by using Microsoft Windows NT Authentication Security, the call fails with the following error message:
Description = "[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'." Return Value = -2147203048
Note that the WMI Provider for SQL Server uses Windows NT Security, by default, when connecting to the database server.

CAUSE

The way in which WMI invokes the call to Distributed Management Objects (DMO) to perform the bulk-copy operation. This problem does not manifest if the data export is performed from an application that is coded to use DMO directly, even if it executes the same calls that WMI uses. This is because of the stateless nature of WMI and how it must reset the state of DMO with every call.

WORKAROUND

To work around this problem either:
  • Use DMO directly for the bulk-copy operation, which requires DMO to be installed. -or-

  • Use Standard Security for the WMI bulk-copy operation. Using Standard Security maintains the same connection criteria for both table enumeration and bulk-copy operations.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 2000.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Install the SQL Server 2000 WMI add-on from the installation CD.
  2. Change the sa password to TEST.
  3. Make sure that the target computer that is running SQL Server has the Northwind database.
  4. Make sure that the Microsoft Windows NT account who is currently logged in has access to the target computer that is running SQL Server.
  5. Create a Microsoft Vbscript (.vbs) script by using the code that follows.

    NOTE: Make sure that you change the target server references.
    dim strServerName, strLoginName, strPassword, strDataFilePath

' ---------------------------
' change the following values as necessary
strServerName = "MyServer"
strSQLServerName = "MyServer\MyInstance"
strDataFilePath = "d:\exportoutput.dat"
' ---------------------------

Set SWbemLocator = CreateObject("WbemScripting.SWbemLocator")

set objWbemSvc = SWbemLocator.ConnectServer(strServerName,"root/MicrosoftSQLServer")

'ImpersonationLevel = 3 is "IMPERSONATE"
objWbemSvc.Security_.ImpersonationLevel = 3

Set objBcpSetting = objWbemSvc.Get("MSSQL_BulkCopySetting").SpawnInstance_()

objBcpSetting.SQLServerName = strSQLServerName
objBcpSetting.DataFileType = 3
objBcpSetting.DataFilePath = strDataFilePath
objBcpSetting.ColumnDelimiter = "|"
objBcpSetting.RowDelimiter = vbNewLine

'Make sure that the target SQL Server 2000 server has the Northwind 'database.
strWQL = "select Name from MSSQL_Table where DatabaseName='Northwind' and Systemobject = False and Name='[dbo].[Customers]'"

set objWbemSet = objWbemSvc.ExecQuery(strWQL)

for each objTable in objWbemSet
	wscript.echo "Found" + objTable.Name
	objTable.ExportData objBcpSetting, objOutVal
	
	if objOutVal.ReturnValue <> 0 then
		wscript.echo objOutVal.Description
		wscript.echo objOutVal.ReturnValue
	else
		wscript.echo "Success"	
	end if
next
  6. Execute the VBScript by using WSCRIPT. The following text displays:
    Microsoft (R) Windows Script Host Version 5.1 for Windows Copyright (C) Microsoft Corporation 1996-1999. All rights reserved. Found[dbo].[Customers] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'. -2147203048
Modification Type:MajorLast Reviewed:10/3/2003
Keywords:kbBug kbpending KB295021
©2003 Microsoft Corporation. All rights reserved.