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!
MSDTC Recommendations on SQL Failover Cluster
First published on MSDN on Jan 15, 2014 The purpose of the following FAQ (Frequently Asked Questions) is to address common questions with MSDTC (Microsoft Distributed Transaction Coordinator) when used with SQL Server Failover Clustered instances to include current recommendations and best practices.Not-Supported: AGs With DTC/Cross-Database Transactions
First published on MSDN on Jan 06, 2014 When the topic of cross-database and/or DTC transaction support, under an Availability Group, comes up the quick response is NOT SUPPORTED! This is a true statement and the conversation tends to then focus on – ‘but why'?’ In fact, some DBAs have tested various forms of these transaction types and not encountered errors.
