Troubleshooting a MSDTC issue between Windows XP SP2/SP1 + SQL 2005 and Windows 2003 SP1 + SQL 2000

Article
12/30/2005

I was happily coding a solution for common problem of writing data in a distributed transaction fashion. This was the first time I encountered the latest in security changes with service packs on workstation and server and the MSDTC. Most of the information is available in Microsoft KnoledgeBase but I found a couple more symptoms that may be of interest.

Disclaimer

Please note that the following information may help resolve the problems found, however it introduces potential significant vulnerabilities and appropriate security measures should be taken and the items below adjusted to properly enforce security in a production or similar environment.

Common Causes

MSDTC on server 'ClientComputer' is unavailable

OLE DB provider "SQLNCLI" for linked server "RemoteServer" returned message "No transaction is active."

New transaction cannot enlist in specified transaction coordinator

Error 8004d00a. Distributed Transaction error

The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "RemoteServer" was unable to begin a distributed transaction

Server: Msg 7391, Level 16, State 1, Line 1 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to start a distributed transaction.

Steps to get working (this is not secure intended for information purposes only)

Make sure MSDTC running on both machines

Follow instructions found https://support.microsoft.com/?kbid=873160

Although I did not have to reboot, some individuals on the internet required reboot to get the MSDTC settings to take affect

Troubleshooting a MSDTC issue between Windows XP SP2/SP1 + SQL 2005 and Windows 2003 SP1 + SQL 2000

Additional resources