SOA Cloud Service - Quick and Simple Setup of an SSH Tunnel for On-Premises Database Connectivity

Executive Overview

With the current release of SOA Cloud Service (SOACS) a common requirement often requested is to connect to an on-premise  database from the cloud SOACS instance. This article outlines a quick and simple method to establish the connectivity between the single-node SOACS instance and the on-premise database server using an SSH tunnel.

Solution Approach

Overview

The overall solution is described in the diagram shown below.

An SSH tunnel is established between the SOACS instance running in the Oracle Public Cloud (OPC) and the OnPrem database server residing inside the corporate firewall. This tunnel is defined with the local port as the TNS listener port of the database server and any suitable remote port on the SOACS instance, that is available and can be used for port forwarding. After the tunnel is established, all the SOACS instance traffic directed to the newly defined port opened on SOACS instance will automatically be forwarded to the database TNS listener. Thus JDBC connections connecting to the port within SOACS instance will automatically connect to the OnPrem database server.

The following sections walk through the detailed setup and shows a composite deployed to the SOACS instance invoking a database adapter retrieving data from a table within the On-Premises database server.

Summary of Steps

• Copy the private key for SOACS instance into the on-premises database server.
• Establish a SSH tunnel session from the DB server to the SOACS VM's public IP. The tunnel will specify a local port and a remote port. The local port will be the TNS listener port of the database and the remote port can be any port that is available within the SOACS instance.
• Define a JDBC datasource using the onPrem database parameters with the exception of replacing the TNS listener port with the remote port defined for SSH tunnel in the last step.
• Test the JDBC datasource connectivity.
• Define a new JNDI entry within Database Adapter that uses the newly created JDBC datasource.
• Use the JNDI in a SOA composite containing a Database Adapter to run a simple query from a database table.
• Deploy and test the new composite for runtime verification of the database query operation.

Task and Activity Details

The following sections will walk through the details of individual steps. The environment consists of the following 2 machines:

• SOACS instance with a single managed server and all the dependent cloud services within OPC.
• Linux machine inside the corporate firewall, used for hosting the On-Premises Database (myOnPremDBServer)

I. Copy the private key of SOACS instance to the database server

When a SOACS instance is created, a public key file is uploaded for establishing SSH sessions. The corresponding private key has to be copied to the database server. The private key can then be used to start the SSH tunnel from the database server to the SOACS instance.

Alternatively, a private/public key can be generated in the database server and the public key can be copied into the authorized_keys file of the SOACS instance. In the example here, the private key for the SOACS instance has been copied to the database server. A transcript of a typical session is shown below.

slahiri@slahiri-lnx:~/stage/cloud$ ls -l shubsoa_key*
-rw------- 1 slahiri slahiri 1679 Dec 29 18:05 shubsoa_key
-rw-r--r-- 1 slahiri slahiri 397 Dec 29 18:05 shubsoa_key.pub
slahiri@slahiri-lnx:~/stage/cloud$ scp shubsoa_key myOnPremDBServer:/home/slahiri/.ssh
slahiri@myOnPremDBServer's password:
shubsoa_key                                                                                100% 1679        1.6KB/s     00:00
slahiri@slahiri-lnx:~/stage/cloud$

On the database server, login and confirm that the private key for SOACS instance has been copied in the $HOME/.ssh directory

[slahiri@myOnPremDBServer ~/.ssh]$ pwd
/home/slahiri/.ssh
[slahiri@myOnPremDBServer ~/.ssh]$ ls -l shubsoa_key
-rw-------+ 1 slahiri g900 1679 Jan  9 06:39 shubsoa_key
[slahiri@myOnPremDBServer ~/.ssh]$

II. Create an SSH Tunnel from the On-Premises Database Server to the SOACS instance VM's public IP

Using the private key from Step I, start an SSH session from the on-premises Database server host, specifying the local and remote ports. As mentioned earlier, the local port is the TNS listener port for the database, e.g. 1521. The remote port is any suitable port that is available in the SOACS instance. The syntax of the ssh command used is shown here.

ssh -R <remote-port>:<host>:<local port> -i <private keyfile> opc@<SOACS VM IP>

The session transcript is shown below.

[slahiri@myOnPremDBServer ~/.ssh]$ ssh -R 1621:localhost:1521 -i ./shubsoa_key opc@shubsoa
[opc@shubsoacs-jcs-wls-1 ~]$ netstat -an | grep 1621
tcp        0      0 127.0.0.1:1621              0.0.0.0:*                   LISTEN
tcp        0      0 ::1:1621                         :::*                            LISTEN
[opc@shubsoacs-jcs-wls-1 ~]$

After establishing the SSH tunnel, the netstat utility can confirm that the remote port 1621 is enabled in listening mode within the SOACS VM. This remote port, 1621 and localhost along with other on-premises database parameters can now be used to define a datasource in Weblogic Adminserver (WLS) console.

III. Define a JDBC DataSource for the On-Premises Database

The remote port from Step II is used in defining the JDBC datasource for the database definition port. It should be noted that the host specified will be localhost instead of the actual IP address of the database server, since the port forwarding with SSH tunnel is now enabled locally within the SOACS VM in Step II.

We will define a datasource corresponding to the standard SCOTT schema of the sample database. From here onwards, the process to define a datasource is straightforward and follows the standard methodology. So, only the primary field values that are used in the datasource definition process are provided below.

• Name: OnPremDataSource
• JNDI Name: jdbc/onPremDataSource
• Driver: Thin XA for instance connections (default)
• Database Name: orcl
• Host Name: localhost
• Port: 1621
• Database User: scott
• Database Password: Enter the password as set in the sample database

IV. Test the JDBC DataSource Connectivity

Near the end of the JDBC datasource creation wizard, the Test Configuration button can confirm if the datasource is created successfully, as shown below.

After the completion of the JDBC datasource creation process, another quick check of netstat for port 1621 will show two additional entries indicating an established session corresponding to the newly created datasource. The connections are terminated if the datasource is shutdown from the WLS console.

[opc@shubsoacs-jcs-wls-1 ~]$ netstat -an | grep 1621
tcp        0      0 127.0.0.1:1621              0.0.0.0:*                   LISTEN
tcp       0      0 127.0.0.1:15148            127.0.0.1:1621               ESTABLISHED                 
tcp       0      0 127.0.0.1:1621              127.0.0.1:15148             ESTABLISHED                 
tcp        0      0 ::1:1621                             :::*                        LISTEN
[opc@shubsoacs-jcs-wls-1 ~]$

V. Define a new JNDI entry within Database Adapter that uses the newly created JDBC datasource

From WLS console, under Deployments, update DbAdapter by creating a new JNDI entry (e.g. eis/DB/DemoDB) and associate it with the JDBC datasource created in Step IV. Since this is a standard configuration task for Database Adapter using a new JNDI entry, the details are not included here.

Instead, only the key field values used in the DbAdapter configuration are provided below.

• New JNDI under Outbound Connection Pools: eis/DB/DemoDB
• XADataSourceName: jdbc/onPremDataSource

VI. Use the newly created JNDI to develop a SOA composite containing a Database Adapter to run a simple database table query

The JNDI entry created in step V is used in the Database Wizard session within JDeveloper to run a query against the DEPT table within SCOTT schema of the sample database. The composite SAR used for deployment in SOACS (sca_DBQuery_rev1.0.jar) is available for download here.

VII. Deploy and test the composite

After deployment, composite can be tested by entering a dummy string for input. Results from the DEPT table rows should be visible in the database adapter invoke, as shown below.

Summary

The test case described here is a quick way to establish the connectivity to an on-premises database with SSH tunnels. This can be typically used for development and functional testing purposes. There are limitations in this approach. An improved solution with better stability and applicability to clusters is covered in another blog by Christian Weeks from our team. For further details, please contact the SOACS Product Management or the SOACS group within A-Team.

Acknowledgements

SOACS Product Management and Engineering teams have been actively involved in the development of this solution for many months. It would not have been possible to deliver such a solution to the customers without their valuable contribution. Finally, a big thanks to my team-mate, Christian Weeks, whose technical help always works wonders.