Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to stablish distributed transactions to a remote server through a firewall?

Posted on 2008-10-06
11
Medium Priority
?
545 Views
Last Modified: 2013-11-16
We are trying to stablish a distributed transaction between Server A  on my LAN and  server B on my DMZ.

SERVER A (in LAN): WIndows 2003 Std SP2, 2 SQL Server Instances: 1 SQLServer 2000 Standard, 2 SQL Server 2005 Express. It contanis 2 linkservers. Link1: Points to Instance2005 on serverA, Link2: points to instance2005 on SERVER B.

SERVER B (in DMZ): Windows 2003 Std SP2, 1 Instance SQL Server 2005 Express.

In SERVER A:The instence 2000 executes  a stored prodecure that contains SQL Sentences (INSERT, SELECT AND DELETE) wich uses the link server mentioned above.
*********************************************************************************
SET XACT_ABORT ON          
BEGIN DISTRIBUTED TRAN
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
DELETE [link2].basededatosDestino.dbo.tablaDestino
INSERT INTO [link2].base de datosdestino.dbo.tabladestino
SELECT * FROM [link1].basededatosorigen.dbo.tablaorigen
*************************************************************************
This works fine if both SERVER A and SERVER B are on my LAN, once I transfer SERVER B to my DMZ i get the following error:
*****************************************************************************************************************************
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
******************************************************************************************************************************
My DMZ is behind a checkpoint Firewall, the ip segment on my LAN is Different to the one on my DMZ the Firewall does the routing.





NETDIAGRAM.doc
0
Comment
Question by:aoviedo08
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 10

Expert Comment

by:kukno
ID: 22652471
Hi,

DTC uses RPC with dynamic ports. This is generally a problem with firewall filters.

Microsoft offers a "solution" to the problem: http://support.microsoft.com/default.aspx?scid=kb;EN-US;250367

CheckPoint is able to interpret Microsoft RPC and "learn" the dynamic ports. However, you must know the RPC "UID" (see checkpoint RPC service object definiton). Actually I don't know the UID for distributed transaction, but you should be able to find that with Wireshark (sniffer).

Regards
Kurt
0
 

Author Comment

by:aoviedo08
ID: 22660884
We already tried the solution offered by Microsoft "http://support.microsoft.com/default.aspx?scid=kb;EN-US;250367" - In the Firewall we added a range of ports from 5000 to 5016  an it still does not work.

We keep getting the same error:
***************************************************************************************************************************
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
***************************************************************************************************************************
0
 
LVL 10

Expert Comment

by:kukno
ID: 22660930
does the firewall logs show any dropped or rejected packets (logging needs to be enabled for every rule!)?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:aoviedo08
ID: 22663413
No rejected packets, all packets accepted from originating server, but no packets detected  as a response from destination server.
0
 
LVL 10

Expert Comment

by:kukno
ID: 22663789
O.K. please run this command on the firewall.

fw monitor -e "(src=10.1.1.1  or dst=10.1.1.1) and (src=192.168.10.1 or dst=192.168.10.1),accept;"

Assumption: Server on Lan 10.1.1.1, Server on DMZ: 192.168.10.1 (please replace with your ip addresses).

If it's a failover cluster, run the command on the master. If it's a load balancing cluster, run it an all nodes, then post the output here.

I can think of several possible problems: NAT, Routing, Anti-Spoofing. We will see it in the dump.

Regards
Kurt
0
 

Author Comment

by:aoviedo08
ID: 22664757
[Expert@pf.2]# fw monitor -e "(src=10.1.1.1  or dst=10.1.1.1) and (src=192.168.10.1 or dst=192.168.10.1),accept;"
 monitor: getting filter (from command line)
 monitor: compiling
monitorfilter:
Compiled OK.
 monitor: loading
 monitor: monitoring (control-C to stop)
 monitor: caught sig 2
 monitor: unloading
[Expert@FW]#

What is sig 2 ??
0
 
LVL 10

Expert Comment

by:kukno
ID: 22664892
sig 2 is "terminal interrupt". Did you press "Ctrl-C"? Maybe to copy the content of the window? That will terminate the process.

BTW: You did NOT change the ip addresses to the ones of your setup! If you don't do that you will not see any packets!

Oh, after you have started "fw monitor" you need to trigger the problem by doing whatever you did to get the SQL error message.
0
 

Author Comment

by:aoviedo08
ID: 22669957
Yes, I pressed CTRL-C to stop the process because I thought that it would then give me the results. How long do I have to wait for the process to end correctly? I waited over 5 minutes

We started the monitor, then executed the SQL DTC to generate the error and then I stopped the process. We changed the IPs and used the real ones to execute the command, then I changed them back to the ones of your example to send the results back to you.

Everything worked fine, the only problem was that I ended the process too soon. How long does the monitoring take to give the results?
0
 

Author Comment

by:aoviedo08
ID: 22673880
These are 2 filer generated by the FW monitor.

We changed the real IPs with the ones from your example. 10.1.1.1 is the server on our LAN and 192.168.10.1 is the server on our DMZ.

Greetings.

Copy-of-MonitorFW2-Orig-.txt
Copy-Of-MonitorFW-Orig-.txt
0
 
LVL 10

Accepted Solution

by:
kukno earned 2000 total points
ID: 22674076
First: In both dumps I see SYN packets that make it just to phase (i) at the incoming interface (see below).

eth6:i[48]: 10.1.1.1 -> 192.168.10.1 (TCP) len=48 id=15557
TCP: 3912 -> 135 .S.... seq=766f3214 ack=00000000
eth6:i[48]: 10.1.1.1 -> 192.168.10.1 (TCP) len=48 id=15635
TCP: 3912 -> 135 .S.... seq=766f3214 ack=00000000
eth6:i[48]: 10.1.1.1 -> 192.168.10.1 (TCP) len=48 id=15762
TCP: 3917 -> 135 .S.... seq=4d2b5494 ack=00000000
eth6:i[48]: 10.1.1.1 -> 192.168.10.1 (TCP) len=48 id=15779
TCP: 3917 -> 135 .S.... seq=4d2b5494 ack=00000000

Explanation: In CheckPoint a packet traverses a lot of internal "checkpoints". With fw monitor you should see the packet a the incoming interface in stage (i) and (I) and at the outgoing interface in stage (o) and (O). I your dump I can see the SYN packets just in stage (i) at the incoming interface eth6. That means that the packets are dropped by the firewall. I wonder, why you do not see any log entries for this. Did you look thoroughly at the logs? Did you allow TCP/135 from the LAN to the DMZ in you policy? That's the port needed for RPC to work!

Second: You seem to have a load balancing cluster in place (I see SYN packets on both machines). So, try to stop one cluster member and see what happens? A "misconfigured" cluster can cause similar problems. However in your case I suspect  a "simple" drop for whatever reason.

Third: What happens if you allow ANY service from the server on the LAN to the server in the DMZ?
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question