How to connect to Oacle server from client outside the firewall using ODBC?

Printable View

12-20-2001, 10:07 AM
elaine3839

Hi,

I have a question regarding to how to connect to Oracle server(which could be on any OS and inside a firewall) from a remote client(which is outside the firewall and most likely a windows client) using ODBC?

Actually, all we need to do is running reports from client, which need to connect to the oracle server using Oracle ODBC driver.

I searched the forum and read a couple of articles on Metalink, but all of them are talking about how to connect to Oracle server from Oracle remote client using Net*8 instead of ODBC. And the solutions range from set use_shared_socket =TRUE to use CMAN.

So
1. shall I go with the same apporach with ODBC connection?
2. If Oracle server is on a WINDOWS box, shall I set use_shared_sockets = TRUE instead of trying CMAN?
3. How does ODBC handle the remote connection? Does it also use port 1521 by default?

Thanks a lot!
12-20-2001, 10:10 AM
TimHall

The only way I've managed to do this is by getting the firewall administrator to accept requests from the clients IP address. This assumes your client process is running on a machine with a fixed IP address, and is secure????
12-20-2001, 10:54 AM
elaine3839

Yeah, that could work, but ...

is there any better way avaiable? Since under certain circumstances, the clients IP address may not be a fixed one.

Also, will this work for ODBC Connection as well? i.e. Is ODBC connect the same way to the Oracle server as Net8?

Have you ever use connection manager(CMAN) or set USE_SHARED_SOCKET = TRUE?

Thanks!
12-20-2001, 11:46 AM
TimHall

I've not tried your last suggestion. The firewall may also be set up to block SSL. I think you really need to speak to an expert. Unfortunatelym that's not me :)

You can usually configure firewalls to block/allow individual protocols (TCPIP, UDP etc.) for a given IP address.

If you can't use a specific IP address some firewalls will let you provide username/password authentication.

I really am at the edge of my firewall knowledge here! If anyone knows more please help!
12-20-2001, 12:02 PM
marist89

Some firewall software (gauntlet, I think is one) comes with sql*net proxy software for just this reason. You point your tnsnames.ora to the firewall as your oracle server and the firewall knows how to forward your request to the server over NET8. Basically, it does a similar job to Connection manager, but in a more secure way.
12-20-2001, 04:25 PM
jmodic

If multithreaded configuration of the database is acceptable for your application, then you can "fix" the port number through which connection will be established. In other words, database will establish the connection with the client through the same port through which the client contacted the listener. So you don't realy need a CMAN nor the use_shared_socket =TRUE.

An example of MTS_DISPATCHERS parameter with "fixed" port 1521 in your init.ora would be something like:

mts_dispatchers="(address=(protocol=tcp)(host=hostname)(port=1521))(dispatchers=1)"
12-20-2001, 04:46 PM
marist89

Quote:

Originally posted by elaine3839

3. How does ODBC handle the remote connection? Does it also use port 1521 by default?

ODBC rides on top of Net8.
12-21-2001, 08:28 AM
TimHall

Quote:

Originally posted by jmodic
If multithreaded configuration of the database is acceptable for your application, then you can "fix" the port number through which connection will be established. In other words, database will establish the connection with the client through the same port through which the client contacted the listener. So you don't realy need a CMAN nor the use_shared_socket =TRUE.

An example of MTS_DISPATCHERS parameter with "fixed" port 1521 in your init.ora would be something like:

mts_dispatchers="(address=(protocol=tcp)(host=hostname)(port=1521))(dispatchers=1)"

I'm not firewall-tastic but won't this open up a whole in the firewall for that port regardless of who the client is? This is no more secure than accepting any TCP request through that port.

Assuming I'm correct, this doesn't affect the need for a static client IP address or password authentication on the firewall.

I'm way out of my depth now :)
12-21-2001, 09:25 AM
jmodic

The firewall will still have to be configured to accept calls from client's fixed address (meaning that client must have fixed IP, not the one obtained from DHCP).

The problem with firewalls and Oracle database on some OS (prticulary on multthreaded OS like WinNT) is that you can't predict the port on which comunication will be established. Take the following example:

- Your listener is set up to listen on port 1521
- Firewall is configured to accept calls from your client's IP on port 1521
1) Your clients tries to established connection with the database by sending request to port 1521 of the database server
2) Firewall let the call through (it is configured to do so) and listener intercepts the call
3) Listener transfers the request to the database
4) After database checks you are authorised to connect to the database it sends the responce back directly to your client. But database chooses some new port on which the session will be established, let's say it is port 2020. So your client gets the message from your database, saying: You are welcome to connect to the database, we will communicate on port 2020.
5.) Your client obeys the instructions from the database and sends a message to the database server, but this time on port 2020.
6.) This message gets rejected by the firewall, because it can accept only messages through port 1521, not through 2020.
7.) As a result, the database connection is not established.

By "fixing" dispatcher's port you just make sure the whole communication will be conducted only on this port, not through some arbitrary port choosen by the database. You are not opening any hole in the firewall.
12-21-2001, 11:17 AM
elaine3839

Thanks all, but the thing is ...

Since we are the software application vendor, we don't really know which OS the customer's Oracle servers are sit on, and we don't want to put a limitation over there. i.e. if they are using dedicated server mode, we don't want to force them change to MTS mode etc.

So I'd like to provide them generic solutions if I can. So far, can I draw a conclusion that:

To make a remote client connect to an Oracle server inside a firewall, your client needs to have a fixed IP address. The firewall should be configured only accept those specified IP addresses. Also, the port 1521 needs to be open on the firewall. Plus

1. If the Oracle server is on Windows Box (which is a multithread server), then you can always make it work by set USE_SHARED_SOCKET = TRUE in registry.

1.1 If their Oracle server is running in MTS mode, then they can simply "fix" the port number through which connection will be established by setting

mts_dispatchers="(address=(protocol=tcp)(host=hostname)(port=1521))(dispatchers=1)"

In this case, you don't have to set USE_SHARED_SOCKETS = TRUE. (is this statement correct?)

2. If the Oracle server is not on a multithreaded OS, then there is no additional requirement besides keep port 1521 open on firewall and configure firewall to only accept predefined IP addresses for the clients.

Is that sounds right to you all?

Is yes, then where can Oracle Connection Manager fit in? Is it true that if you use CMAN, you can skip all above steps? And can you configure firewall to accept all clients' request send to port 1521 instead of only the predefined ones?

Thanks so much for all your help and have a wonderful X'mas!
12-21-2001, 11:44 AM
jmodic

Re: Thanks all, but the thing is ...

Quote:

Originally posted by elaine3839
1. If the Oracle server is on Windows Box (which is a multithread server), then you can always make it work by set USE_SHARED_SOCKET = TRUE in registry.

Yes, but then you must also make your clients avare of the consequences of this setting (ie they can't stop the listener without shutting down the database)

Quote:

1.1 If their Oracle server is running in MTS mode, then they can simply "fix" the port number through which connection will be established by setting

mts_dispatchers="(address=(protocol=tcp)(host=hostname)(port=1521))(dispatchers=1)"

In this case, you don't have to set USE_SHARED_SOCKETS = TRUE. (is this statement correct?)

Yes (AFAIK).

Quote:

2. If the Oracle server is not on a multithreaded OS, then there is no additional requirement besides keep port 1521 open on firewall and configure firewall to only accept predefined IP addresses for the clients.

Not quite. On Unix you have the same problems as with Win OS if your database is set up in MTS mode! Also in this configuration you must fix the dispatcher's port in your init.ora to "fix" the port.

On metalink you have some documents that deals exclusivelly with the Firewal - Oracle issues. I suggest you study them carefully. Here is the URL: http://metalink.oracle.com/metalink/...1&blackframe=0
12-21-2001, 12:08 PM
elaine3839

Thanks you, thank you, thank you...

I will read the docs you send me for sure --- I feel I learn something new from this forum everyday and thanks for all your (and other people) contribution to this forum.

Maybe this question will be answered in the doc, but can I check with you that if the Oracle server is running in dedicated mode, do we still need to 'fix' the port for it? Or it will always use port 1521 even after initial handshaking?

Thanks again...
12-21-2001, 04:11 PM
jmodic

Re: Thanks you, thank you, thank you...

Quote:

Originally posted by elaine3839
Maybe this question will be answered in the doc, but can I check with you that if the Oracle server is running in dedicated mode, do we still need to 'fix' the port for it? Or it will always use port 1521 even after initial handshaking?

On Unix, AFAIK the conversation will remain on the same port (e.g. 1521) if database is not in the MTS mode. On NT on the other hand, regardless if the database is in MTS or dedicated mode, only the initial request will go through that port, after that the database will choose another port through which the actual database session conversation will take place.

If your database is not in MTS mode, then talking about "fixing" the port is irrelevant, as setting that permanent port in the init.ora implies MTS mode! If you wan't to fix the port you must set MTS_DISPATCHERS parameter in the init.ora, and as soon as you have that parameter set you are in MTS mode ;).
08-28-2002, 10:15 PM
tony

Tim and Jurij,

Thanks both for your good explanation and solution. I have had this problem and spent the best part of two days trying to figure it out.

I'ts now fixed.

-- Tony