July 10, 2008 at 10:53 am
I am working on an Access application which must execute a DTS package. Since the workstation that will run this application does not have any SQL Server components installed, I cannot use the DTS objects in my VBA code. Instead, I am using ADO to execute a stored procedure containing the following code:
exec master.dbo.xp_cmdshell 'dtsrun -E -Scasvr\busdiv -N"Import_TO_MyDB"'
When run on a workstation where the user is a sysadmin, this code executes correctly, as does the entire Access application. To enable other users to execute the xp_cmdshell extended stored procedure, I established a sql agent proxy account, granted the intended user access to the master database and granted him EXEC permission on xp_cmdshell.
The intended new user is now listed as a User of the Master db with exec rights on xp_cmdshell. Nevertheless, when I test the application on his workstation, I get the following:
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: Unspecified error
Error source: Microsoft JET Database Engine
Help file:
Help context: 5000000
Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: Unspecified error
Error source: Microsoft JET Database Engine
Help file:
Help context: 5000000
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.
Evidently, xp_cmdshell is able to run and to load dtsrun.exe. The error occurs during the execution of dtsrun, and it is apparently related to the issue of user rights because it does not occur when run on my own workstation or with anybody else who has sysadmin rights. I experimented with changing the dts command line to 'dtsrun -s"casvr\busdiv" -n"Import_To_MyDB" -U"sa" -P"sapassword"' but this yields exactly the same error.
I would appreciate any advice on what needs to be done to enable use of this application by a non-sysadmin user.
July 11, 2008 at 10:09 am
Did you check whether or not the non-admin account has permission to do whatever in your DTS packages, such read/write on a folder?
July 11, 2008 at 10:18 am
That's a little strange. I'd think that a user would have problems with xp_cmdshell before DTSRun.
Does the proxy have access to msdb?
July 11, 2008 at 11:16 am
Thank you for replying.
The proxy is in the name of the head of my department. I do not see him explicitly listed as a user either for msdb nor for the master database.
Should he be? And if so, in what role?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply