oracle 穿越防火墙的问题！

[穿过防火墙连接数据库
[A]这个问题只会在WIN平台出现，UNIX平台会自动解决。
解决方法：
在服务器端的SQLNET.ORA应类似
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
TRACE_LEVEL_CLIENT = 16
注册表的HOME0加[HKEY_LOCAL_MACHINE]
USE_SHARED_SOCKET=TRUE
我按照这个东西作了但是客户端还是不能连街上。我用的是8.1.6

ref：http://community.csdn.net/Expert/topic/3290/3290635.xml?temp=.6574671
找到一个好文章http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:264415410962
carsten -- Thanks for the question regarding "firewall rules for net8 connection", version 8.1.5
originally submitted on 2-Jun-2000 3:02 Eastern US time, last updated 26-Apr-2004 4:42

Tom's latest followup | Bookmark Page | Bottom

You Asked (Jump to Tom's latest followup)

hi ..
which ports must be opened on the firewall to communicate 
with a oracle8-db using net8 and tcp? or, how is is the pool 
of ports defined? 

we are using 1521 as the initial portnumber for net8-communication 
with the db. if we open only this portnumber there is no connection
possible. with a netsniffer we found out that only the initial
contact is done on the 1521-port followed by a portswitch to 
e.g. 1051 or something else. each new connection-attempt
is using a new portnumber. our firewall has no prebuild rule
for this communication-model. where can I get such information?

thanks carsten

 

and we said...

Are you on NT?  The following can be used to help solve this issue.  It explains 
what the issue is and then offers ways around it..

PURPOSE

  This article describes how to get around firewall problems with SQL*Net/Net*8 
on NT Servers 

 
Listeners on NT commonly listen on port 1521.  It is a common misconception that 
if you allow access in to and out of the firewall by enabling access through 
port 1521, that SQL*Net clients will be able to connect. To understand why the 
connect will fail, it is necessary to understand how a SQL*Net 
connection on NT works.

When a client initiates a connect, a TCP connection is established with port 
1521. A TNS CONNECT packet is then sent to the listener. On UNIX systems the 
listener process will fork a new Oracle process to deal with the new incoming 
connection. With UNIX, forked processes will inherit the resources owned by the 
parent process, in other words file handles and TCP sockets. 

Earlier releases of SQL*Net for Windows NT used the WINSOCK V1.1 API. With this 
version of WINSOCK there is no capabaility of passing a TCP socket between two  
processes, and no way to inherit a TCP socket. To work around this restriction 
a new thread of execution is created by the main Oracle process and a local  
connection is made between the listener and this new thread. The newly created 
Oracle thread randomly selects a new TCP port, for example port 1087, to use for 
the connection request and informs the listener of the new port to be used. 

The listener now needs to inform the client that they need to REDIRECT the 
connection attempt to this newly selected networking endpoint. The listener now 
sends a TNS REDIRECT packet to the client with details of the new port to 
reconnect to. The client drops the existing TCP connection and then issues a TCP 
Connect sequence to the new TCP port, and this is then followed by a TNS Connect 
packet. If all is well and the Oracle server is able to process the incoming 
connection request, then the server thread will respond with a TNS ACCEPT packet 
and data will begin to flow.

So, if you enable connects through port 1521 on your firewall, you can now see 
that after the REDIRECT packet has been sent to the client, the connect will 
fail as port 1087 is not enabled in the firewall. As the REDIRECT port that gets 
generated is entirely at random, you cannot enable access through multiple
ports in the firewall as you have no idea which ports will get allocated.

To workaround this problem there are several options:

1. Configure the firewall to limit IP addresses rather than port numbers. This 
is not a very secure option.

2. Use Connection Manager so the TNS CONNECT following the REDIRECT happens the 
server side of the firewall. 

3. If you are on Oracle 8, you can use a WINSOCK V2 API feature called Shared 
Sockets . This allows a socket to be shared (or passed) between multiple 
processes. To use this functionality in a single Oracle Home enviroment, set 
USE_SHARED_SOCKET = TRUE in the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE section of 
the registry. If you are using Multiple Oracle Homes, change to the desired 
Oracle 8 Home and view the oracle.key file in ORACLE_HOME\BIN to find which 
registry key to add USE_SHARED_SOCKET  to.

   Please Note that as WINSOCK V2 allows a socket to be shared between multiple 
processes, you cannot restart the listener without taking the database down 
first.

 

	Reviews
Bookmark Review | Bottom | Top very help full  October 08, 2001 Reviewer:  Pinakin  from NY, USA We just move all database server from one palce to another(NT ,Unix),Then we got problem with Database on NT box, we did not connect to database on NT from outside, so I set USE_SHARED_SOCKET = TRUE in Regdet32 and it works Bookmark Review | Bottom | Top Firewall Rules  January 04, 2002 Reviewer:  T.Thiagarajan  from India The answer was quite useful for WinNT box, but what about the solaris. Can u tell me how to configure firewall rules for solaris. Followup: use dedicated servers and you have no problems like you do on NT. steps 1 and 2 apply to unix, step 3 is unneccessary, unix always has done "shared_sockets" Bookmark Review | Bottom | Top I think options 1 and 2 are not necessary on Unix boxes  January 15, 2003 Reviewer:  Rajidhar Etta  from VA, USA Tom, From the document , it seems we need to implement one of 3 options only if OS is Windows NT, as only 1521 port is to be opened for Unix. Right now our application server (9iAS) communicates with DB server which is behind a firewall with out using connection manager (and only 1521 port is opened). However, we did experienced firewall time out problem for which we are considering 1. Oracle Dead Connection Detection (DCD) 2. Our system administrator suggested tweaking NDD settings for 1521 on client/server boxes so that OS can send ACK packets periodically. 3. Write Custom code to loop through each connection in the middle tier and execute dummy SQL for every "N" minutes. Is my approach correct? Followup: well, the article did start off with This article describes how to get around firewall problems with SQL*Net/Net*8 on NT Servers on NT servers.... 1521 is not a fixed port, you can pick and choose it. And it only applies with dedicated server, if you use shared server - you are back to CMAN again, even on unix. It is funny how your company has a security policy that times out idle connections -- but you are trying to find ways to subvert such a policy... Why not have the connection pool time out its connections -- eg: if you havent been used in 5 minutes, disconnect, we don't need you anymore. That way you would be in compliance with your companies desires. Bookmark Review | Bottom | Top Thanks ,  January 16, 2003 Reviewer:  Rajidhar Etta  from VA, USA Thanks Tom for the reply, I missed the point that those options are for shared server mode only - thanks for clarfication. Our application is hosted in a shared environment and firewall rules cannot be changed easily for each customer- thats why we are figuring out ways to solve this problem. Bookmark Review | Bottom | Top and for Win2k?  May 23, 2003 Reviewer:  Si  from London, England I think I understand the gist of what you have said about 1521 and firewalls on NT. I am trying to install the same on Win2k. Could you suggest any solutions to get past the block? Thank you. Followup: this applies verbaitim to win2k. Bookmark Review | Bottom | Top Re: Win2k troubles  May 24, 2003 Reviewer:  Si  from London, England Thank you for answering so quickly. Unfortunately, setting USE_SHARED_SOCKET = TRUE did not do anything. I have since seen other posts online saying that this does not work for most people, I am not sure why. I am also unsure as to where the connection manager is. A search for the file cman.ora on my drives turned up no results. Would you have any pointers? Thanks. Followup: the documentation -- see the net admin guide. have you actually tested that 1521 is in fact OPEN, you know, telnet to it and see if telnet can get to the listener. if not, it has nothing to do with shared sockets. Bookmark Review | Bottom | Top Re: Win2k troubles  May 24, 2003 Reviewer:  Si  from London, England 1521 is open so I am ok there. The Oracle CD I have is a freebie I got from the website many moons ago, and so I do not have any documentation links with me. Do you know where I can find this online? I also don't have a MetaLink account. Thanks Followup: otn.oracle.com Bookmark Review | Bottom | Top Re: Win2k troubles  May 24, 2003 Reviewer:  Si  from London, England Thank you. Can you tell me how to telnet to 1521 and check if it is open? My firewall settings say that it is but a second opinion would be good. Followup: telnet hostname 1521 if you see: [tkyte@tkyte-pc-isdn Desktop]$ telnet hostname 1521 Trying X.Y.Z.A... Connected to hostname Escape character is '^]' it is open, if you see: [tkyte@tkyte-pc-isdn Desktop]$ telnet tkyte-pc-isdn 1522 Trying 192.168.0.3... telnet: connect to address 192.168.0.3: Connection refused [tkyte@tkyte-pc-isdn Desktop]$ it isn't. Bookmark Review | Bottom | Top Re:Win2k troubles  May 24, 2003 Reviewer:  Si  from London, England I'm having trouble with this. When I open the window, I get a prompt saying Microsoft Telnet> I then type in telnet x 1521 where x is my computer name and I get an Invalid Command error. I type in the same but where x is the hostname given by my ISP and I get the same error. Am I not in the right window? Bookmark Review | Bottom | Top   May 24, 2003 Reviewer:  A reader Si, You should go to command prompt not telnet prompt. Just want to save Toms time :-) Thanks Bookmark Review | Bottom | Top Re:Win2k troubles  May 24, 2003 Reviewer:  Si  from London, England Thank you very much to both Tom and the kind reader. Your help is greatly appreciated. Bookmark Review | Bottom | Top Getting Beyond Firewall  July 01, 2003 Reviewer:  A reader  from Ca, USA We have a situation where if we run the following command from our Oracle 8i Database, we get the following result: SQL> select utl_http.request(' http://elocation.oracle.com' from dual; UTL_HTTP.REQUEST('HTTP://ELOCATION.ORACLE.COM') -------------------------------------------------------------------------------- <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD><TITLE>Oracle eLocation Services</TITLE> <META http-equiv=Content-Type content="text/html; charset=windows-1252"> <META content="MSHTML 5.50.4134.600" name=GENERATOR><LINK href="newimages/marlin.css" type=text/css rel=stylesheet> </HEAD> <BODY class=OraBody> <TABLE cellSpacing=0 cellPadding=0 width="100%" border=0> <TR> <TD vAlign=bottom rowSpan=2><IMG src="newimages/oraclelogo_sizewithprodbrand .gif"><BR><IMG height=69 src="newimages/elocation.gif" width=340></TD> <TD align=right> <TABLE cellSpacing=0 cellPadding=0 align=right border=0> <TBODY> </TBODY> </TABLE> </TD> </TR> <TR> <TD vAlign=bottom> <TABLE border=0 cellPadding=0 cellSpacing=0 width="98%"> <TBODY> <TR> <TD>&nbsp;</TD> <TD width=50>&nbsp;</TD> <TD align=right noWrap width=16><IMG height=21 src="newimages/blue-head.gif" width=12 border=0></TD> <TD background=newimages/blue-content.gif noWrap width="1%" class=OraN av1Selected>Legal Notices</TD> <TD align=right bgColor=#336699 vAlign=top width="12"><IMG height=21 src="newimages/blue-end.gif" width=12 border=0></TD> </TR> </TBODY> </TABLE> </TD> </TR> </TABLE> <TABLE cellSpacing=0 cellPadding=0 width="100%" border=0> <TBODY> <TR> <TD vAlign=bottom height=24> <TABLE cellSpacing=0 cellPadding=0 width="100%" border=0> <TBODY> <TR> <TD vAlign=bottom noWrap width="52%" bgColor=#336699 height=30>&nbsp;< /TD> <TD vAlign=top noWrap width="48%" background="newimages/asysrtb.gif" h eight=30><IMG height=30 src="newimages/asysrt.gif" width=40 border=0></TD> <TD width=8 height="30" xbackground="newimages/rhshadow.gif"><IMG heig ht=30 src="newimages/asylrhs.gif" width=8 border=0></TD> </TR> <TR> <TD vAlign=t However, when our client issued the same command from their Oracle8i Database, they got: SQL> select utl_http.request(' http://elocation.oracle.com' from dual; UTL_HTTP.REQUEST('HTTP://ELOCATION.ORACLE.COM') ---------------------------------------------------------------------------- ---- 1 row selected. I was told that, on their database server, they need userid/password to access the internet. What can they do in this situation where they need to access the internet from a stored procedure in the datbase? thanks Followup: so you are saying that their proxy server challenges them with a user/pass prompt or that they need to use a wallet? If the proxy needs a user/password, its going to be a bit hard. You would need to either o write to the proxy server directly using utl_tcp, you would implement a small http client o load up some java packages into the database that support this functionality. But the really STICKY question you have to answer is "where do I get the user/password from". Hmm, you would need to get it from them - it would be messy at best. Bookmark Review | Bottom | Top   July 01, 2003 Reviewer:  A reader  from Ca, USA Bookmark Review | Bottom | Top Getting Beyond the Firewall  July 01, 2003 Reviewer:  A reader  from Ca, USA Hi Tom, Is there no database configuration setting that takes proxy info (including userid/password) and allows the database server to access the Internet? thanks Followup: no. not with utl_http and definitely not in 8i (where utl_http was a tad "primitive" Bookmark Review | Bottom | Top authenticate proxy  July 01, 2003 Reviewer:  Sudhakar Nagisetty  from Bristol, CT, USA here is the code to authenticate to proxy... handle exceptions accordingly..... req utl_http.req; resp utl_http.resp; name VARCHAR2(256); value clob ; tValue clob ; data VARCHAR2(255); my_scheme VARCHAR2(256); my_realm VARCHAR2(256); my_proxy BOOLEAN; -- When going through a firewall, pass requests through this host. -- Specify sites inside the firewall that don't need the proxy host. utl_http.set_proxy(proxy_info); -- this is your proxy ip address -- Ask UTL_HTTP not to raise an exception for 4xx and 5xx status codes, -- rather than just returning the text of the error page. utl_http.set_response_error_check(FALSE); -- Begin retrieving this web page. req := utl_http.begin_request(strUrl); -- Identify ourselves. Some sites serve special pages for particular browsers. utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0'); utl_http.set_authentication(req, username, password,'Basic',true); -- Now start receiving the HTML text. resp := utl_http.get_response(req); -- Look for client-side error and report it. IF (resp.status_code >= 400) AND (resp.status_code <= 499) THEN -- Detect whether the page is password protected, and we didn't supply -- the right authorization. IF (resp.status_code = utl_http.HTTP_UNAUTHORIZED) THEN utl_http.get_authentication(resp, my_scheme, my_realm, my_proxy); IF (my_proxy) THEN dbms_output.put_line('Web proxy server is protected.'); dbms_output.put('Please supply the required ' || my_scheme || ' authentication username/password for realm ' || my_realm || ' for the proxy server.'); ELSE dbms_output.put_line('Web page ' || strUrl || ' is protected.'); dbms_output.put('Please supplied the required ' || my_scheme || ' authentication username/password for realm ' || my_realm || ' for the Web page.'); END IF; ELSE dbms_output.put_line('Check the URL.'); END IF; utl_http.end_response(resp); -- Look for server-side error and report it. ELSIF (resp.status_code >= 500) AND (resp.status_code <= 599) THEN dbms_output.put_line('Check if the web site is up.'); utl_http.end_response(resp); END IF; -- Keep reading lines until no more are left and an exception is raised. tvalue := strUrl; begin LOOP -- utl_http.read_line(resp, value); utl_http.read_text(resp, value); if tvalue = strUrl then tvalue := value; else dbms_lob.append(tvalue,value); end if; END LOOP; exception WHEN utl_http.end_of_body THEN utl_http.end_response(resp); when others then utl_http.end_request(req); end; EXCEPTION WHEN utl_http.end_of_body THEN utl_http.end_response(resp); -- return value; when others then utl_http.end_request(req); -- return value; END; Bookmark Review | Bottom | Top authenticate proxy  July 01, 2003 Reviewer:  Sudhakar Nagisetty  from Bristol, CT, USA Sorry forgot to mention, the code is for Oracle 9i Bookmark Review | Bottom | Top How about Port on Client  September 17, 2003 Reviewer:  LJ  from oh, usa How about the ports on the client side? Which port is used by Oracle client, e.g. sqlplus, OCI? Followup: they use the ports you told them to -- "connect to host X port 1521 and request the service foo" the tns connect string tells you which host/port they will use. Bookmark Review | Bottom | Top i do all the steps that you said but i still have problem  December 25, 2003 Reviewer:  aldobai mostafa  from yemen thanks Tom my db server oracle 8.1.7 is on one side of firewall in the other side of the firewall there are my clients and DNS server ,DHCP server i configure the firewall to pass any packets (for test prpose only) when i ping the database server i get replay but when i try to use tnsping to the db server i get ora-12560 errors what shall i do to solve this problem ? with my best regards Followup: [tkyte@tkyte-pc-isdn tkyte]$ oerr ora 12560 12560, 00000, "TNS:protocol adapter error" // *Cause: A generic protocol adapter error occurred. // *Action: Check addresses used for proper protocol specification. Before // reporting this error, look at the error stack and check for lower level // transport errors.For further details, turn on tracing and reexecute the // operation. Turn off tracing when the operation is complete. Bookmark Review | Bottom | Top JDBC connection across firewall  December 28, 2003 Reviewer:  Ramadan  from Dhahran, Saudi Arabia We have a webMethods adapter which links between our SAP server and Oracle db (8.1.5 on NT4.0 WS) using JDBC connection. The connection string looks like this... jdbc:oracle:thin:@10.20.30.40:1521:TFMS We experienced the same communication problem when we configured only port 1521 in the firewall. Will USE_SHARED_SOCKET = TRUE solve the problem? I checked the registery and didn't find it there. Should I add it? Followup: it could be it, we use port redirection, use shared socket should let us "inherit" the connection. Bookmark Review | Bottom | Top firewall rules for net8 connection  April 13, 2004 Reviewer:  Steve  from Kingston-Upon-Thames, Surrey UK Hi Tom Dose the above Firewalling issue Affect Oracle 9i as well. I seem to remember hitting the same issue with 8i in the past and using a Communications Manager box to act as a proxy for Oracle Client/Server Traffic. I was Wondering if this is still the case with 9i Regards Steve Followup: depends on your network topology, apparently with your firewall -- or the features you were using with 8i -- you had to use CMAN (shared server will do that for example). In 8i with straight dedicated servers and a pretty "normal" firewall that let sqlnet traffic through, you would not have an issue. Bookmark Review | Bottom | Top firewall rules for net8 connection  April 13, 2004 Reviewer:  Raj  from SanFrancisco, CA, USA Is it mandatory to use 1610 or 1630 as cman ports ? Can't we use port 1521 for cman ? When I start cman, listener in the same machine is not working. I get TNS Connection refused error, What could be the reason Followup: please contact support for install/configuration questions.... the ports are not mandatory, no. Bookmark Review | Bottom | Top firewall rules for net8 connection   April 14, 2004 Reviewer:  Steve  from Kingston-Upon-Thames, Surrey UK Hi Tom Thanks For The prompt Reply Where you said "In 8i with straight dedicated servers and a pretty "normal" firewall that let sqlnet traffic through, you would not have an issue." Did you meen 9i ?? and if so. That 9i no longer used the random port numbers to talk back to the client application. Regards Steve Followup: No, meant 8.0 and up really -- with use_shared_socket -- and dedicated servers, there is no port redirection going on. Bookmark Review | Bottom | Top   April 23, 2004 Reviewer:  A reader if i am trying to connect to oracle server through ms acces using odbc connection do i need to install oracle client on the workstation. Followup: yes. Bookmark Review | Bottom | Top USE_SHARED_SOCKET = TRUE   April 23, 2004 Reviewer:  MGRIEGO  from California I am confused on how to enter this parameter in the registry. Some sites say enter as a key and others as a string. Which is it? If it is entered as a string what do I enter for the "Value Data" field? MGRIEGO Followup: http://www.stunnel.org/examples/oracle.html according to that poster, it is a "string".

Was this response helpful to you? Let us know!

Bookmark this page with the link HERE


To workaround this problem there are several options:

1. Configure the firewall to limit IP addresses rather than port numbers. This
is not a very secure option.

2. Use Connection Manager so the TNS CONNECT following the REDIRECT happens the
server side of the firewall.

3. If you are on Oracle 8, you can use a WINSOCK V2 API feature called Shared
Sockets . This allows a socket to be shared (or passed) between multiple
processes. To use this functionality in a single Oracle Home enviroment, set
USE_SHARED_SOCKET = TRUE in the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE section of
the registry. If you are using Multiple Oracle Homes, change to the desired
Oracle 8 Home and view the oracle.key file in ORACLE_HOME\BIN to find which
registry key to add USE_SHARED_SOCKET  to.

   Please Note that as WINSOCK V2 allows a socket to be shared between multiple
processes, you cannot restart the listener without taking the database down
first.