SQLNET and firewall

SQLNET and firewall(n19)

I am having a problem with Oracle 7.3.4 for NT that may be related to
our firewalls but I'm not sure. Out listener is setup to listen on
port 1521 and we can do a SQL Net loopback without any problems from
the actual host itself(HOST5) and from another host in the same
subnet(HOST7). We know that Oracle is up and we know that SQL Net is
functional.

What we did then, from outside the firewall, we tested to see if we
could get to Host5 using TELNET HOST5 1521 and got through no problem.
So then we tested our client executable(SQL*Plus) by pointing it at a
database on the client side of the firewall and it was able to
connect.

Here's a description of what's between our client and the Oracle
Server(HOST5) we want to access. The client is on a subnet(call it
subnet A) which has a firewall protecting it and then we have HOST5 on
another subnet protected by its own firewall. Both firewalls were
configured to allow communication between the two subnets on port
1521. And we did verify this with telnet and by checking each
firewall's log entries.

Here's what our TNSNAMES.ORA file looks like on the client:

orcl.world =
(DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (COMMUNITY = tcp.world)
          (PROTOCOL = TCP)
          (Host = host5)
          (Port = 1521)
          )
      )
    (CONNECT_DATA = (SID = ORCL)
    )
)

Here's what our listener.ora file looks like on Host5:

################
# Filename......: listener.ora
# Node..........: local.world
# Date..........: 24-MAY-94 13:23:20
################
LISTENER =
(ADDRESS_LIST =
        (ADDRESS=
          (PROTOCOL= IPC)
          (KEY= oracle.world)
        )
        (ADDRESS=
          (PROTOCOL= IPC)
          (KEY= ORCL)
        )
        (ADDRESS=
          (COMMUNITY= NMP.world)
          (PROTOCOL= NMP)
          (SERVER= HOST5)
          (PIPE= ORAPIPE)
        )
        (ADDRESS=
          (COMMUNITY= TCP.world)
          (Host = host5)
          (PROTOCOL= TCP)
          (Port= 1521)
        )
        (ADDRESS=
          (COMMUNITY= TCP.world)
          (Host = 127.0.0.1)
          (PROTOCOL= TCP)
          (Port= 1521)
        )
        (ADDRESS=
          (COMMUNITY= TCP.world)
          (Host = host5)
          (PROTOCOL= TCP)
          (Port= 1526)
        )
)
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = ORCL)
    )
)
PASSWORDS_LISTENER = (oracle)

The error that we receive while trying to connect using SQL*Plus on the client is:

ERROR: ORA-12203: TNS:unable to connect to destination

If you have had similar problems getting sqlnet packets through a dual firewall
configuration...

Ans1:

I think you will find that the problem is that the port that Oracle redirects client
to is not defined as opened within your firewall.

When the client makes the initial connection, the message is sent though on Port 1521,
but once the connection has been received by the Listener, a shadow process is started
to listen on another port (random unused port) and a message is sent back to the
client stating which port the client should subsequently communicate on. The client
then tries to establish a connection on this port number, and your firewall is
rejecting the connection.

To the best of my knowledge, there is no way to tell Oracle which ports the listener
should allow connections on, so there is no way to open these ports up.

Ans2:

I am also trying to connect thru firewalls. I believe it *is* possible, but
I can't figure out how to configure the Listener properly.

Oracle's whitepaper, SQL*Net and Firewalls, indicates that in certain
circumstances it *is* possible to configure the Listener to create shadow
processes on a single port. Specifically, on page 3, it says "When the IP
port number of the SQL*Net connection can be determined in advance, such as
1521, then connection can be permitted with some degree of security.
Systems running multi-threaded servers, pre-spawned servers or ones with
architectures that do not support IP port sharing, require dynamic port
allocation which tends to prevent connections."

So, Dan, as long as you don't fall under any of the exemptions (MTS,
pre-spawn or no port-sharing) this should be possible.

Page 6 of the whitepaper describes the connection sequence. "Depending on
the operating system and TCP/IP protocl implementaiton, one of the following
procedures is performed. 1) The listener bequeaths the client conection to
the spawned server, effectively sharing the listener's IP port 1521.
Wherever possible, the listener bequeaths the connection instead of
redirecting it. 2) The Server performs a wild-card listen to obtain a
unique IP port number from the operating system and communicates the port
number allocated to the listner process. The listener then issues a
redirect, containing the wild-card address, to the client and drops the
conneciton. The client then calls the dedicated server process directly
using the wild-card port number provided in the redirect message."

Obviously, we'd prefer option 1 to occur. The question is, how can we
ensure that this happens?

We're running Oracle for Workgroups 7.3 on NT 4.0, so I don't think we fall
under any of the "exemptions" listed in the first quote-- Dan, you should
check and make sure you're OK here.

Do we need to do anything special to the listener to encourage it to use
port 1521?

Also, is there a way to "trace" the listener to see how it's handling the
connection?

Hosted by www.Geocities.ws