Re: sqlnet over Internet

S S Wan <sswan_at_hk.super.net> wrote in article <60vffa$g5f$1_at_tst.hk.super.net>...
> I once asked Oracle support this question and they replied that
> it's impossible across Firewall. They reason is that the listener
> though listens a well known port (1521?), the server process
> so forked listens on some random port number. It is hard to restrict
> traffic by random port number and allowing only 1521 simply doesn't
> work!
>
> If anyone has implemented such solution across firewall, please kindly
> share your experience with us.

IMHO Oracle support is talking bull. When you initiate a connection from a client, it connects to the listener. Usually it runs on the TCP port 1521 or 1526. The listener forks a process that inherits the tcp socket and serves as the communication agent between the client and the database instance. At no time does it or the listener initiate a new connection on a "random" port to the client.

It's quite simple to test on a Win95 client. Boot the PC, load a DOS shell and run the command NETSTAT. It lists all TCP and UDP connections of the local IP protocol stack. Run SQL*Plus and open a connection to the database. You'll see that there's a single TCP entry listing your connection to the Oracle server. The local port will be a random number (which is ok as it reflect the socket handle I think) and the foreign address will be something like 192.231.254.96:1521 which indicates the server's ip address (or hostname) and port the client requested a connection on. Run now a lenghty SQL statement that returns a lot of data (e.g. select * from all_tab_columns). While SQL*Plus is scrolling and displaying the data, do a NETSTAT again. There will still be a single TCP connection from the local client to the Oracle server.

The problem with going thru a firewall with SQL*Net is when a proxy server is also used. This means that the user on the client must first supply the proxy server with a userid and password before it (the proxy) will allow the connection to the database server. Obviously, SQL*Net does not support it. We discussed this subject in the majordomo filewall list a month or so ago and someone mentioned that there is client software available that serves as an interface between the client Oracle app and the proxy server in order to establish a SQL*Net connection via the the proxy server.

regards,
Billy Received on Fri Oct 03 1997 - 00:00:00 CEST