SQL Server2000 Distributed Transaction Error resolution method

If you run a linked server query in the SQLServer2000 installed on Windows.netserver, the following error may be returned (even if MSDTC is running on the server):

Symptom if you run a linked server query in the SQLServer2000 installed on Windows.netserver, the following error may be returned (even if MSDTC is running on the server): Server:msg8501,level16,state3,line3 Msdtconserver ' servername ' Isunavailable. In the SQL Server error log, you will find the following errors raised by the SPID that runs the query: resourcemanagercreationfailed:resultcode= 0x8004d01c reason this is the design dictates. This error occurs if the Distributed Transaction Coordinator service is running under the local System account. Solution Recent changes to DTC made by RPC security require MSDTC to run as "Ntauthority\networkservice". This is. The default option for NetServer. In WindowsNT and Windows2000, the MSDTC service is run under the "Local System" account by default.

Warning: Improper use of Registry Editor can cause serious problems that may require you to reinstall your operating system. Microsoft is not guaranteed to resolve problems caused by improper use of the Registry Editor. Using Registry Editor requires your own risk.

To change the MSDTC service account from "Local System" to "Ntauthority\networkservice", follow these steps: 1. Run regedt32, browse to Hkey_local_machine\software\ Microsoft\msdtc. Add a DWORD value of TurnOffRpcSecurity, the value data is 1.2. Open a command prompt, run "NETSTOPMSDTC", and then run "NETSTARTMSDTC". 3. Go to the Component Services Administration tool ".

A. Browse to the Start administration tool.
B. Select Component Services.
C. Expand the Component Services tree, and then expand My Computer.
D. Right-click My Computer and choose Properties.
E. In the MSDTC tab, make sure the following options are selected: Network DTC access network management network transaction XA transaction In addition, the DTC login account must be set to "Ntauthority\networkservice".
F. Click OK. This will prompt you to "MSDTC will stop and reboot." All dependent services will be stopped. Please press ' yes ' to continue. Click Yes to continue.
G. Click OK to close the My Computer Properties window.

4. Run Regedt32 again, browse to HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MSDTC, and then delete the turnoffrpcsecurity entry. Your MSDTC service should now run under the "Ntauthority\networkservice" account, and this error will not occur again.

More information when network DTC access is disabled under the MSDTC Security configuration, running a linked server query returns the following error: Server:msg7391,level16,state1, Line2theoperationcouldnotbeperformedbecausetheoledbprovider ' SQLOLEDB '
Wasunabletobeginadistributedtransaction. [Ole/dbproviderreturnedmessage:newtransactioncannotenlistinthespecifiedtransactioncoordinator.]
Trace flag 7300 when opened: Server:msg7391,level16,state1,line2theoperationcouldnotbeperformedbecausetheoledbprovider ' SQLOLEDB '
Wasunabletobeginadistributedtransaction. [Ole/dbproviderreturnedmessage:newtransactioncannotenlistinthespecifiedtransactioncoordinator.] Oledberrortrace[ole/dbprovider ' SQLOLEDB ' itransactionjoin::jointransactionreturned0x8004d00a].
The MSDTC service must be set according to the instructions in the "Resolution" section above to prevent this error.

Steps to reproduce the phenomenon

1. Go to the Start Administration tools service.
2. Right-click the Distributed Transaction Coordinator service, go to the Login tab, and select the Local System account under logon status.
3. Stop and restart the service.
4. From Query Analyzer, run the following script:execsp_addlinkedserverremote1execsp_setnetnameremote1,<remoteservername> Goexecremote1.pubs.dbo.sp_executesqlN ' Createtablet (c1int) ' gosetxact_ Abortongobegintraninsertintoremote1.pubs.dbo.tvalues (1) Committrango