Solved

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

Posted on 2008-10-06
11
520 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
  • 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now