I’m facing DTC issues using SQL Link Servers, but first, I’ve got a couple of questions.
1) Is it in any way possible to use DTC authentication across two individual workgroup machines (both SQL servers being in a workgroup or one being in a workgroup)?
2) If yes, which credentials is it using (can i specify credentials)? If not, am I only left with the “no authentication” option?
3) In general, authentication or not, is the flow to the linked server encrypted? Meaning, if I run a DTC query across a Linked Server (over the WAN), are the results sent back in plain text?
4) Is it possible to configure DTC to only listen on a specific interface/IP (much likes the ServerTcpPort value to specify a port)
5)Im vaguely familiar with SQL, im just trying to get the network side to work. Besides Linked Servers and DTC (whatever its reason is), are there any other options I can pass along to the SQL folks?
So I have a multi-homed SQL 2012 box with a public and private interface. I can perform a DTC query on the private Lan, but I can’t connect to it (run a DTC query) on the public IP, or to the private IP when coming across a VPN connections. Im getting a 7391 error. I verified the following:
-DTC is set to No Authentication on all ends.
-Its not a DTC configuration issues or I wouldn’t be able to perform the DTC query even across the private Lan.
-Its not a firewall issue. In fact, from an IP perspective, I can verify (netstat) that it is indeed establishing a connection to DTC. I have configured DTC to use a static port.
-I configured the firewall to allow any/any for the remote SQL server. In fact, I -for testing purposes- even disabled the firewall all together. Likewise, when establishing a VPN connection (PPTP), im completely not firewalled and connect to the private IP.
-I confirmed on all ends that the NetworkService users has full access to C:\Windows\System32\Msdtc\MSDTC.Log.
-Im getting a success message when testing the LinkServer connection.
-I have no issues performing a regular query (select from LinkSrvrName.DBName.dbo.Table), it only failed when using “BEGIN DISTRIBUTED TRANSACTION”
-I set the "TurnOffRpcSecurity" reg value to 1
-I ran "SET XACT_ABORT ON"
Im getting the following error message:
OLE DB provider "SQLNCLI10" for linked server "ServerName" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server " ServerName " was unable to begin a distributed transaction.
- Edited by JoeNYC Friday, April 25, 2014 12:24 AM
But i've verified the firewall countless times. I've configured a static DTC port on both ends and i could telnet into the IP:port from both ends. I could also see the established connection on both ends. i even tested it by disabling the firewall on both ends. DTCPing shows the error below, but im getting this error even from the one internal server from which it IS working. Im out of ideas....
Error(0x6D9) at dtcping.cpp @303
-->RPC pinging exception
-->1753(There are no more endpoints available from the endpoint mapper.)
RPC test failed