OLE DB provider SQLNCLI11 for linked server unable to begin distributed transaction
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
- 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.
- 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.
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.
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.”
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”.
Thanks TP-Trent….glad the post was helpful. Also, I edited the wording of the section you mentioned, good catch on that! Thanks again.
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.