Debug Distributed Transactions Error in SQL Server

In Windows 2003, the security was tight on MSDTC service. you will get the following error if you start a distributed transaction agaist a linked server:

Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

To enable distributed transactions against linked SQL server or in ad-hoc queries, please follow these steps:

1) Enable network DTC access:
Goto control panel-->Add or Remove Programs, click Add/Remove Windows Components. In the popup window, highlight Application servers, click "Details..." button. In popup window, check "Enable network DTC access" check box if it's not selected.

You need to reboot your computer for this change to take effect.

2) Set up MSDTC security
Goto Administrator Tools-->Componet Services, expand Component Services-->Computers, right click "My Computer"-->Properties-->MSDTC tab-->"Security Configuration...", in the popup window,
Check Network DTC Access;
Check Allow IUnBound and Allow Outbound
make sure DTC Logon Account is NT Authority\NetWorkService

Save the change, MSDTC will restart

3) If firewall exists between the involved servers.
DTC uses Remote Procedure Call (RPC) dynamic port allocation. By default, RPC dynamic port allocation randomly selects port numbers above 1024. By modifying the registry, you can control which ports RPC dynamically allocates for incoming communication. You can then configure your firewall to confine incoming external communication to only those ports and port 135 (the RPC Endpoint Mapper port).
Reference MS article http://support.microsoft.com/?id=250367 to configure the ports RPC uses and firewall.

4) If your servers are running on different domain, or if you can run queries against the linked server, but get the same error messages when transaction is used, you need to change Authentication Mode of MSDTC security.

In the same UI in step 2), select "No Authentication Required" option. This is equivalent to turn off the RPC security. You can achieve the same effect by changing the registry key avlue:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\TurnOffRpcSecurity to 1. If the key not exists, you need to manually add it.

The option in the UI "Incoming Caller Authentication Required" only athenticate incoming RPC. It can be acheieved by the regisgy key value: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\FallbackToUnsecureRPCIfNecessary to 1.

If you manually change registry, you need to restart MSDTC service.

Reference MS article: http://support.microsoft.com/?id=899191

BEGIN TRAN
SELECT * FROM LinkedServername.msdb.dbo.sysjobs
COMMIT TRAN
GO

The above query should be succeeded.

From: http://p-sql.spaces.live.com/?_c11_blogpart_blogpart=blogview&_c=blogpart&partqs=amonth%3d2%26ayear%3d2007

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________