Unable to begin a distributed transaction

Ian Boyd picture Ian Boyd · Sep 19, 2011 · Viewed 175.2k times · Source

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


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

  1. Ensure Distributed Transaction Coordinator service is running on both machies:

    enter image description here

    enter image description here

  2. Disable all MSDTC security on both machines:

    enter image description here

    enter image description here

  3. Turn on random options on the linked server:

enter image description here

  1. Cursed and swore.

  2. Smashed things.

  3. Checked that a SELECT can use the linked server:

        SELECT * FROM ASILive.CustomerManagementSystem.dbo.Users
        ....
    
        (763 row(s) affected)
    
  4. 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
    
  5. 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
    
  6. Checked that @@SERVERNAME matches the server name on both servers:

       SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')
    
       -------------  -------------
       ASITESTSERVER  ASITESTSERVER
    

    and

       SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')
    
       ----------  ----------
       ASIGROBTEST  ASIGROBTEST
    
  7. Screamed

  8. Issued SET XACT_ABORT ON before issuing my query:

    SET XACT_ABORT ON
    GO
    BEGIN DISTRIBUTED TRANSACTION
    SELECT TOP 1 * FROM Sessions
    
  9. Granted Everyone Full Control to:

    HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer
    

    on both servers.

Answer

Ian Boyd picture Ian Boyd · Sep 19, 2011

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.