February 19, 2008 at 9:16 am
Hi I have a package that has a container with a required transaction, meaning that the package starts an MSDTC transaction.
I have been running this across two servers on the same domain - Server A and Server B using windows (SSPI) authentication on the connections. MSDTC is running on both machines configured to allow inboud transactions and everything runs Hunky dory.
However if i change one of the connections to be a server not on our domain requiring SQL Authentication (Server C) , I receive "[Execute SQL Task] Error: Failed to acquire connection "CMS". Connection may not be configured correctly or you may not have the right permissions on this connection.. Server C is running MSDTC version 3.00.00.3535 (Windows 2000) which doesn't appear allow you to make an specific security configuration changes.
To make things even more curious if I connect to server C in SSMS and write a piece of SQL that specificaly begins a distributed transaction, it manages to enlist DTC on Server C and i can see the the transaction beginning and committed in component services.
Can anyone shed any light on this. I can only assume that SSIS is talking to MSDTC through another layer which it doesn't have rights to run.
February 20, 2008 at 2:31 am
Well, a bit more information. I have found a server with SQL Server 2000, windows 2000 and MSDTC 3.00.00.3535. I have tried running a package enlisting DTC on this server connecting via an OLEDB connection this time using Windows Authentication....and I still have the issue
So this appears to be an issue with SSIS and MSDTC 3.00.00.3535. Unfortunately a google on this Gave me a Googlewhack on my on post on this forum 😀
May 20, 2008 at 2:43 pm
I am having similar issues where when I run the package with connection string pointing to remote instance (instead of my localhost) I get same error on the task which has transaction option as "supported". You have mentioned that it was workin for you from Server A to Server B in same domain, can you please let me know MSDTC security settings that worked for you, thanks
May 21, 2008 at 4:53 am
Hi anurags99
Let me know if I'm teaching you to suck eggs at any time.
To enlist MSDTC on your target machine you will need to set some options in MSDTC security configuration. (To get to this screen, open component services in Administrative Tools, expand component services and expand computersn then rightclick My computer, select the MSDTC Tab and click the Security configuration Button)
1. Check the "Network DTC Access" Checkbox in the MSDTC Security configuration dialog. This will enable you to set the following options
2. Under Client and Administrattion, check the "Allow remote Clients" checkbox
3. Under Transaction Manager communication, check the "Allow Inbound" checkbox
4. If your two servers are on the same domain, then you should be able to use Mutual Authentication Required. If this still isn't working try chaging the checkbox to No Authentication
To Enlist MSDTC on your source machine, you will need to set the following MSDTC security configurations
1. Check the "Network DTC Access" Checkbox in the MSDTC Security configuration dialog. This will enable you to set the following options
3. Under Transaction Manager communication, check the "Allow Outbound" checkbox
4. If your two servers are on the same domain, then you should be able to use Mutual Authentication Required. If this still isn't working try chaging the checkbox to No Authentication
Additionally you will obviously need to make sure that the account that is running the SSIS package on the source server A has privileges to connect to target server B and that it has sufficient privileges for each of the tables, stored procedures etc used in the package
Cheers
September 24, 2008 at 8:45 am
Having this problem too - any updates ?
May 19, 2009 at 10:30 pm
Hi Simon,
Thank you very much. This solved my problem!
May 20, 2009 at 3:06 am
No problem Ravikumar,
Joseph, I'm afraid I never solved this problem, I adopted a completely different approach to my solution that didn't involve distributed transactions. I have also moved jobs since then to s site where all the servers are windows 2003 with the same edition of MSDTC so I don't think I'll encounter it anymore. Moving jobs is a bit of a radical solution though 😉
May 20, 2009 at 4:08 am
IIRC so did I 😉
September 23, 2011 at 8:42 am
This problem is resolved by opening Port 135 on the firewall between Server A and Server B. Servers in the same network segment should be ok e.g. 10.10.10.xxx
May 29, 2013 at 11:31 am
I had the same error and used a combination of the previous suggestions to resolve this.
On the source machine (my local machine in this case) I had to allow DTC through my firewall.
I then had to configure the DTC settings (dcomcnfg.exe) and select ‘Network DTC Access’ and ‘Allow Outbound’
On the target server I had to go into the DTC settings and select ‘Network DTC Access’ and ‘Allow Inbound’.
I checked the firewall on my target server and it was already allowing DTC through.
Thanks for sharing your knowledge!
Kellie
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply