SQLNET ALG
The SQLNET protocol is used by Oracle SQL servers to execute SQL commands from clients, including load balancing and application-specific services. Support of stateful firewall and NAT services requires that you configure the SQLNET ALG for TCP port 1521. The ALG monitors the control packets, opens flows dynamically for data traffic, and performs NAT address and port rewrites.
Understanding the SQLNET ALG
The SQLNET Application Layer Gateway (ALG) processes Transparent Network Substrate (TNS) REDIRECT packets for IP addresses and port information. The SQLNET ALG performs Network Address Translation (NAT) on the payload of the TNS REDIRECT packet, opens a pinhole for a new connection from a client to a server, and transfers data between a client and a server located on opposite sides of a Juniper Networks device.
SQLNET ALG supports the following types of data transfer modes:
Redirect mode — connect-redirect type
Interleave mode — connect-accept type
Load balance — connect-redirect-connect-redirect type
SQLNET allows remote data access between applications and the Oracle database, or among multiple Oracle databases. SQLNET primarily establishes and maintains connection between a client application and an Oracle database server. SQLNET has several communication layers that enable clients and database servers to share, modify, and manipulate data.
Oracle SQL servers use the SQLNET protocol to execute SQL commands from clients, including load balancing and application-specific services. The SQLNET protocol uses TNS as its networking architecture, and all SQLNET traffic is encapsulated into TNS packet format.
The SQLNET ALG monitors control packets, opens pinhole for data traffic, and performs NAT and port rewrites. Support of stateful firewall and NAT services are required to configure the SQLNET ALG for TCP port 1521.
Example: Configuring the SQLNET ALG
The SQLNET ALG processes TNS REDIRECT packets, performs NAT, and opens a pinhole for a new connection from a client to a server.
This example shows how to configure the SQLNET ALG in route or NAT mode, allow SQLNET traffic to pass through a device, and transfer data between a client and a server located on opposite sides of a Juniper Networks device.
Requirements
This example uses the following hardware and software components:
An SRX Series Firewall
Two PCs (client and server)
Before you begin:
Understand the concepts behind ALGs. See ALG Overview.
Understand the basics of SQLNET ALG . See Understanding the SQLNET ALG.
Overview
In this example, first you configure network interfaces on the device. Create security zones and assign interfaces to the zones, and configure a policy to allow SQLNET traffic to go through an SRX Series Firewall.
Then you create a static NAT rule set rs1 with a rule r1 to match with the destination address 40.0.172.10/32, and you create a static NAT prefix with address 40.0.172.45/32.
Next you create a source NAT pool src-p1 with a source rule set src-rs1 to translate packets from interface fe-3/0/0.0 to interface fe-3/0/1.0. For matching packets, the source address is translated to an IP address in the src-p1 pool.
Then you create a destination NAT pool des-p1 with a destination rule set des-rs1 to translate packets from zone trust to destination address 40.0.172.10/32. For matching packets, the destination address is translated to an IP address in the des-p1 pool. Finally, you enable SQLNET ALG trace options.
Configuration
To configure the SQLNET ALG, perform these tasks:
- Configuring a Route Mode
- Configuring a Static NAT Rule Set
- Configuring a Source NAT Pool and Rule Set
- Configuring a Destination NAT Pool and Rule Set
- Enabling SQLNET ALG
- Enabling SQLNET ALG Trace Options
Configuring a Route Mode
CLI Quick Configuration
To quickly configure this example, copy the
following commands, paste them into a text file, remove any line breaks,
change any details necessary to match your network configuration,
copy and paste the commands into the CLI at the [edit] hierarchy
level, and then enter commit from configuration mode.
set interfaces ge-0/0/0 unit 0 family inet address 10.208.172.58/21 set interfaces fe-3/0/0 unit 0 family inet address 30.3.3.149/8 set interfaces fe-3/0/1 unit 0 family inet address 40.4.4.149/8 set security zones security-zone trust host-inbound-traffic system-services all set security zones security-zone trust host-inbound-traffic protocols all set security zones security-zone trust interfaces fe-3/0/0.0 set security zones security-zone untrust host-inbound-traffic system-services all set security zones security-zone untrust host-inbound-traffic protocols all set security zones security-zone untrust interfaces fe-3/0/1.0 set security policies from-zone trust to-zone untrust policy sql match source-address any set security policies from-zone trust to-zone untrust policy sql match destination-address any set security policies from-zone trust to-zone untrust policy sql match application junos-sqlnet-v2 set security policies from-zone trust to-zone untrust policy sql then permit
Step-by-Step Procedure
The following example requires you to navigate various levels in the configuration hierarchy. For instructions on how to do that, see Using the CLI Editor in Configuration Mode in the CLI User Guide.
To configure route mode:
Configure interfaces.
[edit interfaces] user@host# set ge-0/0/0 unit 0 family inet address 10.208.172.58/21 user@host# set fe-3/0/0 unit 0 family inet address 30.3.3.149/8 user@host# set fe-3/0/1 unit 0 family inet address 40.4.4.149/8
Configure zones and assign interfaces to the zones.
[edit security zones security-zone] user@host# set trust host-inbound-traffic system-services all user@host# set trust host-inbound-traffic protocols all user@host# set trust interfaces fe-3/0/0.0 user@host# set untrust host-inbound-traffic system-services all user@host# set untrust host-inbound-traffic protocols all user@host# set untrust interfaces fe-3/0/1.0
Configure a SQL policy that allows SQL traffic from the trust zone to the untrust zone.
[edit security policies from-zone trust to-zone untrust ] user@host# set policy sql match source-address any user@host# set policy sql match destination-address any user@host# set policy sql match application junos-sqlnet-v2 user@host# set policy sql then permit
Results
From configuration mode, confirm your configuration
by entering the show interfaces, show security zones, and show security policies commands. If the output does
not display the intended configuration, repeat the configuration instructions
in this example to correct it.
For brevity, this show output includes only the configuration
that is relevant to this example. Any other configuration on the system
has been replaced with ellipses (...).
[edit]
user@host# show interfaces
ge-0/0/0 {
unit 0 {
family inet {
address 10.208.172.58/21;
}
}
}
fe-3/0/0 {
unit 0 {
family inet {
address 30.3.3.149/8;
}
}
}
fe-3/0/1 {
unit 0 {
family inet {
address 40.4.4.149/8;
}
}
}
[edit]
user@host# show security zones
...
security-zone trust {
host-inbound-traffic {
system-services {
all;
}
protocols {
all;
}
}
interfaces {
fe-3/0/0.0;
}
}
security-zone untrust {
host-inbound-traffic {
system-services {
all;
}
protocols {
all;
}
}
interfaces {
fe-3/0/1.0;
}
}
...
[edit]
user@host# show security policies
from-zone trust to-zone untrust {
policy sql {
match {
source-address any;
destination-address any;
application junos-sqlnet-v2;
}
then {
permit;
}
}
}
If you are done configuring the device, enter commit from configuration mode.
Configuring a Static NAT Rule Set
CLI Quick Configuration
To quickly configure this example, copy the
following commands, paste them into a text file, remove any line breaks,
change any details necessary to match your network configuration,
copy and paste the commands into the CLI at the [edit] hierarchy
level, and then enter commit from configuration mode.
set security nat static rule-set rs1 from zone trust set security nat static rule-set rs1 rule r1 match destination-address 40.0.172.10/32 set security nat static rule-set rs1 rule r1 then static-nat prefix 40.0.172.45/32
Step-by-Step Procedure
The following example requires you to navigate various levels in the configuration hierarchy. For instructions on how to do that, see Using the CLI Editor in Configuration Mode in the CLI User Guide.
To configure a static NAT rule set:
Create a static NAT rule set.
[edit security nat static rule-set rs1] user@host#set from zone trust
Define a rule to match with the destination address.
[edit security nat static rule-set rs1] user@host# set rule r1 match destination-address 40.0.172.10/32
Define a static NAT prefix for the device.
[edit security nat static rule-set rs1] user@host# set rule r1 then static-nat prefix 40.0.172.45/32
Results
From configuration mode, confirm your configuration
by entering the show security nat command. If the output
does not display the intended configuration, repeat the configuration
instructions in this example to correct it.
[edit]
user@host# show security nat
static {
rule-set rs1 {
from zone trust;
rule r1 {
match {
destination-address 40.0.172.10/32;
}
then {
static-nat {
prefix {
40.0.172.45/32;
}
}
}
}
}
}
If you are done configuring the device, enter commit from configuration mode.
Configuring a Source NAT Pool and Rule Set
CLI Quick Configuration
To quickly configure this example, copy the
following commands, paste them into a text file, remove any line breaks,
change any details necessary to match your network configuration,
copy and paste the commands into the CLI at the [edit] hierarchy
level, and then enter commit from configuration mode.
set security nat source pool src-p1 address 40.0.172.100/32 to 40.0.172.101/32 set security nat source rule-set src-rs1 from interface fe-3/0/0.0 set security nat source rule-set src-rs1 to interface fe-3/0/1.0 set security nat source rule-set src-rs1 rule r1 match source-address 30.0.0.0/8 set security nat source rule-set src-rs1 rule r1 match destination-address 40.0.0.0/8 set security nat source rule-set src-rs1 rule r1 then source-nat pool src-p1
Step-by-Step Procedure
The following example requires you to navigate various levels in the configuration hierarchy. For instructions on how to do that, see Using the CLI Editor in Configuration Mode in the CLI User Guide.
To configure a source NAT pool and rule set:
Create a source NAT pool.
[edit security nat source] user@host#set pool src-p1 address 40.0.172.100/32 to 40.0.172.101/32
Create a source NAT rule set.
[edit security nat source] user@host# set rule-set src-rs1 from interface fe-3/0/0.0 user@host# set rule-set src-rs1 to interface fe-3/0/1.0
Configure a rule that matches packets and translates the source address to an address in the source pool.
[edit security nat source] user@host# set rule-set src-rs1 rule r1 match source-address 30.0.0.0/8
Configure a rule that matches packets and translates the destination address to an address in the source pool.
[edit security nat source] user@host# set rule-set src-rs1 rule r1 match destination-address 40.0.0.0/8
Configure a source NAT pool in the rule.
[edit security nat source] user@host# set rule-set src-rs1 rule r1 then source-nat pool src-p1
Results
From configuration mode, confirm your configuration
by entering the show security nat command. If the output
does not display the intended configuration, repeat the configuration
instructions in this example to correct it.
[edit]
user@host# show security nat
source {
pool src-p1 {
address {
40.0.172.100/32 to 40.0.172.101/32;
}
}
rule-set src-rs1 {
from interface fe-3/0/0.0;
to interface fe-3/0/1.0;
rule r1 {
match {
source-address 30.0.0.0/8;
destination-address 40.0.0.0/8;
}
then {
source-nat {
pool {
src-p1;
}
}
}
}
}
}
If you are done configuring the device, enter commit from configuration mode.
Configuring a Destination NAT Pool and Rule Set
CLI Quick Configuration
To quickly configure this example, copy the
following commands, paste them into a text file, remove any line breaks,
change any details necessary to match your network configuration,
copy and paste the commands into the CLI at the [edit] hierarchy
level, and then enter commit from configuration mode.
set security nat destination pool des-p1 address 40.0.172.45/32 set security nat destination rule-set des-rs1 from zone trust set security nat destination rule-set des-rs1 rule des-r1 match source-address 30.0.172.12/32 set security nat destination rule-set des-rs1 rule des-r1 match destination-address 40.0.172.10/32 set security nat destination rule-set des-rs1 rule des-r1 then destination-nat pool des-p1
Step-by-Step Procedure
The following example requires you to navigate various levels in the configuration hierarchy. For instructions on how to do that, see Using the CLI Editor in Configuration Mode in the CLI User Guide.
To configure a destination NAT pool and rule set:
Create a destination NAT pool.
[edit security nat destination] user@host#set pool des-p1 address 40.0.172.45/32
Create a destination NAT rule set.
[edit security nat destination] user@host# set rule-set des-rs1 from zone trust
Configure a rule that matches packets and translates the source address to the address in the pool.
[edit security nat destination] user@host# set rule-set des-rs1 rule des-r1 match source-address 30.0.172.12/32
Configure a rule that matches packets and translates the destination address to the address in the pool.
[edit security nat destination] user@host# set rule-set des-rs1 rule des-r1 match destination-address 40.0.172.10/32
Configure a source NAT pool in the rule.
[edit security nat destination] user@host# set rule-set des-rs1 rule des-r1 then destination-nat pool des-p1
Results
From configuration mode, confirm your configuration
by entering the show security nat command. If the output
does not display the intended configuration, repeat the configuration
instructions in this example to correct it.
[edit]
user@host# show security nat
destination {
pool des-p1 {
address {
40.0.172.45/32;
}
}
rule-set des-rs1 {
from zone trust;
rule des-r1 {
match {
source-address 30.0.172.12/32;
destination-address 40.0.172.10/32;
}
then {
destination-nat {
pool {
des-p1;
}
}
}
}
}
If you are done configuring the device, enter commit from configuration mode.
Enabling SQLNET ALG
CLI Quick Configuration
Starting in Junos OS Release 15.1X49-D10 and Junos OS Release 17.3R1, the SQLNET application layer gateway is enabled by default.
To quickly configure this example, copy the following commands,
paste them into a text file, remove any line breaks, change any details
necessary to match your network configuration, copy and paste the
commands into the CLI at the [edit] hierarchy level, and
then enter commit from configuration mode.
set security alg sql
Step-by-Step Procedure
The following example requires you to navigate various levels in the configuration hierarchy. For instructions on how to do that, see Using the CLI Editor in Configuration Mode in the CLI User Guide.
To enable SQLNET ALG:
Enable SQLNET ALG.
[edit ] user@host#set security alg sql
Enabling SQLNET ALG Trace Options
CLI Quick Configuration
To quickly configure this example, copy the
following commands, paste them into a text file, remove any line breaks,
change any details necessary to match your network configuration,
copy and paste the commands into the CLI at the [edit] hierarchy
level, and then enter commit from configuration mode.
set security alg sql traceoptions flag all set security alg traceoptions file trace set security alg traceoptions file size 1g set security alg traceoptions level verbose
Step-by-Step Procedure
The following example requires you to navigate various levels in the configuration hierarchy. For instructions on how to do that, see Using the CLI Editor in Configuration Mode in the CLI User Guide.
To enable SQLNET ALG trace options:
Enable SQLNET ALG trace options.
[edit security alg] user@host#set sql traceoptions flag all
Configure a filename to receive output from the tracing operation.
[edit security alg] user@host#set traceoptions file trace
Specify the maximum trace file size.
[edit security alg] user@host#set traceoptions file size 1g
Specify the level of tracing output.
[edit security alg] user@host#set traceoptions level verbose
Results
From configuration mode, confirm your configuration
by entering the show security alg command. If the output
does not display the intended configuration, repeat the configuration
instructions in this example to correct it.
[edit]
user@host# show security alg
traceoptions {
file trace size 1g;
level verbose;
}
sql traceoptions flag all;
If you are done configuring the device, enter commit from configuration mode.
Verification
Confirm that the configuration is working properly.
- Verifying the SQLNET ALG Control Session
- Verifying the SQLNET ALG
- Verifying the SQLNET ALG Resource Manager Group
- Verifying the SQLNET ALG Resource Information
Verifying the SQLNET ALG Control Session
Purpose
Verify that the SQL command is executed and all the SQL control and data sessions are created.
Action
From operational mode, enter the show security
flow session command.
user@host>show security flow session Session ID: 10880, Policy name: sql, Timeout: 2, Valid In: 30.0.172.12/52315 --> 40.0.172.35/1521;tcp, If: fe-3/0/0.0, Pkts: 6, Bytes: 492 Out: 40.0.172.35/1521 --> 30.0.172.12/52315;tcp, If: fe-3/0/1.0, Pkts: 4, Bytes: 227 Session ID: 10881, Policy name: sql, Timeout: 1800, Valid Resource information : SQLV2 ALG, 5, 18 In: 30.0.172.12/45944 --> 40.0.172.35/1114;tcp, If: fe-3/0/0.0, Pkts: 18, Bytes: 4240 Out: 40.0.172.35/1114 --> 30.0.172.12/45944;tcp, If: fe-3/0/1.0, Pkts: 15, Bytes: 3989 Total sessions: 2
Meaning
Session ID—Number that identifies the session. Use this ID to get more information about the session such as policy name, number of packets in and out.
Policy name—Policy name that permitted the traffic.
In—Incoming flow (source and destination IP addresses with their respective source and destination port numbers, session is TCP, and source interface for this session is fe-3/0/0.0).
Out—Reverse flow (source and destination IP addresses with their respective source and destination port numbers, session is TCP, and destination interface for this session is fe-3/0/1.0).
Verifying the SQLNET ALG
Purpose
Verify that the SQLNET ALG is enabled.
Action
From operational mode, enter the show security
alg status command.
user@host>show security alg status ALG Status : DNS : Enabled FTP : Enabled H323 : Disabled MGCP : Disabled MSRPC : Enabled PPTP : Enabled RSH : Disabled RTSP : Disabled SCCP : Disabled SIP : Disabled SQL : Enabled SUNRPC : Enabled TALK : Enabled TFTP : Enabled IKE-ESP : Disabled
Meaning
The output shows the SQLNET ALG status as follows:
Enabled—Shows the SQLNET ALG is enabled
Disabled—Shows the SQLNET ALG is disabled.
Verifying the SQLNET ALG Resource Manager Group
Purpose
Verify the total number of resource manager groups and active groups that are used by the SQLNET ALG.
Action
From operational mode, enter the show security
resource-manager group active command.
user@host>show security resource-manager group active Group ID 1: Application - SQL ALG Total groups 677, active groups 1
Verifying the SQLNET ALG Resource Information
Purpose
Verify the total number of resources and active resources that are used by the SQLNET ALG.
Action
From operational mode, enter the show security
resource-manager resource active command.
user@host>show security resource-manager resource active Resource ID 2: Group ID - 1, Application - SQL ALG Resource ID 1: Group ID - 1, Application - SQL ALG Total Resources 4044, active resources 2