Remote Exec of DTS MS Access Data Pump Task Returns Dreaded 80004005
Posted on 2007-08-05
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 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?