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

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Hybrid View

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?

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????

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.

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:

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:

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.

Jurij ModicASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?

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

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?