Recommended MSDTC settings for using Distributed Transactions in SQL Server

MSDTC needs to be configured correctly for distributed SQL Server transactions to work properly – one of the main places you will encounter this problem is running distributed transactions across linked servers where part of the transaction runs on the local SQL Server instance and part of the transaction runs on the linked server. If there is a problem then the following error message may be displayed:

OLE DB provider "SQLNCLI10" for linked server "linked server name" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "linked server name" was unable to begin a distributed transaction.



To configure the MSDTC to handle distributed transactions you will need to configure it’s network access as per this technet article. The key steps to remediate this problem are:



Configuring network transactions for MS DTC


If your distributed transactions fail because of network connectivity problems, this failure might be related to the configuration of the DTCs that are participating in the transactions. Check each DTC and make sure that Network MS DTC Access is enabled. You can use the following procedure to accomplish this task.

To enable Network DTC Access for MS DTC transactions

1.Open the Component Services snap-in.

To open Component Services, click Start. In the search box, type dcomcnfg, and then press ENTER.

2.Expand the console tree to locate the DTC (for example, Local DTC) for which you want to enable Network MS DTC Access.

3.On the Action menu, click Properties.

4.Click the Security tab and make the following changes:
In Security Settings, select the Network DTC Access check box.


In Transaction Manager Communication, select the Allow Inbound and Allow Outbound check boxes.



5.Click OK.



 



It’s also advised to check your firewall settings and ensure that DTC can make it through the firewall.

Comments

Popular posts from this blog

Monitoring Raspberry Pi Performance Using Cacti

Using NAGIOS to Check the Physical Memory Available on a Windows Host

Error 2738 Occurs When Installing An MSI Package