SQL Server Distributed Transactions Problems

Hi everybody.
Recently i´ve been working in an app with Microsoft SQL Server 2008.
One of the things the app must do is:
From one instance of SQL Server begin a distributed transaction and commit the transaction in another instance of SQL Server in other server.
According to microsoft, in both servers you must have the MSDTC service configured and started. I´m not going to deep in detail right now (but then i will).
I was happy beacause i had all in its place and from the source server i executed the next query:


but after executed the query i got an awful error:


OLE DB provider "SQLNCLI10" for linked server "" returned message "No transaction is active.".Msg 7391, Level 16, State 2, Line 5
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server " " was unable to begin a distributed transaction.
And that was the beginning of my headache. I was googling for some days and found nothing that could help me.
Before this happen all was good and worked well. The scenario before the error is next:
Server 1

• Windows Server 2003 R2 (x86) Standard Edition
• SQL Server 2008 (x86) Standard Edition SP1

Server 2

• Windows Server 2003 (x86) Enterprise Edition
• SQL Server 2000 Estandard Edition SP3

The MSDTC service was configured and started. There was no problem in executing the distributed transaction.
But IT team decided that the company needed new hardware and software(OS) for the servers.
So the scenario changed as follows:
Server 1

• Windows Server 2008 R2 Enterprise Edition (x64)  SP1

• SQL Server 2008 Standard Edition (x64) SP2 10.0.4000

Server 2

• The same as before

I thought everything was good, but after testing the distributed transaction i got the error.
I spent several hours trying to find a solution by googling and posting in forums, but nothing seems to take me to the solution.
Looks like in Windows Server 2008, the MSDTC has been improved and you have to be careful with the configuration.
So the problem was that i could not start a distributed transaction from a x64 bit Windows 2008 OS to a x86 bit Windows 2003 OS.
After waiting for replays in posts and trying several combinations of configurations nothing worked.
But finally i found a solution, so here it is:
Network Connectivity
First of all:

• Make sure both servers can ping each other by the machine name, cause MSDTC uses netBIOS to establish a connection.
• Open the command line on both servers and run this command
  • On Server 1:   ping Server2
  • On Server 2: ping Server1
• If you got response from both servers you are good, else you need to check your network dns.
• Figure out if a firewall is active and blocking the MSDTC communication.

This was one of my problems cause Server 1 is in one lan and Server 2 in other and there was not mx records in the DNS for the servers but was easy to solve asking the network administrator to register the names in the DNS.
DTC Configuration
The second step is configure the MSDTC on both Servers and Here is how:
On Server 1 (Windows Server 2008)
1.- Open Start->Administrative Tools->Component Services.

2.- Expand the Component Services Node untill you find Local DTC right click and select properties option

3.- In the Local DTC properties clic in the security section and set the configuration as in the image

Two parts of this image are the most important in configuration of DTC in WS2008:

• No Authentication Required.
• Enable SNA LU 6.2 Transactions.

Note that every time you make a change in the DTC Security configuration, the DTC service is restarted automatically.
The final step is make shure the DTC service is started and configured to start when windows start.
1. Open Start->Administrative Tools->Services

2. Look for the Distributed Transactions Coordinator Service

Make sure the status is “Started” and the Startup Type is “Automatic”.

On Server 2 (Windows Server 2003)
To configure the DTC follow these steps
1. Open Start->Adminitrative Tools->Component Services

2.- In the Components Services window Expand Component Services->Computers->My Computer and right clic on this node and select properties option

3. In this new window clic on the MSDTC section and find the Security Configuration button

4. In the Security Configuration window apply the changes as in the next image and clic OK

And that is all the configuration of the DTC.
Now you need the verify that the service is started, i´m not going to detail this step as is the same in WS2008.
Just remember that all this configurations mus be done with an account that has the permissions to do it.
Linked Server Configuration on Server 1
Now the DTC is configured on both servers we must create and configure a linked server on Server 1 pointing Server 2.
Im not going to give much detail in how this is done, but here is a quick shot:

Here is my Linked Server in the SQL Server 2008 instance pointing to my SQL Server 2000 instance.
And remeber to apply this settings to the linked server:
Data Access: True
RPC: True
Enable Promotion of Distributed Transactions: True
You can do this when creating the linked server or when it already exists just right clic over it and select properties, then clic on the Server Options option in the left panel and change the options.



Testing Distributed Transactions
We’ve come so far so the final step is run the distributed transaction query of the beginning:

As you can see no error is raised.
If none of this stuff helps i recommend you to visit the next links for a deep troubleshooting of DTC:
Troubleshooting MSDTC Part 1
http://www.mssqltips.com/tip.asp?tip=2083
Troubleshooting MSDTC Part 2
http://www.mssqltips.com/tip.asp?tip=2113
MSDN posts
http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/523116d4-4c8e-43a8-abf4-e705ffaa68c8
Thanks for reading and hope this is very informative and useful for you.