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!
How to communicate with Linked Server encryption
I'm using SQL SERVER's Linked Server to link between databases. But there is a security problem with this method. So is there a way to encrypt the communication section when accessing other databases through Linked Server and importing data? Please let me know if you have any good method or experience applying it Thank you.
