Partager via

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

  • Article

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

Related articles that may help...

How to troubleshoot MS DTC firewall issues
https://support.microsoft.com/default.aspx?scid=kb;en-us;306843

DTCPing troubleshooting tool
https://support.microsoft.com/default.aspx?scid=kb;en-us;306843

Original article that helped the most with configuration of MSDTC
https://support.microsoft.com/?kbid=873160

Vulnerabilities in MSDTC and COM+ Could Allow Remote Code Execution (902400)
https://www.microsoft.com/technet/security/Bulletin/MS05-051.mspx

Comments

  • Anonymous
    November 01, 2006
    Let me just thank you, I had that problem with MSDTC and solved it by following the link you provided. Thank you.

  • Anonymous
    September 27, 2007
    Thanks for the pointers with this. I was having huge problems until I read your post and followed the link to Microsoft.

  • Anonymous
    February 13, 2008
    Hi, This is a big comment.Thanks for your patience. Update stmt. fails within a transaction over linked server. But this error occurs only one-way. OLE DB provider “SQLNCLI” for linked server “B” returned message “No transaction is active.”. Msg 7391, Level 16, State 2 The operation could not be performed because OLE DB provider “SQLNCLI” for linked server “B” was unable to begin a distributed transaction. The following samples are run on Server A: 1./- ERROR (above) begin tran update B.dbmaint..abc set x=5 where y = 4 commit tran -/ 2./-WORKS update B.dbmaint..abc set x=5 where y = 4 -/ 3./-ERROR begin distributed tran select * from B.dbmaint..abc commit tran -/ 4./-WORKS begin tran select * from B.dbmaint..abc commit tran -/ All 4 samples above work from server B 1./- WORKS begin tran update A.dbmaint..abc set x=5 where y = 4 commit tran -/ etc.. Server A: Microsoft SQLServer 2005-9.00.3042.00(Intelx86) Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1) Server B: Microsoft SQLServer 2005-9.00.3042.00(Intelx86) Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2) Thanks

  • Anonymous
    January 21, 2009
    PingBack from http://www.keyongtech.com/2150256-sql-2005-updating-data-in

  • Anonymous
    September 04, 2009
    Thanks, link helped sort my problems out.