0

I have 2 Windows servers EC2 located in different region. Both of them have public IP so they can communicate with each other.

In order to allow remote query, i created a linked server. Basically, they are in different region so i used their public IP as data source. 1st server IP: 13.xxx.xxx.xxx 2nd server IP: 52.xxx.xxx.xxx

I tested the connection and it was successful. Remote query also worked fine. For example, update table set number = 0 from [remote-server-IP].txtoc.dbo.xhead where code ='1234'

But if i use BEGIN TRANSACTION and COMMIT TRANSACTION like this BEGIN TRANSACTION update table set number = 0 from [remote-server-IP].txtoc.dbo.xhead where code ='1234' COMMIT TRANSACTION

i got error. OLE DB provider "SQLNCLI11" for linked server "linkedservername" returned message "No transaction is active.". Msg 7391, Level 16, State 2, Line 2 The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "linkedservername" was unable to begin a distributed transaction.

I tried many methods - Enable Distribute Transaction on both server from Component Service - Allow them work through Windows Firewall - Allow all traffic between them (setting in Security group and Windows Firewall)

But none of them worked.

Anyone can shed a light on this, please?

The One
  • 101
  • 5

1 Answers1

0

This question is old, and I stumbled across it and am only commenting, not an acceptable answer:

1) Public IPs for SQL are dangerous, you should keep that in mind. Between Regions you can do a route with a VPN at a minimum. 2) Make sure the DTC on the remote server allows remote connections - Start - dcomcnfg - Expand Component Services - Computers - My Computer - Distributed Transaction Coordinator - Local DTC (Properties on this) - Security - Enable Network DTC, Allow Remote Clients, Allow inbound, Allow outbound. 3) If that doesn't solve it try dtcping from Microsoft to troubleshoot further.

Steve Radich-BitShop.com
  • 573
  • 3
  • 7