Solved

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

Posted on 2008-10-06
11
540 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
Ready to trade in that old firewall?

Whether you need to trade-up to a shiny new Firebox or just ready to upgrade from whatever appliance you're using now, WatchGuard has the right appliance for you! Find your perfect Firebox today with appliance sizing tool!

 

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 500 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

615 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