Microsoft SQL Server: SQLNCLI11 – Linked Server DTC Issue

When using a Microsoft SQL Server – Linked Server between a remote database server, you may encounter an error when trying to pass transaction sets between the environments.

[Microsoft][SQL Server Native Client 11.0][SQL Server]OLE DB provider “SQLNCLI11” for linked server “REMOTEDB” returned message “The transaction manager has disabled its support for remote/network transactions.”.
[Microsoft][SQL Server Native Client 11.0][SQL Server]The operation could not be performed because OLE DB provider “SQLNCLI11” for linked server “REMOTEDB” was unable to begin a distributed transaction.

In order to rectify this issue you will want to turn on Distribution Transaction Coordinator (DTC) to allow remote connections. On Windows 2008, go into Administrative Tools > Component Services, expand the component services, computers, my computer, and distributed Transaction Coordinator. Its relatively the same process for Windows 2012 & 2012R2.

Right click on the Local DTC, and select properties. Under the Security Tab, select allow remote clients and agents, and allow inbound and outbound transactions. You will need to do this on both the local and remote databases. When you make changes to the DTC it will restart the service. Depending on your configuration you may need to reboot the server for this to take effect.

What is DTC?

The Distributed Transaction Coordinator (MSDTC) service is a component of modern versions of Microsoft Windows that is responsible for coordinating transactions that span multiple resource managers, such as databases, message queues, and file systems. MSDTC is included in Windows 2000 and later operating systems, and is also available for Windows NT 4.0. MSDTC performs the transaction coordination role for components, usually with COM and .NET
architectures. In MSDTC terminology, the director is called the transaction manager. By default, the Microsoft Distributed Transaction Coordinator (MSDTC) service is installed with Windows 2000 and onwards, and is considered to be core functionality that cannot be uninstalled.