MSDTC :: WIN2k3SP1/WINXPSP2 :: Windows Firewall
When you need to work with distributed resources (two databases for instances); you need distributed transactions. Generally; two phase commit technique is used in distributed transactions. Microsoft Distributed Transaction Coordinator (MSDTC) is one such implementation; and it’s available on Windows out of the box. Another good thing about it; that its tightly integrated with SQL Server and COM+ (COM based enterprise services which is also available out of the box on any Window machine)
MSDTC has certain drawbacks; it uses Remote Procedure Call (RPC) and in past there had many security loop holes reported in RPC implementation of Windows. If you happen to use Windows Firewall on Windows 2003 SP1 machines; and you want to use MSDTC you need to perform certain steps.
- On installing SP1; all network communication of MSDTC is disabled; so you need to enable it first. SP1 also has an enhanced MSDTC network communication implementation; it now supports authentication; and one can implement Windows Authentication.
- You need to make a rule to allow RPC (TCP/135) in Windows Firewall.
- Lastly you need to make a rule to allow MSDTC process (C:\WINDOWS\system32\msdtc.exe) in Windows Firewall.
One small note; if you are using Linked Databases in SQL; and if you use begin/commit transaction pairs in your TSQL; and the transaction involves linked databases; MSDTC is used auto-magically for such distributed transaction. In these scenarios; you need to do the above as well.