How to connect to Oacle server from client outside the firewall using ODBC?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

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

Hybrid View

  1. #1
    Join Date
    May 2001
    Posts
    285

    Unhappy

    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!

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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????
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    May 2001
    Posts
    285

    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!

  4. #4
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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!
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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)"
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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 Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    May 2001
    Posts
    285

    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!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width