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

TIENHC · May 15, 2025, 10:18am

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:

Blockquote 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!

Ifor · May 15, 2025, 4:34pm

I am not convinced about using Linked Servers remotely and even less convinced about the 4-part naming. You could try:

EXEC [master].dbo.sp_serveroption @server=N'YourRemoteServer'
  ,@optname=N'remote proc transaction promotion'
  ,@optvalue=N'true';