Forum Discussion

TIENHC's avatar
TIENHC
Copper Contributor
May 15, 2025

Linked Server Selection Query Fails with "MS DTC has stopped this transaction"

Hi everyone, 

I’m currently setting up a Linked Server from our head office (server A) SQL Server to a subsidiary unit (Server B) SQL Server. The Linked Server connection tests successfully, and I can log into server A from server B and can using query. However, when I try to execute a query using a 4-part name through Linked server, I encounter the following error:

Msg 8522, Level 16, State 3, Line 1 Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.

Environment Details:

Head Office (server A) SQL Server:

  • SQL Server 2008 R2 (already upgraded pack SP3)
  • Windows Server 2012
  • TLS 1.2 enabled
  • MS DTC service is turned on

Subsidiary (server B) SQL Server:

  • SQL Server 2016
  • Windows Server 2016 Standard (64-bit)
  • TLS 1.2 enabled
  • MS DTC service is turned on

Networking:

  • The B server connects via VPN to be on the same network as server A
  • Ping and Telnet tests (IP and port) from both sides work fine
  • SQL login from server A to server B(via IP and port) is successful

USING PROVIDER: i have try to using SQLNCLI11 and MSOLEDBSQL, but both till error DTC.

Linked Server test connection: Success

Update, insert query: it's ok, but when i try selection query, i got error DTC has stop this transaction.

example:

SELECT * FROM [LinkedServerName].[DatabaseName].[dbo].table

Has anyone faced a similar issue? Could this be a MS DTC configuration mismatch or network security/firewall/DTC port range issue?

Any guidance on how to properly configure MS DTC across different servers/domains/VPNs would be highly appreciated.

Thanks in advance!

 

4 Replies

  • Mike_Lemay's avatar
    Mike_Lemay
    Copper Contributor

    You might also want to turn off "Promotion of Distributed Transactions"

     

  • Mike_Lemay's avatar
    Mike_Lemay
    Copper Contributor

    Try using Openquery.  Besides being less problematic with syntax it also issues the command remotely so it can be faster than direct linked server queries because it sends the entire query to the remote server for execution. This means the remote server handles parsing, optimizing, and executing the query, reducing the workload on the local server.

  • TIENHC's avatar
    TIENHC
    Copper Contributor

    execute me, could you guys give me solution for that error?

    • Neb12's avatar
      Neb12
      Copper Contributor

      You might need to turn on AdHoc distributed queries: 

      EXEC sp_configure 'show advanced options', 1;
      RECONFIGURE;
      EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
      RECONFIGURE;

Resources