Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

Recommended MSDTC settings for using Distributed Transactions in SQL Server


View products that this article applies to.

Summary

When you start distributed transactions that involve SQL Server Database Engine as a resource, you might encounter various error messages if the MSDTC configuration is not correct. When you have more than one SQL Server involved in a Distributed Transaction, you need to make some changes to the default configuration of MSDTC for these distributed transactions to succeed.

The error messages you will encounter if you do not configure MSDTC properly are as follows:
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.

↑ Back to the top


More Information

For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table:

Rule SoftwareRule TitleRule descriptionProduct versions against which the rule is evaluated 
SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA)














MSDTC settings not configured optimally















The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides a rule to detect situations where the MSDTC is not configured properly. The SQL Server 2008 R2 BPA supports both SQL Server 2008 and SQL Server 2008 R2. 

If you run the BPA tool and encounter a warning with the title of Engine - MSDTC settings not configured optimally, then you need to verify the MSDTC configuration settings.

The configuration parameters checked by the SQL Server 2008 R2 BPA are discussed in detail in the following MSDN topics :
Enable Network Access Securely for MS DTC
Enable Network DTC Access

Note: You will get this warning in the BPA tool even if the MSDTC registry key is missing which could indicate that the MSDTC install is corrupt.
SQL Server 2008
SQL Server 2008 R2
















SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA)MSDTC settings not configured optimally
The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides a rule to detect situations where the MSDTC is not configured properly. If you run the BPA tool and encounter a warning with the title of Engine - MSDTC settings not configured optimally, then you need to verify the MSDTC configuration settings.

The configuration parameters checked by the SQL Server 2012 BPA are discussed in detail in the following MSDN topics :
Enable Network Access Securely for MS DTC
Enable Network DTC Access

Note: You will get this warning in the BPA tool even if the MSDTC registry key is missing which could indicate that the MSDTC install is corrupt.
SQL Server 2012














You can review and update the MSDTC settings using the instructions from the following articles:
817064 How to enable network DTC access in Windows Server 2003
306212 How to troubleshoot error 7391 that occurs when you use a linked server in SQL Server
899191 New functionality in the Distributed Transaction Coordinator service in Windows Server 2003 Service Pack 1 and in Windows XP Service Pack 2
873160 You may receive a 7391 error message in SQLOLEDB when you run a distributed transaction against a linked server after you install Windows XP Service Pack 2 or Windows XP Tablet PC Edition 2005
940971 An error message is logged in the Summary.txt file when a SQL Server 2005 service pack, cumulative update or cluster hotfix installation fails: "The Transaction Manager is not available"

If you plan to use the SQL Server with applications that start XA transactions, then you need to enable XA transaction support in MSDTC. Refer to the following topics for more information on this:
Supporting XA Transactions
Understanding XA Transactions

↑ Back to the top


Keywords: kb

↑ Back to the top

Article Info
Article ID : 2027550
Revision : 3
Created on : 11/20/2019
Published on : 11/20/2019
Exists online : False
Views : 496