Remote Exec of DTS MS Access Data Pump Task Returns Dreaded 80004005

Posted on 2007-08-05
Last Modified: 2013-11-30
Trying to exec DTS task involving load of MS Access table to SQL Server 2000 table using Data Pump task via remote exec of xp_cmdshell command of dtsrun. That is, on client, connect to database server and exec user stored procedure containing command EXEC xp_cmdshell @cmd where @cmd has been set to 'dtsrun /S /E /N DTS Package Name.  Fails with:
DTSRun OnError:  DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
   Error string:  Unspecified error
   Error source:  Microsoft JET Database Engine
   Help file:  
   Help context:  5000000

Remote execution of DTS Task not involving MS Access connection seems to work fine.

Client is NOT in sysadmin group so set up xp_sqlagent_proxy_account (account for user that xp_cmdshell "runs as") with Windows user having necessary Windows and SQL Server priviliges to execute dtsrun and perform all work in DTS package run by dtsrun.  Gave client priviliges, of course, to run extended stored procedure xp_cmdshell.

When logged directly on SQL Server, xp_sqlagent_proxy_account setup can successfully execute DTS package from Enterprise Manager or from command line using dtsrun.  Again, however, same account cannot execute the Package when remotely invoked using xp_cmdshell.

MS Access Jet engines are fully up to date on client and server.

This seems like a permissions related problem, but I can't see the difference between running the DTS Package directly logged on as the xp_sqlagent_proxy_account and executing remotely.  And why does it only occur for MS Access related DTS tasks?
Question by:ogdenbt
    LVL 8

    Accepted Solution

    The problem is like you said a permmission issue. the account thats used to start sqlserver needs to have permission to the drive or folder that contains the access database. I am assuming that the access database is on a different server.


    Author Comment

    I am not the system or SQL Server admin so I am going to restate your solution to make sure I have it:

    The windows account used to start the SQL Server instance on which I am running DTS (found by right clicking the instance in Enterprise Manager and viewing the Security tab for the account at the bottom of the page) must have permissions to the drive on which the MS Access database read in the DTS task resides.  Is this correct?

    I will try this but it is confusing since the xp_sqlagent_proxy_account documentation suggests that the DTS task is run under its account which already has permissions to the drive.  That is the security context seems to be: xp_cmdshell executed under Windows client account; command performed by xp_cmdshell executed under Windows xp_sqlagent_proxy_account.  What am I missing in my understanding of xp_sqlagent_proxy_account?

    Author Comment

    I put the Windows account used to start the SQL Server instance into the local administrators Administrators Group and then tried the remote execution of the DTS Task that tries to read a MS Access database.  It still failed.  Should I do any kind of re-start or other setup before trying the remote execution again?  Any other suggestions  Here is what I run to test remote execution.  This results in the error stated in my first post.

    From client:
    Using Query Analyzer, login into remote server as client and run the exec_xp_cmdline_sp stored procedure on the server containing the DTS scripts.  Pass to the stored procedure the DOS command line set in @cmd variable.  The Tst2 DTS package contains only 1 task, a Data Pump Task load of a SQL Server table from a MS Access table.  The exec_xp_cmdline_sp stored procedure simply contains the command: EXEC xp_cmdshell @cmd

    DECLARE @RC int
    DECLARE @cmd varchar(512)
    -- Set parameter values
    SET @cmd = 'dtsrun /S colossus /E /N Tst2'
    EXEC @RC = [egbs_tst_1].[dbo].[exec_cmdline_sp] @cmd
    LVL 8

    Expert Comment

    try this from query analyzer

    xp_cmdshell 'dir \\servername\sharedfolder'

    sharedfolder is where the access database resides

    see if you get any results back - if not then that pc you connecting from should also have permissions to the server where access database resides.

    also use xp_cmdshell like this



    Author Comment

    I could not get the \\servername\sharedfolder version of the DIR command to work (it did not recognized the servername) but I did successfully run dir z:\egbs_scripts\load_risk\in.  This works since the xp_cmdshell DOS command runs on the server and the Z drive is local to the server.

    By the way I am running xp_cmdshell as master..xp_cmdshell.  I am also currently allowing the output to return to the calling process, that is how I see the DIR output and the DTS Package output.

    Here are other tests I have tried:
    Logged in as xp_sqlagent_proxy_account on server, ran DTS package using Enterprise Manager and dtsrun.exe - Both worked.
    Logged in as client account on client, ran DTS package NOT containing any connection to a MS Access database using stored procedure to run xp_cmdshell onf server with DOS command of dtsrun.  Worked.
    Ran accesschk.exe System Internals utility from MS to check access of xp_sqlagent_proxy_account to directory on server containing the MS Access database that I cannot connect to via DTS.  Access permissions all OK.
    Logged in as client acount on cllient ran stored procedure to run xp_cmdshell on server with DOS command of "whoami" to check Windows user on server.  Returned xp_sqlagent_proxy_account.  Also ran osql and select of SQL Server SYSTEM_USER.  Returned xp_sqlagent_proxy_account.  So apparently xp_cmdshell using xp_sqlagent_proxy_account to run on server.

    I am now thinking that it is some kind of incompatibility between the MS Access database created by the client on the server and the DTS Jet drivers that are attempting to connect to the MS Access database.  I will try to get more diagnostics on the attempt to connect via DTS script.


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now