Sql-server – Unable to begin a distributed transaction

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.

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

   

   

2. Disable all MSDTC security on both machines:

   

   

3. Turn on random options on the linked server:

4. Cursed and swore.

5. Smashed things.

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

       SELECT * FROM ASILive.CustomerManagementSystem.dbo.Users
       ....
   
       (763 row(s) affected)
   

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

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

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

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

   and

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

10. Screamed

11. Issued SET XACT_ABORT ON before issuing my query:

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

12. Granted Everyone Full Control to:

    HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer
    

    on both servers.