I've been wrestling with a Microsoft Distributed Transaction Coordinator (MS DTC) issue today and thought it was worth noting the workaround on here. The error occurred whilst running an SSIS package but this post isn't really related to SSIS - its a wider issue.
I was trying to enlist SQL Server -running on a remote machine- in a transaction running on my local development machine. I was getting errors:
-Failed to acquire connection "<connection-manager-name>". Connection may not be configured correctly or you may not have the right permissions on this connection.
-Aborting the current distributed transaction.
After some hair-tearing and a bit of googling I came across this Microsoft knowledge base article: http://support.microsoft.com/kb/839279 which suggested Windows Firewall was the problem. It suggested opening up port 135 for msdtc.exe in order that it can communicate with other machines. When I tried to do this I got told that port 135 was already open for another application. It doesn't seem quite right to me that you can only open a port for one application but I'm not a security expert so I'm not going to moan about that too much. So, instead of messing about configuring Windows Firewall, I decided to just turn the whole thing off. This solved all the problems I was having.
My colleague Paul Mcmillan has alot of experience of using DTC and has a good post on the subject here: http://blogs.conchango.com/paulmcmillan/archive/2005/10/17/2277.aspx. One pearl of wisdom from Paul: "With DTC issues always start investigations using DTCPing and DTCTester"
UPDATE 14th November 2005:
The client that I am working for doesn't allow us administrative priveliges from our standard dev logon so they provide us a different account in order to carry out administrative functions (e.g. Turning off Windows Firewall). If you have the same problem then you can save yourself logging out in order to do this by executing:
- runas /u:"<user-account>" "control.exe firewall.cpl"