Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS and MSDTC on a SQL Authenticated Connection Expand / Collapse
Author
Message
Posted Tuesday, February 19, 2008 9:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 11:33 AM
Points: 170, Visits: 169
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.
Post #457466
Posted Wednesday, February 20, 2008 2:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 11:33 AM
Points: 170, Visits: 169
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 :D
Post #457880
Posted Tuesday, May 20, 2008 2:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 9, 2010 3:04 PM
Points: 1, Visits: 9
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
Post #504064
Posted Wednesday, May 21, 2008 4:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 11:33 AM
Points: 170, Visits: 169
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
Post #504348
Posted Wednesday, September 24, 2008 8:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:24 AM
Points: 162, Visits: 738
Having this problem too - any updates ?
Post #575275
Posted Tuesday, May 19, 2009 10:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:47 AM
Points: 9, Visits: 288
Hi Simon,

Thank you very much. This solved my problem!
Post #720287
Posted Wednesday, May 20, 2009 3:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 11:33 AM
Points: 170, Visits: 169
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
Post #720395
Posted Wednesday, May 20, 2009 4:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:24 AM
Points: 162, Visits: 738
IIRC so did I
Post #720420
Posted Friday, September 23, 2011 8:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 11:47 AM
Points: 4, Visits: 133
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
Post #1180168
Posted Wednesday, May 29, 2013 11:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 23, 2013 1:44 PM
Points: 1, Visits: 72
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
Post #1457882
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse