Unable to begin a distributed transaction

Question

I'm trying to run SQL against a linked server, but I get the errors below :

BEGIN DISTRIBUTED TRANSACTION
SELECT TOP 1 * FROM Sessions


OLE DB provider "SQLNCLI" for linked server "ASILIVE" returned message "No transaction is active.".

Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "ASILIVE" was unable to begin a distributed transaction.

There are two errors returned by the provider:

Error #1:

Number: $80040E14
Source: Microsoft OLE DB Provider for SQL Server
Description: OLE DB provider "SQLNCLI" for linked server "ASILIVE" returned message "No transaction is active.".
HelpFile: 
HelpContext: $00000000
SQLState: 01000
NativeError: 7412

Error #2

Number: $80040E14
Source: Microsoft OLE DB Provider for SQL Server
Description: The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "ASILIVE" was unable to begin a distributed transaction.
HelpFile: 
HelpContext: $00000000
SQLState: 42000
NativeError: 7391

How do I get Microsoft to favor functionality over security?

Or, at least, how can I get two SQL Severs to talk to each other?

Related questions

The operation could not be performed because OLE DB provider “SQLNCLI10”... (*linked server name is (null))
Distributed transaction error? (using Oracle provider)
Unable to enlist in a distributed transaction with NHibernate (using Hibernate)
Error using distributed transaction in SQL Server 2008 R2 (SQL Server 2008 R2, no answer)
Distributed Transaction Error Only Through Code (caused by connection pooling)
Error performing distributed transaction coordinator in linked server (SQL Server 2008, no answer)
Distributed transaction error? (no accepted answer; only answer doesn't help)
How to Insert into remote table using Linked server withint Transaction? (accepted answer doesn't resolve)

What I have done is irrelevant, but I'll post it anyway.

Ensure Distributed Transaction Coordinator service is running on both machies:
Disable all MSDTC security on both machines:
Turn on random options on the linked server:

Cursed and swore.
Smashed things.

Checked that a SELECT can use the linked server:

    SELECT * FROM ASILive.CustomerManagementSystem.dbo.Users
    ....

    (763 row(s) affected)

Checked that client server can ping the remote server:

     C:\Documents and Settings\avatar>ping asicmstest.contoso.com

     Pinging asicmstest.contoso.com [10.0.0.40] with 32 bytes of data:

     Reply from 10.0.0.40: bytes=32 time<1ms TTL=128
     Reply from 10.0.0.40: bytes=32 time<1ms TTL=128
     Reply from 10.0.0.40: bytes=32 time<1ms TTL=128
     Reply from 10.0.0.40: bytes=32 time<1ms TTL=128

     Ping statistics for 10.0.0.40:
         Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
     Approximate round trip times in milli-seconds:
         Minimum = 0ms, Maximum = 0ms, Average = 0ms

Checked that the remote server can commnicate back, by name, to the initiating server:

     C:\Documents and Settings\avatar>ping asitestserver.contoso.com

     Pinging asitestserver.contoso.com [10.0.0.22] with 32 bytes of data:

     Reply from 10.0.0.22: bytes=32 time<1ms TTL=128
     Reply from 10.0.0.22: bytes=32 time<1ms TTL=128
     Reply from 10.0.0.22: bytes=32 time<1ms TTL=128
     Reply from 10.0.0.22: bytes=32 time<1ms TTL=128

     Ping statistics for 10.0.0.22:
         Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
     Approximate round trip times in milli-seconds:
         Minimum = 0ms, Maximum = 0ms, Average = 0ms

Checked that @@SERVERNAME matches the server name on both servers:

   SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')

   -------------  -------------
   ASITESTSERVER  ASITESTSERVER

and

   SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')

   ----------  ----------
   ASIGROBTEST  ASIGROBTEST

Screamed

Issued SET XACT_ABORT ON before issuing my query:

SET XACT_ABORT ON
GO
BEGIN DISTRIBUTED TRANSACTION
SELECT TOP 1 * FROM Sessions

Granted Everyone Full Control to:

HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer

on both servers.

good to see you didn't skip the crucial "cursed and swore" step! — jwl, Mar 11 '13 at 18:08
@joelarson Hey, sometimes people are very particular that you follow **every** step. i didn't want anyone saying that i didn't try something. — Ian Boyd, Mar 13 '13 at 18:04
I love this post. It typifies every experience I've ever had with MSDTC. — A. Murray, Sep 02 '16 at 10:05
In my case, after I set "Enable Promotion of Distributed Transactions for RPC" to False on the linked server, it works. Maybe try that — Sarsaparilla, Jan 03 '17 at 19:30
@HaiPhan Turning off the use of distributed transactions breaks transnational integrity: if you rollback your changes on the local server means that the changes will stay in place on the remote server. Very dangerous. — Ian Boyd, Jan 04 '17 at 21:02

Ian Boyd · Accepted Answer · 2015-11-27T15:18:54.467

Found it, MSDTC on the remote server was a clone of the local server.

From the Windows Application Events Log:

Event Type: Error
Event Source: MSDTC
Event Category: CM
Event ID: 4101
Date: 9/19/2011
Time: 1:32:59 PM
User: N/A
Computer: ASITESTSERVER
Description:

The local MS DTC detected that the MS DTC on ASICMSTEST has the same unique identity as the local MS DTC. This means that the two MS DTC will not be able to communicate with each other. This problem typically occurs if one of the systems were cloned using unsupported cloning tools. MS DTC requires that the systems be cloned using supported cloning tools such as SYSPREP. Running 'msdtc -uninstall' and then 'msdtc -install' from the command prompt will fix the problem. Note: Running 'msdtc -uninstall' will result in the system losing all MS DTC configuration information.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Running

msdtc -uninstall
msdtc -install

and then stopping and restarting SQL Server service fixed it.

Let's see if I've got it right : 1- msdtc -uninstall 2- msdtc -install 3- restart SQL Server service 4- restart SQL Agent service, just to be sure 5- even restart "Distributed Transaction Coordinator" service... I've done all this on both machines (main server & linked server) a~and nothing, still the same error ! — Hamid Sadeghian, Jun 30 '13 at 08:21

Steve Bauman · Answer 2 · 2020-10-12T18:00:43.230

21

I was able to resolve this issue (as others mentioned in comments) by disabling "Enable Promotion of Distributed Transactions for RPC" (i.e. setting it to False):

As requested by @WonderWorker, you can do this via SQL script:

EXEC master.dbo.sp_serveroption
     @server = N'[mylinkedserver]',
     @optname = N'remote proc transaction promotion',
     @optvalue = N'false'

edited Oct 12 '20 at 18:00

answered Jul 29 '19 at 21:11

Steve Bauman

8,165
7
40
56

1

This worked for me. What's strange is it's been fine for weeks, and then suddenly started. Possibly because the remote server's DTC service might be disabled. Anyway, thank you. – Boyd P Dec 19 '19 at 18:11
1

Brilliant. Do you know of a way to script this? On all my servers, all the linked server objects have had the Enable Promotion of Distributed Transactions set to true and I haven't been able to figure out who did it yet. It's so tedious to go through. – WonderWorker Oct 12 '20 at 11:34
1

@WonderWorker I've just added an example SQL script you can use, give it a shot! – Steve Bauman Oct 12 '20 at 18:01

score 7 · Answer 3 · answered Sep 19 '11 at 18:30

OK, so services are started, there is an ethernet path between them, name resolution works, linked servers work, and you disabled transaction authentication.

My gut says firewall issue, but a few things come to mind...

Are the machines in the same domain? (yeah, shouldn't matter with disabled authentication)
Are firewalls running on the the machines? DTC can be a bit of pain for firewalls as it uses a range of ports, see http://support.microsoft.com/kb/306843 For the time being, I would disable firewalls for the sake of identifying the problem
What does DTC ping say? http://www.microsoft.com/download/en/details.aspx?id=2868
What account is the SQL Service running as ?

score 2 · Answer 4 · answered Oct 09 '14 at 19:15

2

If the servers are clustered and there is a clustered DTC you have to disable security on the clustered DTC not the local DTC.

answered Oct 09 '14 at 19:15

David Wolfinger

21
1

score 2 · Answer 5 · answered Jan 02 '20 at 09:58

2

For me, it relate to Firewall setting. Go to your firewall setting, allow DTC Service and it worked.

answered Jan 02 '20 at 09:58

Hoang Tran

886
3
13
32

score 1 · Answer 6 · answered Oct 15 '13 at 08:35

My last adventure with MSDTC and this error today turned out to be a DNS issue. You're on the right track asking if the machines are on the same domain, EBarr. Terrific list for this issue, by the way!

My situation: I needed a server in a child domain to be able to run distributed transactions against a server in the parent domain through a firewall. I've used linked servers quite a bit over the years, so I had all the usual settings in SQL for a linked server and in MSDTC that Ian documented so nicely above. I set up MSDTC with a range of TCP ports (5000-5200) to use on both servers, and arranged for a firewall hole between the boxes for ports 1433 and 5000-5200. That should have worked. The linked server tested OK and I could query the remote SQL server via the linked server nicely, but I couldn't get it to allow a distributed transaction. I could even see a connection on the QA server from the DEV server, but something wasn't making the trip back.

I could PING the DEV server from QA using a FQDN like: PING DEVSQL.dev.domain.com

I could not PING the DEV server with just the machine name: PING DEVSQL

The DEVSQL server was supposed to be a member of both domains, but the name wasn't resolving in the parent domain's DNS... something had happened to the machine account for DEVSQL in the parent domain. Once we added DEVSQL to the DNS for the parent domain, and "PING DEVSQL" worked from the remote QA server, this issue was resolved for us.

I hope this helps!

score 1 · Answer 7 · answered Apr 01 '17 at 10:26

1

If your Destination server is on another cloud or data-center then need to add host-entry of MSDTC service(Destination Server) in your source server.

Try this one if problem doesn't resolved, After enable the MSDTC settings.

answered Apr 01 '17 at 10:26

JERRY

1,165
1
8
22

score 0 · Answer 8 · answered Mar 10 '15 at 15:48

0

Apart from the security settings, I had to open some ports on both servers for the transaction to run. I had to open port 59640 but according to the following suggestion, port 135 has to be open. http://support.microsoft.com/kb/839279

answered Mar 10 '15 at 15:48

Trepach

33
5

score 0 · Answer 9 · answered Oct 18 '19 at 12:45

I was getting the same error and i managed to solve it by configuring the MSDTC properly on the source server to allow outbound and allowed the DTC through the windows firewall.

Allow the Distributed Transaction Coordinator, tick domain , private and public options

score 0 · Answer 10 · answered Aug 11 '21 at 17:21

0

The Firewall settings to Allow the DTC the ticking Private and Public as well as Domain did the trick. Just Domain is not enough (it feels like it should be, but nah).

answered Aug 11 '21 at 17:21

CrushingIT

1
1

score 0 · Answer 11 · answered Jun 16 '23 at 05:37

Yesterday i had a similiar issue so i thought i will share my experience and the solution.

We were trying to insert rows from source to a destination table using linked server, but we were getting the error "Unable to begin a distributed transaction"

After trying multiple things we found out that the problem was we were using the destination table name directly in the synonyms

We ended up creating a stored procedure in the destination server which inserts the row, and from our source synonyms we called the stored procedure and wollah everything worked out, Hope it helps.

Unable to begin a distributed transaction

Related questions

11 Answers11

Linked

Related