Post reply

Failed to acquire connection when using transactions - MSDTC

  • February 7, 2012 at 8:22 am

    #264052

    Hello,

    I'm at my wits' end trying to get a package to work with transactions, so I'm hoping someone here can help.

    I have a package which makes updates on two different servers, and I want to have a transaction around these updates.

    I have the package working perfectly without any transactions, but as soon as I set the TransactionOption to Required it stops working, with the following error:

    "Failed to acquire connection "xxx". Connection may not be configured correctly or you may not have the right permissions on this connection."

    In order to simplify things I am now testing using a package with just a single connection, and one execute SQL task, which simply does "select @@version". It still succeeds without transactions and fails with them.

    I have spent some time with Google, and done the following:

    Configured MSDTC both on my machine which is running the SSIS package and on the SQL server with the following security settings:

    Network DTC Access

    Allow Remote Clients

    Allow Inbound

    Allow Outbound

    Mutual Authentication Required

    Rebooted my machine and the SSIS package and made sure MSDTC service is running.

    Used DTCPing, to test DTC - it is all working fine (I had to add RestrictRemoteClients = 0 to my machine's registry and reboot).

    Made sure the windows firewall is turned off at both ends (although this made no difference to DTCPing, I thought I'd give it a try).

    I have also tried it with both MSDTC security setting No Authentication Required instead of Manual Authentication Required and it makes no difference.

    My machine has Windows 7 on it and the server has Windows 2008 R2 and SQL 2008 R2 on it. The second server has Windows 2008 and SQL 2008 on it, and I can't even get the DTCPing to work on that, however if I can't make the first server work then there's no point putting any effort into the second.

    I'm on the verge of giving up and adding lots of Execute SQL tasks with begin tran, commit tran and rollback in them!

    Any help would be gratefully received.

    Thanks,

    Rachel.

  • February 9, 2012 at 2:16 am

    #1444936

    I guess nobody knows the answer then - I have now officially given up!

    I am instead going to use Execute SQL tasks with begin tran/commit tran and rollback in an error handler.

    Thanks for all who took a look anyway,

    Rachel.

  • February 9, 2012 at 3:32 am

    #1444966

    Rachel Byford (2/9/2012)

    I guess nobody knows the answer then - I have now officially given up!

    I am instead going to use Execute SQL tasks with begin tran/commit tran and rollback in an error handler.

    Thanks for all who took a look anyway,

    Rachel.

    I have found that this option always works best, instead of relying on the MSDTC.

    Don't forget to put the RetainSameConnection property on the connection manager to true.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 9, 2012 at 3:38 am

    #1444970

    Yes, thanks.

    I have it all working nicely now - not worth the trauma of MSDTC.

    The only thing which could go wrong would be if the commit at one server succeeded, but then the commit at the other failed.

    Hopefully that's pretty unlikely though.

    Rachel.

  • September 13, 2017 at 3:37 am

    #1959220

    Hi,

    You must enable network access for the MSDTC.

    On Windows:

    1. Click Start, click Run, and type dcomcnfg to launch the Component Services Management console.
    2. Click to expand Component Services and click to expand Computers.
    3. Click to expand My Computer, click to expand Distributed Transaction Coordinator, right-click Local DTC, and click Properties.
    4. Click the Security tab of the Local DTC Properties dialog

    In that dialog box, I had to enable "Network DTC Access" and also "Allow Inbound" and "Allow Outbound".

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply