Linked Server Problem – Windows 2003 SP1. (Setting MSDTC Security Configuration)
In one of our server running Windows 2003 & SQL Server 2000 our system administrators has applied the service pack 1 and they have completed successfully. The next day night all the SQL jobs in another server (Windows 2000 & SQL Server 2000) which is configured to link the Win2k3 server has been failed with the error message below.
Executed as user: mydomain\sqlaccount. The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [SQLSTATE 42000] (Error 7391) [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]. [SQLSTATE 01000] (Error 7300). The step failed.
I’m sure the problem is with MSDTC in the destination server (Win2k3) but not sure what exactly it is. I’ve googled and found the KB articles http://support.microsoft.com/kb/816701 and http://support.microsoft.com/kb/329332 but both the articles didn’t helped me. I’ve started digging it more and finally found that in Windows 2003 SP1 Microsoft has added a new security feature for MSDTC service, below is the quote from the KB article http://support.microsoft.com/kb/899191
Microsoft Windows Server 2003 Service Pack 1 (SP1) and Microsoft Windows XP Service Pack 2 (SP2) include many security-related updates and changes. Some of these changes affect the Microsoft Distributed Transaction Coordinator (MSDTC) service.
After you install Windows Server 2003 SP1 or Windows XP SP2, some changes are made to the default security settings that cause Distributed Transaction Coordinator traffic to fail over the network. In this situation, you may receive one or more error messages or error codes.
Ok, Now I’ve got the information needed that why the jobs started to fail. I’ve done the steps to remove the linked server error and it worked for me.
- Goto Run Type dcomcnfg.exe and press enter
- Expand “Component Services” –> Computers, now you can see My Computer there
- Right click on “My Computer” and click on properties as shown below
- Now you will shown with MSDTC properties as show below. Click the MS DTC tab, and then click Security Configuration.
- In the Security Configuration dialog box, click to enable Network DTC Access check box.
- Under Network DTC Access, click Network Transactions. Under Network DTC Access, select Allow Inbound and/or Allow Outbound depending on whether the server is initiating or receiving the request. Select No Authentication Required if the linked servers are not also running Windows 2003 SP1. This option exists only in Windows server 2003.
- Make sure that DTC Logon Account is set to NT Authority\NetworkService.
- Click OK. In the message box, click Yes to continue.
- Now the MSDTC service is restarted and the settings has been changed. Now use your linked server queries, this should succeed now.
- If you have any concerns or suggestions for this article, please post it in our discussion thread.
June 11th, 2009 - 17:01
Hi Vidhya Sagar,
I am also getting the same error when connecting to linked server. I can query the linked server table but within a trigger i am getting the error.
My settings are same as what you said above. The error is below
OLE DB provider “SQLNCLI” for linked server “server2″ returned message “The partner transaction manager has disabled its support for remote/network transactions.”.
Msg 7391, Level 16, State 2, Procedure EMP_OLD_INS_UPD_DEL_TRG, Line 20
The operation could not be performed because OLE DB provider “SQLNCLI” for linked server “server2″ was unable to begin a distributed transaction.
June 11th, 2009 - 18:31
Did you make sure that MSDTC is running and configured as above? Since this error is similar to MSDTC settings.