Microsoft KB Archive/295021

= BUG: WMI MSSQL_Table.ExportData Always Uses Standard Security =

Article ID: 295021

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



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 = &quot;[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'.&quot; 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.



Steps to Reproduce Behavior
 Install the SQL Server 2000 WMI add-on from the installation CD. Change the sa password to TEST. Make sure that the target computer that is running SQL Server has the Northwind database. Make sure that the Microsoft Windows NT account who is currently logged in has access to the target computer that is running SQL Server.  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 = &quot;MyServer&quot; strSQLServerName = &quot;MyServer\MyInstance&quot; strDataFilePath = &quot;d:\exportoutput.dat&quot; ' ---

Set SWbemLocator = CreateObject(&quot;WbemScripting.SWbemLocator&quot;)

set objWbemSvc = SWbemLocator.ConnectServer(strServerName,&quot;root/MicrosoftSQLServer&quot;)

'ImpersonationLevel = 3 is &quot;IMPERSONATE&quot; objWbemSvc.Security_.ImpersonationLevel = 3

Set objBcpSetting = objWbemSvc.Get(&quot;MSSQL_BulkCopySetting&quot;).SpawnInstance_

objBcpSetting.SQLServerName = strSQLServerName objBcpSetting.DataFileType = 3 objBcpSetting.DataFilePath = strDataFilePath objBcpSetting.ColumnDelimiter = &quot;|&quot; objBcpSetting.RowDelimiter = vbNewLine

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

set objWbemSet = objWbemSvc.ExecQuery(strWQL)

for each objTable in objWbemSet wscript.echo &quot;Found&quot; + objTable.Name objTable.ExportData objBcpSetting, objOutVal if objOutVal.ReturnValue <> 0 then wscript.echo objOutVal.Description wscript.echo objOutVal.ReturnValue else wscript.echo &quot;Success&quot; end if next </li> 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

</li></ol>

Additional query words: logon

Keywords: kbbug kbpending KB295021

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.