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?

  • Anyone has any idea? – The One Oct 10 '17 at 3:41

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.