Doc ID:  Note:125021.1 
Subject:  Oracle Connectivity with Firewalls 
Type:  BULLETIN 
Status:  PUBLISHED 
Content Type:  TEXT/PLAIN 
Creation Date:  21-NOV-2000 
Last Revision Date:  24-MAY-2001 
 

PURPOSE
-------

This will explain the Oracle connection process with firewalls
and port redirection.

 
SCOPE & APPLICATION
-------------------

This document is intended to explain Oracle connectivity to all
non networking users.  The document will explain how the connection
works, how the port is redirected, how the connection is blocked, and
how to correct the problem.


Oracle Connection and Firewalls
-------------------------------
 
When the Oracle client makes a sqlplus connection to the database
(sqlplus userid/password@alias), it will compare the alias name you
supplied in the sqlplus line and look for a match in the tnsnames.ora
file or names server.  Once it obtains the address for the database
server, a connetion attempt is made to the server from the client.
The listener is contacted on the database server and port redirection
can take place depending on the platform, configuration of the init<sid>.ora
file and/or the Oracle product being used.  The OS will obtain a free port
from the OS and send back to the client via the listener the new port
assignment.  The client will then try to connect to the database on a
new port.

A remote Oracle client making a connection to an Oracle database can
fail if there is a firewall installed between the client and the server
if there is port redirection.  The firewall will block the port when
the Oracle client connects to the database and can fail with the client
receiving Oracle error ora-12203 or ora-12535.   The Client connection
failure is due to port redirection from the Windows operating system.  
Port redirection requires the Client to connect to the database using
a different port than originally configured in the configuration ora files.
Oracle MTS on Unix platforms, (without specifying the address with the
ports in the init ora file), Oracle SSL, and NT platforms will cause port
redirection.

A level 16 client trace file can verify if the problem is a firewall 
issue.  In the sqlnet.ora file on the client add the followng lines:

trace_level_client  =  16
trace_file_client = client
trace_directory_client = < a valid directory and path > ie:  c:\temp

Save the changes to the sqlnet.ora file and try connecting with sqlplus
to force the error.  This will create the trace file.  Here are several
excerts from a level 16-trace file of what to look for.

The initial packets sent to the listener on port 1521 in trace file.

niotns: Calling address: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
(HOST=server1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=v815.world)
(CID=(PROGRAM=D:\V815\BIN\SQLPLUSW.EXE)(HOST=server1)(USER=system))))
nladget: entry
nladget: exit
nscall: entry
nscall: connecting...
nsc2addr: entry
nttbnd2addr: entry
nttbnd2addr: port resolved to 1521
 

The received packet from the listener telling the client to use 1729 port.

nscon: recving a packet
nsprecv: entry
nsbal: entry
nsbgetfl: entry
nsbgetfl: normal exit
nsmal: entry
nsmal: 44 bytes at 0xb892d0
nsmal: normal exit
nsbal: normal exit
nsprecv: reading from transport...
nttrd: entry
nttrd: socket 232 had bytes read=64
nttrd: exit
nsprecv: 64 bytes from transport
nsprecv: tlen=64, plen=64, type=5
nsprecv: packet dump
nsprecv:00 40 00 00 05 00 00 00  |.@......|
nsprecv:00 36 28 41 44 44 52 45  |.6(ADDRE|
nsprecv:53 53 3D 28 50 52 4F 54  |SS=(PROT|
nsprecv:4F 43 4F 4C 3D 74 63 70  |OCOL=tcp|
nsprecv:29 28 48 4F 53 54 3D 31  |)(HOST=1|
nsprecv:33 38 2E 32 2E 32 31 33  |38.2.213|
nsprecv:2E 36 31 29 28 50 4F 52  |.61)(POR|
nsprecv:54 3D 31 37 32 39 29 29  |T=1729))|    <- port change
nsprecv: normal exit
nscon: got NSPTRD packet
nscon: got 54 bytes connect data
nscon: exit (0)

The client resolving the connection to port 1729.

nscall: connecting...
nsc2addr: entry
nttbnd2addr: entry
nttbnd2addr: port resolved to 1729
nttbnd2addr: using host IP address: 138.2.213.61
nttbnd2addr: exit
nsc2addr: normal exit

Edit the trace file and you can see the send packets sent from the client
on port 1521 (or your port if different) to the listener.  There will be
receive packets packets returned from the server to the client reflecting
a new port assignment.  Then the client will send packets again from the
client only this time to a different port.  The connection will then fail
at this point in the trace file.

The port that is assigned to the client is randomly chosen by the
operating system and can't be modified.  It can be any free port available
that the server determines is not is use by any other software or hardware.

Once it is determined that the problem is the firewall causing the
connection to fail, the next step is to select a solution to resolve
the issue

Being that Oracle is working correctly, and the firewall is working
correctly, there are several solutions to correct the problem to
allow the clients to connect to the database.


Solution: Firewall Vendor
The first solution is to contact the firewall vendor and see if they have
an upgrade to allow for oracle connectivity with OS port redirection.  If
the firewall software can be upgraded, it is the best solution to follow.


Solution: Connection Manager
The second solution is used for Oracle net8 versions and above, and
requires setting up connection manager (cman) to allow the clients to
connect through a firewall (note:2077721.6 explains cman in more
detail with configuration examples).  Connection Manager is an executable
that can be run from the bin directory, which allows clients to connect
when a firewall is in place between the client and the server.
Connection Manager is similar to a listener.  It reads a cman.ora file,
which contains an address that Connection Manager listens on for incoming
connections, usually default ports of 1610 or 1630.  Connection Manager
starts similar to the listener and will enter a listening state.  The 
Oracle client needs to be running net8 or above, and will need to have 
the following entered into the tnsnames.ora file.

cmantest =
   (description =
     (address_list =
       (address =			<- first address is to the cman		
         (protocol=tcp)
         (host=hostname or ip of cman)
         (port=1610)
       )
       (address=			<- second address is to listener
         (protocol=tcp)
         (host=hostname or ip of listener)
         (port=1521)
       )
     )
     (connect_data = (sid =  sidname))
     (source_route = yes)		<-This tells the client that it is
    )					  using cman and it must take the
                                          first two addresses listed.

 
When the client contacts the connection manager, cman will look in 
cache for the second address the client brought with it.  The second
address will point to the host machine where the listener is running.
Cman will then use that address to direct the client to the listener
and then the connection to the database will be made.

There are many documents on metalink, which will explain connection
manager and configuration in more detail.  This note is to explain the
connection process with firewalls, and not to go into detail with 
connection manager.


Solution: Use_Shared_Socket
A third solution for NT servers is to add the use_shared_socket = true
into the registry (see 124140.1).  This will allow the OS to share port
1521 and clients will then stay on 1521 when connecting to the database 
and will not be port redirected.  A downfall of this option is all connetions
will stay on the listener port and if the listener is stopped or
restarted all the connections will be severed from the database.
----------------------------------------------------------------------
By default on Windows NT and Windows 2000 Port Redirection is used, but it can 
be changed to Port Sharing with a registry key or environment variable.

In Windows NT port sharing can be activated by setting 

USE_SHARED_SOCKET = TRUE 

in the system environment (Control Panel, SYSTEM environment).

In Windows 2000 this is done also by the SYSTEM button in the control panel, 
but environment variables are specified and found by clicking the "
Advanced" button.

The parameter can also be set within the WINNT registry under

\\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE (Releases 8.0) or
\\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME<#> (Release 8i )

Both methods require a shutdown/startup of the NT server

See following document for more details:
<Note:124140.1>
How to enable USE_SHARED_SOCKET on WINNT and 
Windows 2000 
-----------------------------------------------------------------------

Unix
Unix Platforms can have problems connecting to a database through a
firewall if they have implemented Multi Threaded Server (MTS).  MTS
dispatchers will redirect connection ports like NT Platform does as
mentioned above. 

Solution: Setting MTS ports
A work around for this is to specify the port in the mts parameters of
the init.ora file (note:1016349.102).  This will allow the dispatcher
to use the port specified and will not select a port a random.  Then make 
sure the port is open on the firewall.  The following example will show 
the ports set to 2450 and 3125.  Please set these parameters according to
your individual systems.  This solution will also work with NT.

Example
mts_dispatchers="(address=(protocol=tcp)(host=hostname)
                 (port=2450))(dispatchers=1)"
mts_dispatchers="(address=(protocol=tcp)(host=hostname)
                 (port=3125))(dispatchers=1)"


SSL
Using SSL will cause Port redirection.  The work around is to select
and set the ports using MTS in the init.ora, or by setting the Ports
with Connection Manager in the cman.ora file.



RELATED DOCUMENTS
-----------------
note:1016349.102
note:66382.1
note:124140.1