• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 557
  • Last Modified:

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

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
aoviedo08
Asked:
aoviedo08
  • 5
  • 5
1 Solution
 
kuknoCommented:
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
 
aoviedo08Author Commented:
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
 
kuknoCommented:
does the firewall logs show any dropped or rejected packets (logging needs to be enabled for every rule!)?
0
The Firewall Audit Checklist

Preparing for a firewall audit today is almost impossible.
AlgoSec, together with some of the largest global organizations and auditors, has created a checklist to follow when preparing for your firewall audit. Simplify risk mitigation while staying compliant all of the time!

 
aoviedo08Author Commented:
No rejected packets, all packets accepted from originating server, but no packets detected  as a response from destination server.
0
 
kuknoCommented:
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
 
aoviedo08Author Commented:
[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
 
kuknoCommented:
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
 
aoviedo08Author Commented:
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
 
aoviedo08Author Commented:
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
 
kuknoCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The Lifecycle Approach to Managing Security Policy

Managing application connectivity and security policies can be achieved more effectively when following a framework that automates repeatable processes and ensures that the right activities are performed in the right order.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now