ASA Oracle SQLNET Disconnects

[enkrypter]

I wanted to make a post to help other people.

I have an ASA5585-40 FO pair running 8.4.5 code in my data center that protects various subnets containing oracle servers and application servers.  After installing the FW with wide open IP ANY ANY rules we noticed things broke.

1. The first thing we did was disable SQLNET global policy inspection.  It's know to be a pile of junk.

2. The next thing we did was create a global service policy to match TCP/1521 traffic with an ACL

3. We  then set TCP connection properties on those streams to include the      following details:

   1. Timeout=3:00:00

   2. Reset enabled

   3. DCD enabled

   4. Retry interval 00:05:00

   5. Retry times=5

4. We also configured the TCP normalization options in another TCP map on these streams.

   1. Disabled the "Clear Urgent flag" to allow URG flags

I am posting this because default ASA settings are not shown in the config file and I could not find this stuff anywhere on Netpro or Google.  There seemed to be a lot of different firewall and Oracle related trouble with a lot of different solutions that did not work for us.

Some Oracle applications will loose connectivity to the database if the application server sets the urgent flag in TCP packets.  I'm not willing to speculate on which types of Oracle applications use this flag, but all of our do and they flat out refused to connect to the database if the flag is not set.

By default the ASA will remove URG flags.  The result is, you will have disconnected sessions as the ASA will see the connections as timed out and discard them.  By setting the TCP nomalization map to allow URG flags, your applications should function normally.

Enabling Dead Connection Detection will keep database connections alive so the hard TCP timeout value wont kill off long running DB process connections.  This allows you to maintain a shorter TCP global limit and only adjust limits on traffic that really needs them set higher.  This will help keep your ASA from crashing due to memory issues or causing the sate or connection tables form getting so full that they cannot accept any new connections.

Our Oracle environment is crazy, but I am sure that I am not the only person that has had these issues.  GL, I hope this helps someone else.  It's been driving my nuts for the last two days.

[moghadasi_ha]

Dear friend ,

Thanks for your post , I had same problem as you with Oracle and Cisco ASA . Fortunately my problem has been solved with your solution .

[Jouni Forss]

Hi,

Too bad I am unable to endorse actual starting posts. Only replies. Since this would indeed be some helpfull information.

I have also had to deal with similiar problems as you and have had to resort creating special policys for just this traffic.

EDIT:

And to clarify about the "endorsement", I mean the following

https://supportforums.cisco.com/community/netpro/idea-center/cafe/blog/2012/07/27/cisco-designated-vip-endorsed-program

- Jouni

[david.tran]

enkrypter wrote:
Enabling Dead Connection Detection will keep database connections alive so the hard TCP timeout value wont kill off long running DB process connections. 

I could have told you this two years ago .  That's what we did to our environment to get around issues like this.  Does not matter if you have Cisco ASA or Checkpoint firewalls

For those that are interested, you can just enable keepalive for sqlnet on the database sqlnet.ora file.  Here is the syntax:

SQLNET.EXPIRE_TIME = 1 (in minute). 

In this secnario, every 60 seconds (however, the very first keepalive will start in 2 minutes after the connection is established), the database will probe the client with a keepalive packet of about 10 bytes.  In your tcpdump you will see something like this:

21:58:18.572337 IP 192.168.1.70.1521 > 192.168.15.7.2345: P 6436:6446(10) ack 6199 win 46644

21:58:18.697282 IP 192.168.15.7.2345 > 192.168.1.70.1521: . ack 6446 win 64677

This is highly recommended when you have database connection going across the firewalls.

my 2c

[abashoru]

Thanks Enk,

I had this problems just couple of days ago,  but the strange thing was the ASA was denying the SQL trafffic tcp/1521 from the wrong interface. For example the database is located on the outside and the client on the inside,  the traffic from the database is shown to be denied by the ASA from the inside, No NAT is being used on the ASA and ip any any on both interfaces did not show any effects. 

Also SFTP is being used between the client and the database. Am seeing syn timeout.

Has anyone seen this behavior before? Please help. 

Cheers 

[MKH]

Hello

We have replaced our FWSM with the cisco ASA 5585-x (SSP-60).We have configured them in cluster mode. But some Oracle applications are losing connectivity to the database after replacement of Firewalls, Frequently.

The error on the application server is:

“Failed getting connection - at oradatabase.cpp(101) ORA-12547 : TNS: lost contact”

And error on the ASA is:

“Deny TCP (no connection) from appserver_ip/54864 to database_server_ip/1521 flags FIN ACK on interface Application_server_interface.”

The first thing we created IP ANY ANY rules on the interface that belongs to applications.

According to forum suggestions, we have disabled SQLNET global policy inspection.

The next thing, we have created a service policy (interface base) to match our application to database connection on TCP/1521 protocol.

Then we have setted up TCP connection properties on those streams to include the following details:

1. Timeout=0:00:00 >>>>>unlimited
2. Reset enabled
3. DCD enabled
4. Retry interval 00:15:00
5. Retry times=5

We also have configured TCP map in the TCP normalization options on that:

1. Setted the reserved bits on “Allow only”.
2. Disabled the "Clear Urgent flag" to allow URG flags.
3. Disabled the “Drop Connection on window variation”.
4. Disabled the “Drop Packets that exceed maximum segment size”.
5. Disabled the “check if retransmitted data is the same as original”.
6. Disabled the “Drop SYN packets with data”.
7. Enable TTL evasion protection.
8. Disabled the “Verify TCP checksum”.
9. Disabled the “Drop SYNACK packets with data”.
10. Disabled the “Drop packets with invalid ACK”.

And in TCP option just “clear window scale” has enabled.

Does inspection on SQLNET ineffect by disabling SQLNET global policy inspection?

What‘s wrong with us?

Thank you.

[enkrypter]

1.  I would not recommend setting any timeout value to unlimited.  You run the risk of memory exhaustion or causing your ASA to no longer accept new connections. 

2.  You should not need interface access rules that permit IP any any.  If this is done right, you should only need to permit applications to talk over TCP/1521 to the database.

3.  See attachment for ASDM configuration screenshots.

[MKH]

Hello

I really appreciate your help and assistance.

Do you set The  SQLNET.EXPIRE_TIME parameter in your database?

Does the SQLNET.EXPIRE_TIME parameter in your database have default value?What is that quantity?

Thank you.