Microsoft KB Archive/302828

= FIX: DTS Fails to Transfer Rows When a Non-Sysadmin User Opens Packages from T-SQL with Xp_cmdshell =

Article ID: 302828

Article Last Modified on 10/29/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q302828



BUG #: 354674 (SHILOH_BUGS)

BUG #: 101842(SQLBUG_70)



SYMPTOMS
When a user who is not a member of the sysadmin role executes a Data Transformation Services (DTS) package by using the xp_cmdshell extended stored procedure and the Dtsrun utility, the package may fail to transfer data from the source to the destination. Even though no data transfers, the package execution may report a successful completion (such as in the following error message) and not display any errors:

Dtsrun: Loading...

Dtsrun: Executing...

Dtsrun: Package execution complete.

You may experience similar behavior when you run packages as scheduled jobs. The jobs may report success (although no data is transferred) or failure.



CAUSE
This problem occurs because:
 * When a non-sysadmin user executes the xp_cmdshell stored procedure and the Dtsrun utility, the command runs under the security context of the SQLAgentCmdExec account.

-or-
 * When a non-sysadmin user schedules a package, the non-sysadmin user uses the SQLAgentCmdExec account security context.

When SQL Server executes the DTS package, SQL Server creates and deletes the temporary files in the SQL Server TEMP folder. This is a local account, which does not have permissions for the TEMP folder, which therefore causes the behavior that is described in the &quot;Symptoms&quot; section of this article.



SQL Server 2000
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

SQL Server 7.0
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 7.0. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

301511 INF: How to Obtain the Latest SQL Server 7.0 Service Pack



WORKAROUND
To work around this problem, grant the SQL Agent proxy account read and write permissions to the SQL Server service startup account's TEMP folder.

SQL Server 2000

For Microsoft SQL Server 2000:
 * Give the Microsoft Windows NT account that you use for the SQL Agent proxy account read and write permissions to the SQL Server TEMP folder ( :\Documents and Settings\ \Local Settings\Temp), where  is the SQL Server startup account.

-or-
 * Set the SQL Server TEMP folder to a folder to which the SQL Agent proxy account has read and write permissions.

SQL Server 7.0

For SQL Server 7.0, give the SQLAgentCmdExec account read and write permissions to the C:\Temp folder.



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

SQL Server 2000

This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.

SQL Server 7.0

This problem was first corrected in Microsoft SQL Server 7.0 Service Pack 4.

Additional query words: dtsrun xp_cmdshell

Keywords: kbbug kbfix KB302828

-

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

© Microsoft Corporation. All rights reserved.