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

Question

I'm trying to run a distributed transaction from my machine (SQL Server 2012) to a client server (SQL Server 2008).

I'm trying to run:

begin distributed transaction
select * from [172.01.01.01].master.dbo.sysprocesses
Commit Transaction

and I get:

OLE DB provider "SQLNCLI11" for linked server "172.01.01.01" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "172.01.01.01" was unable to begin a distributed transaction.

I can run a SELECT to that server with data coming back, so at least I know the servers can see each other, and the Linked Server exists and is operating

Now, there are multiple posts on the web for this, but I can't get it to work. This is what I have tried so far:

1. Set DTC properties to the following (on both server)

2. Restarted the Distributed Transaction Coordinator (MSDTC) from Control Panel -> Services (on both servers).

3. Uninstalled and installed DTC (on both servers).

4. Restarted the remote server.

5. Turned off the firewall on both servers.

6. Enabled sp_configure 'Ad Hoc Distributed Queries', 1 (on both servers).

7. I ran DTCPing and it pinged successful.

8. Linked server properties changed to the following:

What else are there to try?

UPDATE: Running the transaction from another server to 172.01.01.01 works. Therefore the issue is not on the destination server, but on my machine which is the source.

Answer 1

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

Answer 2

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".