TCP port used by Oracle connections
No Wonder
Is there any way to specify the range of TCP ports to be used by Oracle
DB connections?? As there's a firewall between the client and the DB
server, the network admin has opened the port for listener (1521) but
he's asking whether certain port range can be specified for Oracle
connection.
Thanks,
Johnny
Sent via Deja.com
http://www.deja.com/
David Fitzjarrell
In a word, no.
Are you having to deal with port redirection? If you're on NT, or have
MTS enabled on UNIX you can experience this. Port redirection assigns
a new port number, different than the assigned port number, to the
incoming connection. Connection Manager can help with this, as well as
a configuration modification to the MTS entries in the init<SID>.ora
file. From Metalink:
Doc ID: Note:125021.1
Subject: Oracle Connectivity with Firewalls
Type: BULLETIN
Status: REVIEWED
Content Type: TEXT/PLAIN
Creation Date: 21-NOV-2000
Last Revision Date: 30-NOV-2000
Language: USAENG
*************************************************************
This article is being delivered in Draft form and may contain
errors. Please use the MetaLink "Feedback" button to advise
Oracle of any issues related to this article.
*************************************************************
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 connection 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
excerpts 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 (bulletin 2067721.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.
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 (bulletin 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
-----------------
2067721.6
2064550.102
1016349.102
66382.1
124140.1
Related bulletins may be accessed on Metalink.
I hope this helps.
--
David Fitzjarrell
Oracle Certified DBA
Van Messner
different ports. You'll need one set of parameters for each port, so doing
more than a few would be a drag. But if you just want, say 1521-1526, you'd
only need 6 sets.
Van
"David Fitzjarrell" <ora...@aol.com> wrote in message
news:956oe3$77i$1...@nnrp1.deja.com...
Nuno Souto
wrote:
>file. From Metalink:
Interesting that nobody even at Metalink has clicked into the real
problem that SQL*Net may cause with firewalls. I'll try to explain
with the example given:
>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
Note the client has refered to the server correctly using a DNS host
name: HOST=server1.
The firewall won't block this if it is setup correctly.
Now watch what happens:
>
>
>The received packet from the listener telling the client to use 1729
>port.
>snip...
>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)
Not only has the listener responded with an unusual port, but it also
has passed back an IP number address, not a host name! And that IP
address is obviously from the "inside" of the firewall, ie, an invalid
IP address to use for the outside world!
Now what does the client do?
Watch:
>
>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
>
*Boom*! The firewall will do its job and block the connection, because
nobody from the outside world should know there is a 138.2.213.61
address inside the firewall! They should always talk DNS hostnames
inside their little packets...
>
>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.
>
I don't think this will ever help. I've never seen it work anywhere,
because the problem is not the port but the IP address being hardcoded
into the packet and then used by the client!
>
>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 (bulletin 2067721.6 explains cman in more
>detail with configuration examples). Connection Manager is an
That will be a better solution.
I first noticed this problem with a sniffer about 6 years ago with V7.
Looks like it's still there. And ORACLE is still recommending the "get
a patch for the firewall software" thing! It never will work while the
listener insists on passing the IP address instead of the host name
back to the client. It's got nothing to do with the port number,
although that is an additional problem.
FWIW...
Cheers
Nuno Souto
nso...@bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html
Niall Litchfield
news:3a780045.10970266@news-server...
> Interesting that nobody even at Metalink has clicked into the real
> problem that SQL*Net may cause with firewalls. I'll try to explain
> with the example given:
>
> >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))))
> Now what does the client do?
> Watch:
>
> >
> >The client resolving the connection to port 1729.
> >nttbnd2addr: port resolved to 1729
> >nttbnd2addr: using host IP address: 138.2.213.61
>
> *Boom*! The firewall will do its job and block the connection, because
> nobody from the outside world should know there is a 138.2.213.61
> address inside the firewall! They should always talk DNS hostnames
> inside their little packets...
>
> >
> >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.
> >
>
> I don't think this will ever help. I've never seen it work anywhere,
> because the problem is not the port but the IP address being hardcoded
> into the packet and then used by the client!
We run the Cisco secure firewall which does this job (Network Address
Translation NAT). This piece of hardware (and I'm sure it isn't the only
one) will read the packets for protocols it knows about and substitute a
translated IP address, dependent upon direction. At least that is what the
documentation says I havent actually tested.
> >
> >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 (bulletin 2067721.6 explains cman in more
> >detail with configuration examples). Connection Manager is an
>
>
> That will be a better solution.
>
not an available solution for those of us running std edition though.
--
Niall Litchfield
Oracle DBA
Audit Commission UK