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

Setting "Enable promotion of distributed transaction" flag to false (in Linked Server Properties Window) solved my similar problem.


I faced a similar problem and I resolved it as follows. There is a node in tree structure of object explorer in SQL Server. There you will find Serverobjects → LinkedServers → below that there is a list of IP addresses of distributed servers.

Right click on it, select properties, a window will pop up. Select server options in the left pane; you will get list of properties. Set the flag value false to the property "Enable promotion of distributed transaction".


If after configuring your MS Distributed Transaction Coordinator (MSDTC) on the two SQL server's according to the OP's original post, you still get "no transaction active", you should check that each host is reachable via the IP (assuming that's what you've used) registered in the linked server.

For example; on a recent setup, two SQL servers were reachable through a network in the 192.168.200.x range (same subnet), but each server was also indirectly connected through an IP in the 10.x.x.x range. On one SQL Server, the DNS server it used kept resolving the target SQL server to it's 10.x.x.x IP (which was firewalled) even though the linked server entry used the IP in the 192.168.200.x of the target server.

It appears that MSDTC uses the hostname of the server, while SQL server connects over any linked connection using the IP or hostname defined in the linked server entry, leading to this confusing behaviour of apparent connectivity when checking the target linked server within SQL Management Studio, but inability to execute remote procedures on the target.

The solution was to add entries in the host file's (%windir%\system32\drivers\etc\hosts) to explicitly force each SQL server to resolve the other to the IP address on the 192.168.200.x network.

On host 1 (IP 192.168.200.15):

# TARGET SERVER
192.168.200.20    targetserverhostname.and.any.domain.suffix  targetserverhostname

On host 2 (IP 192.168.200.20)

# SOURCE SERVER
192.168.200.15    sourceserverhostname.and.any.domain.suffix sourceserverhostname

Don't forget to ensure MSDTC has been configured according to the OP's screenshot above allowing network access and (if required) No Authentication.