[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 924
  • Last Modified:

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

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?
0
ogdenbt
Asked:
ogdenbt
  • 3
  • 2
1 Solution
 
JulianvaCommented:
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.

0
 
ogdenbtAuthor Commented:
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?
0
 
ogdenbtAuthor Commented:
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
SELECT @RC
0
 
JulianvaCommented:
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

master..xp_cmdshell


0
 
ogdenbtAuthor Commented:
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.

0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now