Protecting Oracle connections

Date: Mon, 21 May 2001 11:15:10 +0000
From: Grzegorz Cempla <grzegorz.cempla@linart.pl>
Subject: Re: Stunnel & oracle


Hi,

So I've tested connecting oracle with stunnel. And I must say it doesn't work
like we would like it to.

Oracle connections work like ftp. As far as I know Oracle server listener
negotiates separate port for sending queries/results and obviously there is
no way to predict this port and tunnel it. The only thing you can do is to
tunnel the listener connection. So if you have the client and server on one
network it should work fine (as far as I know Oracle implements some kind of
encryption to the active connections) but if you want to hide the server in
some kind of private network it will NOT work at all. Unfortunately that was
what I needed to do.



Date: Tue, 24 Sep 2002 12:08:28 +0300
From: eth <eth@finsiel.ro>
Subject: Re: multiple ports simultaneously?

mtrojnar@mirt.net wrote:

Adrian Dascalu tells
me that you *CAN* forward Oracle through stunnel. That's how it works in   
our app: we stunnel jdbc to an Oracle server which is behind a
firewall... the cheat code is: USE_SHARED_SOCKET; it's a string in
"hkey_local_machine/Oracle, All Homes"... wherever you find Oracle; this
will trick Oracle into switching from FTP-like to standard connection. 
Do not use it on NT SP2 or early; tested on NT SP6 and 2000 AS with
Oracle 8. Perhaps someone should update the examples page...






Date: Fri, 8 Nov 2002 12:48:48 +0100
From: "Peter de Groot" <pdgroot@robes.nl>
Subject: Re (message on www.stunnel.org/examples): Stunnel & oracle

I read the message about stunnel and Oracle on the stunnel.org website, and
noticed that it's not entirely accurate. Actually, the message explains why
it's not possible to stunnel Oracle Networking, while in fact I've used it a
number of times without any major problems.

According to the message, Oracle network traffic is not possible over
stunnel, as the Oracle listener allocates a random port for the connection
between the Oracle client and the database.
The Oracle listener on Windows servers usually listens on ports 1521 and
1526. When a connection is made from a client, the listener opens different
port, to which the client is allowed a dedicated connection. The listener
tells the client to connect to that port, and the client will use that port
for the rest of the connection to the database.

This is true for Oracle on Windows servers, not for Oracle on UNIX servers.
If an Oracle client connects to an Oracle listener on a UNIX host, the
connection with the database is established right away over that first
connection, no redirects to different ports will be made.

The Net8 'feature' on Windows was created because of bugs in the TCP/IP
implementation in earlier version of WindowsNT (prior to NT4SP3 if I'm
correct). The buggy TCP/IP implementation somehow didn't allow port sharing,
so connections hadd to be redirected to a dedicated port per client
connection. 

The redirection of Oracle traffic on Windows servers can be disabled in
WindowsNT4SP3 and above (including Windows2000), by entering the registry
entry 'USE_SHARED_SOCKET = TRUE', in the Windows registry under   
HKLM\Software\Oracle\. This will cause the listener to act the way it acts
on UNIX servers, allowing multiple connections on the default ports 1521
and/or 1526. You'll have to restart the TNSListener service after adding
USE_SHARED_SOCKET to the registry.

There could be a few reasons why you should consider not to implement the
USE_SHARED_SOCKET method.
First of all, if there are a lot of client connections to the database,
directing them all through the same port could cause some loss of
performance. You should be aware of this if your database has to handle more
that 50 active connections at a time.   
Second, it only works for Oracle 8 and higher database software. If you're
using Oracle7, consider upgrading.
This solution doesn't work in MTS (Multi Threaded Server) environments as
far as I know.


Now for the example part. This will assume that your Oracle server is
running on oracleserver.domain.tld, and that the listener is configured to
listen to ports 1521 and 1526. Stunnel client will be listening on ports
10001 and 10006, stunnel server will be listening to 11521 and 11526.

First, you'll have to adjust your tnsnames.ora on the client machine, to
make the Oracle client connect to localhost instead of the Oracle server.
You'll find the tnsnames.ora in %ORACLE_HOME%\network\admin\ or
%ORACLE_HOME%\net80\admin\, or in the directory the TNS_ADMIN environment
variable or registry value points at.

### Example tnsnames.ora before change ###
ORCL.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver.domain.tld)(PORT =
1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver.domain.tld)(PORT =   
1526))
    )
    (CONNECT_DATA =
      (SID = ORCL)
    )
  )

### Example tnsnames.ora for use with stunnel ###
ORCL.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 10001))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 10006))
    )
    (CONNECT_DATA =
      (SID = ORCL)
    )
  )

###################################################

The stunnel configuration, using stunnel-4 configuration files:  

### client config file for stunnel-4 ###
client=yes

[oracle1521]
accept=127.0.0.1:10001
connect=oracleserver.domain.tld:11521

[oracle1526]
accept=127.0.0.1:10006
connect=oracleserver.domain.tld:11526
###################################################

### server config file for stunnel-4, running on oracleserver.domain.tld ###
client=no
cert=c:\stunnel\stunnel.pem

[oracle1521]
accept=11521
connect=1521

[oracle1526]
accept=11526
connect=1526
########################################