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 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.
SELECT * FROM LinkedServername.msdb.dbo.sysjobs
The above query should be succeeded.
This entry was posted in SQL server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s