SQL

OLE DB provider SQLNCLI11 for linked server unable to begin distributed transaction

OLE DB provider SQLNCLI11 for linked server unable to begin distributed

Today in working with a new virtual machine that was spun up with Windows Server 2012 R2 and SQL Server 2014, I was asked to troubleshoot an issue where distributed transactions were not working from this particular VM and other SQL servers and failing with the error OLE DB provider SQLNCLI11 for linked server unable to begin distributed transaction. ย We knew that DTC was working correctly between the others server and not the new VM. ย The other SQL boxes were old Windows 2003 Servers running SQL 2005.

However, in doing some digging into the configuration of DTC on the 2012/SQL2014 box, I found the default settings were not configured correctly to allow communication with the other servers. ย Let me detail those settings that are configured out of the box for Windows Server 2012 R2 for Local DTC. ย These configuration settings are found under theย Component Services snapin which can be launched by typingย dcomcnfg.mscย at a run/command window.

  • Right click on theย Local DTC option under theย Distributed Transaction Coordinator

msdtc_01

 

  • You will then see the Local DTC Properties dialog box. ย Navigate to theย Security tab. ย As you can see below, the default options are that everything is unselected – Network DTC Access, Transaction Manager Communication, etc.

msdtc_02

  • Place a check in the Network DTC Accessย and theย Allow Inbound andย Allow Outboundย under the Transaction Manager Communication option. ย We also selectedย No Authentication Required as this was the way the other servers in the enviroment were configured.

msdtc_03

Also, in addition to the configuration above, you need to make sure that the Windows Firewall is setย in the Allowed apps and features to allowย Distributed Transaction Coordinatorย for at leastย Domain if you are in a domain environment. ย Set the rules here according to your particular environment and network setup.

msdtc_04

After setting up MSDTC and then also the Windows Firewall rules as they should be to allow the MSDTC traffic through, all the errors that we had previously with the distributed transactions in SQL code running on this test box went away.

The errors we saw ranged from:

The operation could not be performed because OLE DB provider “SQLNCLI11” for linked server was unable to begin a distributed transaction.

Also received:

OLE DB provider “SQLNCLI11” for linked server returned message “No transaction is active.”

 

Subscribe to VirtualizationHowto via Email ๐Ÿ””

Enter your email address to subscribe to this blog and receive notifications of new posts by email.



Brandon Lee

Brandon Lee is the Senior Writer, Engineer and owner at Virtualizationhowto.com, and a 7-time VMware vExpert, with over two decades of experience in Information Technology. Having worked for numerous Fortune 500 companies as well as in various industries, He has extensive experience in various IT segments and is a strong advocate for open source technologies. Brandon holds many industry certifications, loves the outdoors and spending time with family. Also, he goes through the effort of testing and troubleshooting issues, so you don't have to.

Related Articles

3 Comments

  1. Had to deal with this same issue today and this was very helpful. Thanks!
    One note though… your wording of “Check off Network DTC aCCESS” is slightly confusing. I’d say it actually needs to be check, or check “on”.

    1. Thanks TP-Trent….glad the post was helpful. Also, I edited the wording of the section you mentioned, good catch on that! Thanks again.

  2. Thank you so much! The firewall setting did the trick for me. The DTC was not checked in there already, but it is surprising that it would work on and off even when the check was not filled.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.