Two Problems With DTC

user_13701750 0
Viewed 1457

I am trying to establish a distributed transaction between two SQL Servers. My task is to get some data from server A, transform them, save on server B, and as a final step to update server A.

When I executed this T-SQL statement

BEGIN DISTRIBUTED TRANSACTION TestTran
SELECT
*
FROM dbo.test

I got the error:

OLE DB provider "MSOLEDBSQL" for linked server "xx.xx.xx.xx" returned message "No transaction is active.". Msg 7391, Level 16, State 2, Line 2

The first server (server A) is SQL Server 2008 R2. Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64)

And the second server is Microsoft SQL Server 2019 (RTM-GDR) (KB4517790) - 15.0.2070.41 (X64) Oct 28 2019 19:56:59 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 17763: )

MS DTC is configured on both servers as shown in the image below

DTC configuration

Both firewall are configured to allow DTC, as shown in the image below Firewall Configuration

I was no sure how to use DTCPing because DTCPing just allows NETBIOS names. I put the servers IP address in the hosts file, but DTCPing returns an error, shown in the image below. DTCPing error

And that is just the beginning of my difficulties.

My second task is to establish a distributed transaction between server A ( the same as shown above ) and another server on which I have very limited permission. Let's name that server - server C.

When I tried to establish a distributed transaction I got the following error.

OLE DB provider "SQLNCLI10" for linked server "XXXX" returned message "The partner transaction manager has disabled its support for remote/network transactions.".

It seems to me that server C is not properly configured and that I need to contact a local administrator.

I have to be more specific when I call a local administrator.

So, to wrap up. I have two difficulties with the distributed transactions.

  1. No transaction is active is an error message.

  2. And the second error message is the partner transaction manager has disabled its support for the distributed transactions.

What seems to be the trouble? Thanks in advance!

msdtc transactions sql-server
1 Answers

DTC, it would appear, hates using IP addresses for Linked Servers. Your DTCPing note above, about it requiring the NetBIOS name and how it would not accept an IP address, gave me the hint I needed to fix my own challenge with this.

Consider having 2 servers,

  • In server 1 (call it 10.0.0.1 - srvServerA)
  • In server 2 (call it 192.168.10.1 - srvServerB)

Both on different networks, connected via VPN, no DNS between the two.

To fix this issue:

  • On server 1, add a hosts entry that points to the server 2's name.
  • On server 2, add a hosts entry that points to the server 1's name.
  • Re add your linked servers on both servers, using the NAME, and not the IP, and suddenly DTC will stop moaning.
user_11474490
Related