RE: Oracle and SqlNet behind a firewall

From: Rusnak, George A. II <rusnakg_at_SDCL.LEE.ARMY.MIL>
Date: Fri, 21 Jul 2000 09:43:36 -0400
Message-Id: <10565.112640@fatcity.com>

We were having the same issues. The comments below are from an Oracle techie.
Hope this helps

The issue here is getting SQL*Net to connect through a firewall. If that works, then Designer will work. On NT by default, ports 1521 and/or 1526 are used for a connection, but a random port is selected for communication from the server back to the client. If you are on version 8.0.X, there is a specific parameter that can be set to keep communication on a specific port. Then, you could open the firewall for that port and have things work. The other option is to see if the firewall you are using specifically supports Oracle SQL*Net. Check with your firewall vendor for specifics. Excepts from
technical notes and documentation included below.

---

On Windows NT, when a connect request comes in to the listener, the listener spawns and Oracle thread. This thread is a listening thread, and is started on a wild-card address - meaning that the thread is listening for connections on the current I.P. address, and an unused port number given to the thread by the networking software. The Oracle thread will contact the listener using IPC and inform the listener of its listening address, connection load, and some other status information. The listener sends back to the client a REDIRECT address. This tells the client to reconnect to the newly spawned Oracle thread.
Since this Oracle thread is on a random port (a range of ports cannot be defined), the firewall will not let the connection through. The resulting error is usually a TNS-12203.

There are two ways to resolve this issue. The first way is to use a firewall that has a SQLNet proxy built into it. The way this works is that the SqlNet proxy starts another listening processes (usually on port 1610). This causes the firewall to act as a Multi Protocol Interchange. So, by using the tnsnav.ora file on the client, you connect to port 1610 (the firewall). The firewall passes the connection to the server. The server gives a redirect to the client. The client reconnects to the firewall proxy on port 1610, and the firewall passes the connection to the Oracle thread on the wild-card listening address. Here's what the connection flow would look like:

1.	connect to proxy and pass connection to listener
2.	send redirect to client
3.	connect to redirected address via the proxy
4.	oracle accepts the connection

	firewall
		                           ||
+------+ <--------2--------||-------2------  +---------+
	|client|                   ||                |listener |(port_21)
+------+ --------1------> proxy ----1------> +---------+
A   \                   /||\
	  |    \---------3-------/ || \-----3------> +---------+
			  |                        ||                |

oracle |(port=xxxx)
+--------------4---------||-------4------- +---------+ The second way to resolve this issue is to upgrade the server to 8.0.x and use the USE_SHARED_SOCKET parameter in the registry. With this method, it doesn't matter what kind of firewall you have. The syntax for this parameter is:
USE_SHARED_SOCKET = TRUE
Place the parameter in the registry under HKEY_LOCAL_MACHINE:Software:Oracle Restart Oracle and the listener for the parameter to take effect. Here's how USE_SHARED_SOCKET works. The listener binds and creates a socket on the address specified in the listener.ora file. On this socket, there is a LISTEN state active that is used by the listener. When a new connection comes in to the listener, the listener spawns an Oracle thread on the listening port (i.e. 1521). This happens over and over again so that you have a listener and several established connections using port 1521. Pictorially this scenario would look like this:

	+---<O>--------<O>----<O>---<O>--+
		               |                                |
	               |     This square represents     <O>
	               |     a listening socket for     |
	               |     port 1521.                 |
	<O>                              |
	               |     <O> = oracle thread        <O>
	               |     <L> = listener             |
		               |                                |
	+-<O>-------<L>--<O>------<O>----+

The operating system then does a poll() or a select() on the socket to test for any data. If any of the threads have data, a signal handler is used to contact the application and inform it of the new data. The disadvantage of USE_SHARED_SOCKET is that if the listener shuts down, all connections are disolved.
Finally, a very common question concerning the listener and port numbers is why different port numbers show up in the listener.log file. What you are seeing is the client's source port and client's source IP address. Here's how this relates to your firewall.
If I want to make a TCP connection to a server (let's say with TELNET), I need to create a socket. To create a socket, I need 4 pieces of information: A source IP and port, and a destination IP and port. So let's use TELNET as an example (the listening port for the TELNET process is 23 on the server):

	source       destination
          +-----------+---------------+
    IP    |138.2.12.8 |185.45.67.53   |
          +-----------+---------------+
  port    |    xx     |     23        |
          +-----------+---------------+

Notice I have labeled the source port as 'xx'. What happens is that the networking software on the client chooses at random, or in sequential order, a valid port (between 1024 and 65535) so the client can send and receive data. This is what you are seeing in the listener.log file. Will the be a problem with the firewall? No. The firewall will restrict incoming connections, but will freely let any connection on any port out (which is okay). Here's what it might look like:

	Firewall
            <-------------||---------\
            <-------------||---------\\
[CLIENT]----------------->||          \---[SERVER]

Received on Fri Jul 21 2000 - 08:43:36 CDT