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:
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 "Symptoms" section of this article.
RESOLUTION
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 (
Drive letter:\Documents and Settings\User1\Local Settings\Temp), whereUser1is 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
