SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS and MSDTC on a SQL Authenticated Connection


SSIS and MSDTC on a SQL Authenticated Connection

Author
Message
Simon-220850
Simon-220850
Right there with Babe
Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)

Group: General Forum Members
Points: 775 Visits: 217
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.
Simon-220850
Simon-220850
Right there with Babe
Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)

Group: General Forum Members
Points: 775 Visits: 217
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 BigGrin
anurags99
anurags99
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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
Simon-220850
Simon-220850
Right there with Babe
Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)

Group: General Forum Members
Points: 775 Visits: 217
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
Joseph Fallon
Joseph Fallon
SSC-Addicted
SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)

Group: General Forum Members
Points: 438 Visits: 849
Having this problem too - any updates ?
Ravi Kumar V
Ravi Kumar V
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 324
Hi Simon,

Thank you very much. This solved my problem!
Simon-220850
Simon-220850
Right there with Babe
Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)

Group: General Forum Members
Points: 775 Visits: 217
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 ;-)
Joseph Fallon
Joseph Fallon
SSC-Addicted
SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)

Group: General Forum Members
Points: 438 Visits: 849
IIRC so did I ;-)
richard_murphy
richard_murphy
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 180
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
kellieharrisson
kellieharrisson
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search