none
ERROR 0x80004005 : DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER - Remote Execution of DTEXEC Causing Connection Failures.

    Question

  • Hello all.. 

     

    I have a simple DTSX package that takes a CSV file and loads it into a table in SQL Server 2008 Ent. x64 bit (on 2008 Ent. Server).  The package runs well when DTEXEC is executed locally on the server which it resides on.  I run into trouble when I try to execute the package using PSExec like so:

    C:\>c:\pstools\psexec \\myservr -u "mydom\myuser" -p "mypass" dtexec /FILE "X:\ETL\packages\test1.dtsx" /CHECKPOINTING OFF  /REPORTING EW /DECRYPT mykey

     

    The package encryption is set to "EncryptAllWithPassword", which I supply along with DTEXEC "/decrypt" attribute.  The error I am getting is "SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "MyDB" failed with error code 0x80004005. There may be error messages posted before this with more information on why the AcquireConnection method call failed."

     

    When the package is executed locally, the user connection properties are exactly the same (Windows Authenticated connection, using an administrative account).  The account specified in the SSIS log below (mydom\myuser - modified of course) is the proper user and is exactly the same user that is being used when the package is executed successfully (when dtexec is run locally). 

     

    I should also mention one last thing, and that is; if I do not call an OLEDB connection to SQL server, the package executes with no error even when called remotely using the PSExec call above.  I've loaded data from a CSV file to a local RAW file, and I can execute the package with no problems from both local and remote machines.

     

    If anyone can shed some light on this problem, please help as soon as you can.  I thank you all in advance for any and all advice! 

     

    Here is the entire log for the package: 

    ---------------------------------------------------------------------------------------------------------------------------------------------------------

    #Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message

    OnPreValidate,myservr,mydom\myuser,test1,{*****-8CAE-41B7-BB00-E4F1B7D04744},{*****-13BD-4BBA-BAF4-56B457348F90},4/5/2010 5:58:15 PM,4/5/2010 5:58:15 PM,0,0x,(null)

    OnPreValidate,myservr,mydom\myuser,simple,{*****-8BF3-487D-9072-FD81663E392E},{*****-13BD-4BBA-BAF4-56B457348F90},4/5/2010 5:58:16 PM,4/5/2010 5:58:16 PM,0,0x,(null)

    OnInformation,myservr,mydom\myuser,simple,{*****-8BF3-487D-9072-FD81663E392E},{*****-13BD-4BBA-BAF4-56B457348F90},4/5/2010 5:58:16 PM,4/5/2010 5:58:16 PM,1074016266,0x,Validation phase is beginning.

    OnProgress,myservr,mydom\myuser,simple,{*****-8BF3-487D-9072-FD81663E392E},{*****-13BD-4BBA-BAF4-56B457348F90},4/5/2010 5:58:16 PM,4/5/2010 5:58:16 PM,0,0x,Validating

    OnProgress,myservr,mydom\myuser,simple,{*****-8BF3-487D-9072-FD81663E392E},{*****-13BD-4BBA-BAF4-56B457348F90},4/5/2010 5:58:16 PM,4/5/2010 5:58:16 PM,50,0x,Validating

    OnError,myservr,mydom\myuser,simple,{*****-8BF3-487D-9072-FD81663E392E},{*****-13BD-4BBA-BAF4-56B457348F90},4/5/2010 5:58:16 PM,4/5/2010 5:58:16 PM,-1071611876,0x, SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "MyDB" failed with error code 0x80004005. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

    OnError,myservr,mydom\myuser,simple,{*****-8BF3-487D-9072-FD81663E392E},{*****-13BD-4BBA-BAF4-56B457348F90},4/5/2010 5:58:16 PM,4/5/2010 5:58:16 PM,-1073450985,0x,

    component "sql" (53) failed validation and returned error code 0xC020801C.

    OnProgress,myservr,mydom\myuser,simple,{*****-8BF3-487D-9072-FD81663E392E},{*****-13BD-4BBA-BAF4-56B457348F90},4/5/2010 5:58:16 PM,4/5/2010 5:58:16 PM,100,0x,Validating

    OnError,myservr,mydom\myuser,simple,{*****-8BF3-487D-9072-FD81663E392E},{*****-13BD-4BBA-BAF4-56B457348F90},4/5/2010 5:58:16 PM,4/5/2010 5:58:16 PM,-1073450996,0x,One or more component failed validation.

    OnError,myservr,mydom\myuser,simple,{*****-8BF3-487D-9072-FD81663E392E},{*****-13BD-4BBA-BAF4-56B457348F90},4/5/2010 5:58:16 PM,4/5/2010 5:58:16 PM,-1073594105,0x,There were errors during task validation.

    OnPostValidate,myservr,mydom\myuser,simple,{*****-8BF3-487D-9072-FD81663E392E},{*****-13BD-4BBA-BAF4-56B457348F90},4/5/2010 5:58:16 PM,4/5/2010 5:58:16 PM,0,0x,(null)

    OnPostValidate,myservr,mydom\myuser,test1,{*****-8CAE-41B7-BB00-E4F1B7D04744},{*****-13BD-4BBA-BAF4-56B457348F90},4/5/2010 5:58:16 PM,4/5/2010 5:58:16 PM,0,0x,(null)


     

    Tuesday, April 06, 2010 1:27 AM

Answers

All replies

  • In case my following assumption is incorrect, please supply the following:

    1. Version of SSIS you're using.

    2. Command line or other details of "how it works" when you're "starting it locally".

    3. The details of the connection manager that's failing remotely - driver, credential type, etc...

    Without knowing those, I can only guess it may be a 32/64 bit issue.  Please ensure that you're running the same bitness of DTExec when you're running locally as opposed to remotely.  Here are some details on bitness and SSIS.


    Todd McDermid's Blog
    Tuesday, April 06, 2010 2:29 AM
  • Todd, 

    Thank you for your help.  

     

    HERE'S THE LOCAL EXECUTION RESULTS:

    C:\>dtexec /FILE "X:\ETL\packages\test1.dtsx" /CHECKPOINTING OFF  /REPORTING EW /DECRYPT mykey

    Microsoft (R) SQL Server Execute Package Utility

    Version 10.0.1600.22 for 64-bit

    Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

     

    Started:  10:54:08 AM

    Warning: 2010-04-06 10:54:10.12

       Code: 0x80049304

       Source: simple SSIS.Pipeline

       Description: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance c

    ounters are not available.  To resolve, run this package as an administrator, or on the system's console.

    End Warning

    DTExec: The package execution returned DTSER_SUCCESS (0).

    Started:  10:54:08 AM

    Finished: 10:54:14 AM

    Elapsed:  5.413 seconds

     

     

     

    HERE'S THE REMOTE EXECUTION RESULT:

    C:\>c:\pstools\psexec \\MyServ -u "MyDom\MyUser" -p "mypa$$word" dtexec /FILE "X:\ETL\packages\test1.dtsx" /CHECKPOINTING OFF  /REPORTING EW /D

    ECRYPT mykey

     

    PsExec v1.97 - Execute processes remotely

    Copyright (C) 2001-2009 Mark Russinovich

    Sysinternals - www.sysinternals.com

     

     

    Microsoft (R) SQL Server Execute Package Utility

    Version 10.0.1600.22 for 64-bit

    Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

     

    Started:  10:58:16 AM

    Error: 2010-04-06 10:58:17.37

       Code: 0xC020801C

       Source: simple sql [53]

       Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAG

    ER.  The AcquireConnection method call to the connection manager "MyDB" fail

    ed with error code 0x80004005.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

    End Error

    Error: 2010-04-06 10:58:17.37

       Code: 0xC0047017

       Source: simple SSIS.Pipeline

       Description: component "sql" (53) failed validation and returned error code 0

    xC020801C.

    End Error

    Error: 2010-04-06 10:58:17.37

       Code: 0xC004700C

       Source: simple SSIS.Pipeline

       Description: One or more component failed validation.

    End Error

    Error: 2010-04-06 10:58:17.37

       Code: 0xC0024107

       Source: simple

       Description: There were errors during task validation.

    End Error

    DTExec: The package execution returned DTSER_FAILURE (1).

    Started:  10:58:16 AM

    Finished: 10:58:17 AM

    Elapsed:  0.452 seconds

    dtexec exited on MyServ with error code 1.

     

     

    Both servers have SSIS v2.0 (SQL 2008 Ent. 64 bit).  Both are on a Windows Server 2008 Ent 64 bit.  The connection that fails when executed remotely, is an OLEDB Connection to a separate SQL 2008 Ent. server.  Connection string: "Provider=SQLNCLI10.1;Data Source=1.1.1.1;Integrated Security=SSPI;Initial Catalog=MyDB".  It uses Windows Authentication for Login. 

     

    However, I just tested the connection using a sql login "SA", and same thing happens.  When executed locally the package works.  When executed remotely it fails with exactly the same error. 

     

     

    Thank you again for all your help.  

     

    - Eyal

    Tuesday, April 06, 2010 6:03 PM
  • Drive "X" is what?  A local drive, or a mapped network drive?
    Todd McDermid's Blog
    Tuesday, April 06, 2010 6:15 PM
  • Drive "X" is a physical drive on the host machine (where the package is being executed).  However, this can not be a problem considering that the same package will execute successfully if another type of component is used.. as in my first attempt, which takes a CSV file and loads it into a Raw File, also on drive "X". 

     

    I have two servers, A and B.  X drive is on server B.  Server A uses PsExec to call DTEXEC on Server B, and passing in all the attributes with it.  

    When the package takes a CSV file (from a completely different source) and loads it into a Raw File on server B, the package works when executed from either server B or A.  When it uses an OLEDB connection it only works when executed locally. 

     

    - Eyal

    Tuesday, April 06, 2010 6:53 PM
  • It might be an issue with permissions then.  Somehow PSExec isn't executing your package in the appropriate account.  That may also explain the fact that "SA" doesn't work - because unless you've change the default ProtectionLevel, it wouldn't load the sensitive password information if it were executed under the wrong account.

    Can you watch Task Manager on server B when the PSExec call is made?  See what user the DTExec is executing as?


    Todd McDermid's Blog
    Tuesday, April 06, 2010 8:50 PM
  • Okay.. I'm a bit at a loss here.. 

     

    So I have Task Mgr up and I'm looking at the processes.  When I run dtexec locally, I see it pop-in to the processes list (DtExec.exe) and execute under the specified user package works well.  However, when I run it remotely.. I don't even see Dtexec pop in at all..  I don't see anything pop-in.. and no other process seems effected by the remote execution.. 

     

    What I do see is, on the remote host (where I execute psexec), I see a process for "psexec.exe*32" work.   So first thing I made sure to re-check, since it seems like the targeted server (hosting the package) doesn't seem to execute the package what so ever (no dtexec.exe process running when remotely executed), is that the package still works..  So I disabled the OLEDB dataflow and just went back to loading a CSV file onto a Raw File.. still targeting the same SSIS package.. not a second package.. so the only thing that's different is the OLEDB load.  The package works when called remotely (using psexec). 

     

    So to recap.. when Server A uses PsExec to call DtExec on Server B,  the Task Mgr on Server B shows no activity and Server A shows "PsExec*32" running under proper user credentials.  When DtExec is called locally on Server B, the Task Mgr shows DtExec running with proper user credentials.  This behavior is identical between the two versions of working/non-working packages. 

     

    Lastly, I'm sure that PsExec is running as 32bit in Server A.. and maybe that's why I don't see the process come up in Server B..  But still that doesn't take out of the equation the fact that when OLEDB is not being used, the package works fine.  Also, we should consider that the SSIS log file (logging every event in the SSIS package) notes that the user executing the package is the same user in both instances (remote / local execution). 

     

     

    So now I'm completely lost!  :) 

     

     

     

     

    Wednesday, April 07, 2010 7:44 PM
  • You do have to make sure you have the "show all users processes" button/checkbox/whatever flipped on in TaskManager.  Have you done that?
    Todd McDermid's Blog
    Wednesday, April 07, 2010 7:55 PM
  • Ah.. good call! .. So on Server B (target server) I see DtExec.exe running under same user.. and PsExec (remote process) running under SYSTEM. 

     

     

    Wednesday, April 07, 2010 9:48 PM
  • OK - you've said the package works when you execute it "locally".  Does this mean that it works on your dev machine when you run it in BIDS?  Or does this mean it works when you RDP to the server and run it with DTExec?
    Todd McDermid's Blog
    Thursday, April 08, 2010 3:15 AM
  • Both servers are in the same network..  When I say "locally" I mean on the host machine of the ssis package.  When I say remotely, I mean Server A is calling Server B.  In both cases, the package is executed by DtExec, never the BIDS app. 

     

     

    Thursday, April 08, 2010 6:58 PM
  • Have you ever tried to RDP to server B and attempt to run the package via DTExec?
    Todd McDermid's Blog
    Thursday, April 08, 2010 8:14 PM
  • Maybe the terms "Remote" and "Local" are confusing..  

     

    1. I'm RDPing to one box.. and executing DTExec on a package with in the same box.. the package runs and successfully loads data using OLEDB to a separate SQL server. 

    2. I'm RDPing to another box (in the same network and domain as the one above) and executing PSExec, which is calling DTExec on the same package as in the previous step.  The package executes successfully, but fails to load data to the specified SQL destination via OLEDB.  The package log specifies the errors I encountered ('DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAG').  

     

    In both cases, the user executing the package is the same domain user with administrator rights.  The process runs under the specified user (on both boxes) as specified in above thread.  And if I take out the OLEDB task from the package and repeat step 2, the package will load data with no errors.  

     

    I hope that leaves out all ambiguity and helps to clarify the problem.  

    Monday, April 12, 2010 10:43 PM
  • I should also add that in the grand scheme of things, all I'm trying to do is distribute my SSIS packages (and their workload) to a number of machines, while having one central controller (server A) which calls and executes these packages on each of the distributed machines (Server B).  The process should run in the host machine (Server B), not in the controller machine.  That is - Server A should tell Server B what package to execute and what files / parameters to use during load, while Server B does all the workload.  If I had Server C, D and E, I would use Server A to call each of these boxes and specify which package to load as well.. 

     

    I'm trying to distribute load of an ETL system to grow linearly - distributing my load equally between a virtually limitless number of ETL servers. 

     

    - Eyal

    Monday, April 12, 2010 10:50 PM
  • Hmmm.  Seems like you've tried everything I can think of - and more.  (Thanks for the clarifications.)

    Perhaps PSExec isn't going to work for you here.  Perhaps you could define SQL Agent jobs on the "remote" servers, and launch them by calling sp_start_job on those servers... ?


    Todd McDermid's Blog
    Monday, April 12, 2010 10:59 PM
  • I thought of that.. But MSSQL Must be installed for SQL Agent to run, not so with SSIS....  If I'm going to have a scaling ETL system, distributing as many as 10, 20 or even 50 servers, I'd rather not install an instance of SQL on these machines and get hit with the licensing costs.. 

     

    I also want to pass in all variable information dynamically.. as in.. I'd like the controller (Server A) call the workload server (Server B) and say.. "Hey... go ahead and execute Package1.dtsx and load this file: [file path]".. this way.. I can have 100 files to load and 10 servers to load them with.. 

     

    So I'm still on my mission of finding a way to do this.. 

     

     

    Thank you very much for all your help anyways.  

     

    - Eyal

     

     

    Tuesday, April 13, 2010 12:31 AM
  • The other options are pretty well laid out here: Loading and Running a Remote Package Programmatically.
    Todd McDermid's Blog
    • Marked as answer by Zongqing Li Monday, April 19, 2010 2:32 AM
    Tuesday, April 13, 2010 1:10 AM
  • Hey did you get any solution. We are also getting the same error. Please reply if you got it.
    Vikas
    Thursday, June 17, 2010 2:44 AM
  • Eyal,

    It sounds like you have a classic Kerberos Double Hop issue.  http://support.microsoft.com/kb/319723  If you turn on profiler and watch the connection come in from your package you are going to see that it is most likely a service account from the middle box and not the account which kicked off the powershell process.  You will need to work with your Active Directory to get everyone trusting each other.  You can see it in the link that Todd provided:

    "Impersonating another account to run the package is often not a successful solution. Although the package starts under the impersonated account, the additional threads that are created by the package revert to the account that is used by the process that started the package. These threads include the threads used by the data flow to load and save data. Therefore, the process account itself needs permission to most of the external resources used by the package."

    Once you get the computers trusting one another it should authenticate with the PS user.

    Thanks,

    FindTim

    Thursday, June 17, 2010 3:01 AM
  • I found the problem.. it was chimney offload features of Win2k8 r2
    Thursday, July 15, 2010 12:53 AM
  • Hi Eyal-

     

      What did you end up setting the configuration for these features?  I am having the same trouble as you and I tried turning the chimney offload features both on and off (both on the remote and local PCs) and I still get the errors.

     

    Thanks,
    Phil

     

    Tuesday, July 27, 2010 9:06 PM
  • Eyal, Phil...

    I'm working on the same scenario as well, distributing my SSIS packages across multiple servers and have the same issue with PSEXEC. Can you post the resolution?

    Thanks,

    Paul

    Monday, October 04, 2010 4:08 PM