MSDTC security change + reboot 1

[LFCfan]

Hi folks,

We're trying to run an SSIS package which deletes data from a table on one server and loads it with data from another, within a transaction.

We were getting an error message "The partner transaction manager has disabled its support for remote/network transactions", so I changed the MSDTC security settings to allow network transactions. The MSDTC stopped and restarted, but still no joy and I'm getting the same error.

I found a post somewhere where the author says you have to reboot the server for the MSDTC changes to come into effect.
Rebooting these two servers will cause a lot of hassle, does anyone have any thoughts on anything else I could check before resorting to that?

Windows Server 2003 on both machines, and SQL 2005 with @@version of 9.00.1399.06

Many thanks

[sub]~LFCfan[/sub]

 

[mrdenny]

No you do not need to reboot for MS DTC changes to make effect (for the most part).

Have you gone into the Control Pannel and installed the DTC Network Components on EVERY machine involved in the transaction.

You then need to enable network support in the component services applet in Start > Programs > Administrative Tools on all machines involved in the transaction. Then restart DTC on all machines.

A good way to test DTC is to setup a linked server from one machine to another. After you get the linked server to work normally run the following.

begin distributed transaction
select *
from [i]otherserver[/i].master.dbo.sysfiles
commit

If that runs fine then DTC is working correctly. If it fails more trouble shooting is needed.

Is there a firewall between the machines or are then on the same network?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[LFCfan]

Denny,

Thanks for your help. Unfortunately I tried pretty much everything you posted, and the servers were rebooted this morning, with unsusprisingly no effect.
Having done some further reading it must be a security/firewall issue. Neither of the machines have Windows firewall running, but my organisation does have another firewall scenario which none of my team understands how it works (apparently it is referred to as a firewall, but isn't a firewall in the usual sense of the word - whatever that means!)

What do you mean by installing the DTC network components? I checked that the DTC service was running on all machines, and altered the security configuration in Component Services, have I missed out a step?

I didn't try the linked server option, because I found a handy little command line tool called dtctester that MS provide here:

http://support.microsoft.com/kb/293799.

I tried the linked servers today and the error I got was the good old 7391 error :
"Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "XXXX" for linked server "XXXXXXXX" was unable to begin a distributed transaction."

Looks like I'm going to have to compose an email to the security guys to "open up the, er, DTC port? Or something?" - and I need to come up with better wording than that, which could be tricky as it's not an area I'm familiar with!

Thanks again

[sub]~LFCfan[/sub]

 

[mrdenny]

DTC uses RPC for communication so basically all ports over 1024 will need to be opened bidirectionally in addition to 1433, and 135-139.

Here is the MSKB for setting up DTC for network access in Windows 2003

http://support.microsoft.com/default.aspx?scid=kb;en-us;817064.

If they don't want to open up all the ports then you can restrict the ports that DTC will use to communicate (by restricting the ports that RCP can use.

I can't find the MSKB on doing so, but here are the instructions.

Start > Programs > Admin Tools > Component Services
Drill down to Console Root > Component Services > Computers > My Computer
Right click on My Computer and click properties.
Default Protocols Tab
Select TCP/IP and click Properties
Click Add
Enter a Port Range you want to use 5000-5100 or something. The more similtanious connections the more ports you will need. 20 should be the minimum. Click ok all the way through till you get to the Component Services MMC. Then reboot.

You need to do that to all servers involved in the transaction that will be accessing machines through the firewall.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[LFCfan]

Thanks a bunch Denny!
Not had the chance yet to sort this out as other events have overtaken this (as these things are wont to do), but you've helped me make sense of it tremendously

Thanks again

[sub]~LFCfan[/sub]

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com