Fix – ‘The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server was unable to begin a distributed transaction.’ In SQL Server

There has been a lot of times where we can encounter this error - “The operation could not be performed because OLE DB provider "SQLNCLI11" for the linked server was unable to begin a distributed transaction.” and we may simply not be aware of what next. Basically, this error has crept when we try to fetch information from one server to another through a linked server and perform DML operations at a local instance. Additionally, if you notice that this happens particularly while working on stored procedures.

Before jumping on to the actual fix, please check for the MSDTC feature is enable in component services settings.

To check that, please follow the below steps:

1. Search for “Component Services” application on your computer.

2. Double click on “Component Services” and expand until you see “Local DTC”.

3. Right-click on “Local DTC” and select properties.

A new pop-up window opens, and you can notice 3 tabs. Please click on the “Security” to find something as below:

Please be sure that the Network DTC Access is enabled on the server where you are encountering the error message.

Once we are sure of the Network DTC Access is enabled, the next troubleshooting we must do is to check the “Enable Promotion of Distribution Transactior” feature under the linkedserver properties.

Let’s check at the steps to navigate to Linked Server Properties.

1. Please open the server instance and expand the “Server Objects” option.

2. Navigate to option “Linked Server”.

3. Right Click on the respective linked server properties and navigate to “Server Options”.

4. Please switch the “Enable Promotion of Distributed Transactior” option to “False”.