Pix firewall sqlnet inspection

cisco24x7 · ‎09-19-2009

Hi everyone,

I hope someone with extensive experiences with Cisco Pix/ASA can tell me

what the purpose of this command:

fixup protocol sqlnet 1521-1526

fixup protocol http 80

policy-map global_policy

class class_sqlnet

inspect sqlnet

inspect http

I have rule on the firewall as follows:

policy-map global_policy

class class_sqlnet

inspect sqlnet

access-list test permit tcp any any range 1521-1526 log

access-group test interface outside

Behind the firewall is a bunch of Oracle database servers version 9i, 10g and 11g.

I use an Oracle utility called Oracle loader to load about 40GB of data into these

oracle databases. These jobs hang after loading about 20GB of data into the databases.

The only way to load 40GB of data into the database is to "no fixup protocol sqlnet 1521-1526".

My question is this:

What are the advantages of enabling sqlnet inspection on the firewall? From what I've

seen so far, it does nothing but cause trouble for my sqlnet traffics especially loading

massive amount of data into the database. What does sqlnet inspection actually do?

Are there any security risks in disabling sqlnet inspection? Thanks.

Jon Marshall · ‎09-19-2009

David

Sqlnet inspection is there because of the way sqlnet allocates ports. Basically clients connect to the SQL server on port 1521. The server then starts up another sqlnet service for that client on a random port. The server informs the client of the random port and the client then initiates the connection to the new port.

Obviously without sqlnet you would have to open up all dynamic ports on the firewall to allow the new connection through. Sqlnet inspection on the firewall "reads" the random port sent by the server and dynamically opens up that port for the client connection.

Having said all that a lot of more recent Oracle Sqlnet implementations don't actually do this random port thing anymore and indeed if it is often a configurable option within the Oracle app.

I have personally experienced issues with sqlnet and have had cause to turn it off before to enable Oracle to work correctly.

Jon

cisco24x7 · ‎09-20-2009

"Having said all that a lot of more recent Oracle Sqlnet implementations don't actually do this random port thing anymore and indeed if it is often a configurable option within the Oracle app."

Fair enough. My Oracle database servers are configured to use only tcp port 1521 as seen in the below tcpdump:

[Expert@NEO-labgw]# tcpdump -nnni eth1 host 192.168.1.210 and not port 161

tcpdump: listening on eth1

13:23:19.469850 192.168.1.201.53929 > 192.168.1.210.1521: S 523713923:523713923(0) win 65535 (DF)

13:23:19.470550 192.168.1.210.1521 > 192.168.1.201.53929: S 1633498661:1633498661(0) ack 523713924 win 16384

13:23:19.471191 192.168.1.201.53929 > 192.168.1.210.1521: . ack 1 win 65535 (DF)

13:23:19.471640 192.168.1.201.53929 > 192.168.1.210.1521: P 1:269(268) ack 1 win 65535 (DF)

13:23:19.491274 192.168.1.210.1521 > 192.168.1.201.53929: P 1:9(8) ack 269 win 65267 (DF)

13:23:19.492061 192.168.1.201.53929 > 192.168.1.210.1521: P 269:537(268) ack 9 win 65527 (DF)

13:23:19.492773 192.168.1.210.1521 > 192.168.1.201.53929: P 9:41(32) ack 537 win 64999 (DF)

13:23:26.820223 192.168.1.201.53929 > 192.168.1.210.1521: P 4430:4605(175) ack 4452 win 64619 (DF)

13:23:26.821714 192.168.1.210.1521 > 192.168.1.201.53929: P 4452:5166(714) ack 4605 win 64951 (DF)

13:23:26.823209 192.168.1.201.53929 > 192.168.1.210.1521: P 4605:4626(21) ack 5166 win 65535 (DF)

13:23:26.823960 192.168.1.210.1521 > 192.168.1.201.53929: P 5166:5887(721) ack 4626 win 64930 (DF)

13:23:26.926009 192.168.1.201.53929 > 192.168.1.210.1521: P 4626:4654(28) ack 5887 win 64814 (DF)

13:23:26.926782 192.168.1.210.1521 > 192.168.1.201.53929: P 5887:5904(17) ack 4654 win 64902 (DF)

13:23:27.133475 192.168.1.201.53929 > 192.168.1.210.1521: . ack 5904 win 64797 (DF)

13:23:29.032513 192.168.1.201.53929 > 192.168.1.210.1521: P 4654:4667(13) ack 5904 win 64797 (DF)

13:23:29.033763 192.168.1.210.1521 > 192.168.1.201.53929: P 5904:5921(17) ack 4667 win 64889 (DF)

13:23:29.040059 192.168.1.201.53929 > 192.168.1.210.1521: P 4667:4677(10) ack 5921 win 64780 (DF)

13:23:29.040188 192.168.1.201.53929 > 192.168.1.210.1521: F 4677:4677(0) ack 5921 win 64780 (DF)

13:23:29.044753 192.168.1.210.1521 > 192.168.1.201.53929: . ack 4678 win 64879 (DF)

13:23:29.044816 192.168.1.210.1521 > 192.168.1.201.53929: F 5921:5921(0) ack 4678 win 64879 (DF)

13:23:29.045573 192.168.1.201.53929 > 192.168.1.210.1521: . ack 5922 win 64780 (DF)

Therefore, even with sqlnet inspection enable, it shoud work right?

Sadly, it does NOT.

Jon Marshall · ‎09-20-2009

David

"Therefore, even with sqlnet inspection enable, it shoud work right?"

Well you would think so but then again i didn't write the inspection code for sql :-)

As far as i know the sqlnet code is there purely to deal with the port number issue so to be honest if it works without it then i would disable it.

Jon