Rights Issue Executing DTSRun in Stored Procedure

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:


SET NOCOUNT ON
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 did the following:

1. Established a sql agent proxy account by running the following code in
Query Analyzer:

use master
go

xp_sqlagent_proxy_account N'SET'
, N'<mydomain>'
, N'<ntuser>'
, N'<ntuser's password>'
go

-- retrieve the proxy account to check that it's correct.
xp_sqlagent_proxy_account N'GET'
go

2. Granted the intended user access to the master database

use master
sp_grantdbaccess 'NewUser'

3. Granted EXEC permission to the intended user to xp_cmdshell

grant exec on xp_cmdshell to NewUser

4. Unchecked the "Only users with SysAdmin privileges can execute CmdExec
and ActiveScripting job steps" checkbox on SQL Server Agent Properties.

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 would appreciate any advice on what needs to be done to enable use of this
application by a non-sysadmin user.
.

Relevant Pages

  • Re: Scheduled DTS package doesnt run
    ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... > I call the stored procedure by an Execute SQL Task object with all ... DTSRun: Loading... ...
    (microsoft.public.sqlserver.dts)
  • Re: About dtsrun
    ... DTSRUN is a command-line utility so you execute it from the command-prompt ... the SQL Server instead of the client, you can run DTSRUN from a SQL Agent ...
    (comp.databases.ms-sqlserver)
  • SQL output in DTS SQL Execute Task
    ... When you run a DTS package with a simple "Execute ... Where does that, or ANY output, from the SQL go??? ... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 ...
    (microsoft.public.sqlserver.server)
  • Re: Opinions on approach, please...
    ... Create a data layer program per table, ... number of bind variable tokens in your dynamic SQL. ... then simply EXECUTE that without passing it any parameters? ... Hard code WHERE clauses. ...
    (comp.lang.cobol)
  • Re: SQL Injection- Bypassing magic_quotes
    ... Because i was trying to execute: ... Don't terminate the query and you most ... Or try something like bobcat or one of the other SQL injection tools out ... Chief Information Security Officer ...
    (Pen-Test)
Loading