title: Unable to begin a distributed transaction
body: <p>I'm trying to run SQL against a linked server, but I get the errors below :</p>
  <pre><code>BEGIN DISTRIBUTED TRANSACTION
  SELECT TOP 1 * FROM Sessions
  
  
  OLE DB provider &quot;SQLNCLI&quot; for linked server &quot;ASILIVE&quot; returned message &quot;No transaction is active.&quot;.
  
  Msg 7391, Level 16, State 2, Line 3
  The operation could not be performed because OLE DB provider &quot;SQLNCLI&quot; for linked server &quot;ASILIVE&quot; was unable to begin a distributed transaction.
  </code></pre>
  <p>There are two errors returned by the provider:</p>
  <p><strong>Error #1:</strong></p>
  <pre><code>Number: $80040E14
  Source: Microsoft OLE DB Provider for SQL Server
  Description: OLE DB provider &quot;SQLNCLI&quot; for linked server &quot;ASILIVE&quot; returned message &quot;No transaction is active.&quot;.
  HelpFile: 
  HelpContext: $00000000
  SQLState: 01000
  NativeError: 7412
  </code></pre>
  <p><strong>Error #2</strong></p>
  <pre><code>Number: $80040E14
  Source: Microsoft OLE DB Provider for SQL Server
  Description: The operation could not be performed because OLE DB provider &quot;SQLNCLI&quot; for linked server &quot;ASILIVE&quot; was unable to begin a distributed transaction.
  HelpFile: 
  HelpContext: $00000000
  SQLState: 42000
  NativeError: 7391
  </code></pre>
  <p>How do I get Microsoft to favor functionality over security?</p>
  <p>Or, at least, how can I get two SQL Severs to talk to each other?</p>
  <h2>Related questions</h2>
  <ul>
  <li><a href="https://stackoverflow.com/questions/6407447/the-operation-could-not-be-performed-because-ole-db-provider-sqlncli10-for-link">The operation could not be performed because OLE DB provider “SQLNCLI10”...</a> (*linked server name is <code>(null)</code>)</li>
  <li><a href="https://stackoverflow.com/questions/6999906/distributed-transaction-error">Distributed transaction error?</a> (<em>using Oracle provider</em>)</li>
  <li><a href="https://stackoverflow.com/questions/1012147/unable-to-enlist-in-a-distributed-transaction-with-nhibernate">Unable to enlist in a distributed transaction with NHibernate</a> (<em>using Hibernate</em>)</li>
  <li><a href="https://stackoverflow.com/questions/6608191/error-using-distributed-transaction-in-sql-server-2008-r2">Error using distributed transaction in SQL Server 2008 R2</a> (<em>SQL Server 2008 R2, no answer</em>)</li>
  <li><a href="https://stackoverflow.com/questions/4205484/distributed-transaction-error-only-through-code">Distributed Transaction Error Only Through Code</a> (<em>caused by connection pooling</em>)</li>
  <li><a href="https://stackoverflow.com/questions/7298565/error-performing-distributed-transaction-coordinator-in-linked-server">Error performing distributed transaction coordinator in linked server</a> (<em>SQL Server 2008, no answer</em>)</li>
  <li><a href="https://stackoverflow.com/questions/6999906/distributed-transaction-error">Distributed transaction error?</a> (<em>no accepted answer; only answer doesn't help</em>)</li>
  <li><a href="https://stackoverflow.com/questions/3033838/how-to-insert-into-remote-table-using-linked-server-withint-transaction">How to Insert into remote table using Linked server withint Transaction?</a> (<em>accepted answer doesn't resolve</em>)</li>
  </ul>
  <hr />
  <p>What I <em>have</em> done is irrelevant, but I'll post it anyway.</p>
  <ol>
  <li><p>Ensure <code>Distributed Transaction Coordinator</code> service is running on both machies:</p>
  <p><img src="https://i.stack.imgur.com/l8iss.png" alt="enter image description here" /></p>
  <p><img src="https://i.stack.imgur.com/yx3Pm.png" alt="enter image description here" /></p>
  </li>
  <li><p>Disable all MSDTC security on both machines:</p>
  <p><a href="https://i.stack.imgur.com/7iJ7X.png" rel="noreferrer"><img src="https://i.stack.imgur.com/7iJ7X.png" alt="enter image description here" /></a></p>
  <p><img src="https://i.stack.imgur.com/SFTZn.png" alt="enter image description here" /></p>
  </li>
  <li><p>Turn on random options on the linked server:</p>
  </li>
  </ol>
  <p><a href="https://i.stack.imgur.com/M22Wa.png" rel="noreferrer"><img src="https://i.stack.imgur.com/M22Wa.png" alt="enter image description here" /></a></p>
  <ol start="4">
  <li><p>Cursed and swore.</p>
  </li>
  <li><p>Smashed things.</p>
  </li>
  <li><p>Checked that a <code>SELECT</code> can use the <em>linked server</em>:</p>
  <pre><code>    SELECT * FROM ASILive.CustomerManagementSystem.dbo.Users
      ....
  
      (763 row(s) affected)
  </code></pre>
  </li>
  <li><p><a href="http://www.sqlservercentral.com/Forums/Topic241855-5-2.aspx" rel="noreferrer">Checked that client server can <code>ping</code> the remote server</a>:</p>
  <pre><code>     C:\Documents and Settings\avatar&gt;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&lt;1ms TTL=128
       Reply from 10.0.0.40: bytes=32 time&lt;1ms TTL=128
       Reply from 10.0.0.40: bytes=32 time&lt;1ms TTL=128
       Reply from 10.0.0.40: bytes=32 time&lt;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
  </code></pre>
  </li>
  <li><p>Checked that the remote server can commnicate back, by name, to the initiating server:</p>
  <pre><code>     C:\Documents and Settings\avatar&gt;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&lt;1ms TTL=128
       Reply from 10.0.0.22: bytes=32 time&lt;1ms TTL=128
       Reply from 10.0.0.22: bytes=32 time&lt;1ms TTL=128
       Reply from 10.0.0.22: bytes=32 time&lt;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
  </code></pre>
  </li>
  <li><p><a href="http://support.microsoft.com/kb/306212" rel="noreferrer">Checked that <code>@@SERVERNAME</code> matches the server name on both servers</a>:</p>
  <pre><code>   SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')
  
     -------------  -------------
     ASITESTSERVER  ASITESTSERVER
  </code></pre>
  <p>and</p>
  <pre><code>   SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')
  
     ----------  ----------
     ASIGROBTEST  ASIGROBTEST
  </code></pre>
  </li>
  <li><p>Screamed</p>
  </li>
  <li><p><a href="http://support.microsoft.com/kb/306212" rel="noreferrer">Issued <code>SET XACT_ABORT ON</code> before issuing my query</a>:</p>
  <pre><code>SET XACT_ABORT ON
  GO
  BEGIN DISTRIBUTED TRANSACTION
  SELECT TOP 1 * FROM Sessions
  </code></pre>
  </li>
  <li><p><a href="http://support.microsoft.com/kb/306212" rel="noreferrer">Granted <code>Everyone</code> <code>Full Control</code> to</a>:</p>
  <pre><code>HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer
  </code></pre>
  <p>on both servers.</p>
  </li>
  </ol>