Accessing Secure Database Environments using Port Tunneling

In my day-to-day work, I have found that many database developers are frustrated for not being able to connect to the database server on linux/unix environment from their windows desktop due to new security imperatives imposed by network managers by not opening the ports on the server to access except from the a specific environment ( Here linux development environment with ssh  server) .

These restrictions cause significant bottlenecks to productivity due to lack of GUI tool to use by developers while developing database code.

While database developers are technically literate, network services setup and firewalls are not a normal part of their knowledgebase.

This post describes the setup of a SSH tunneling to allow the connection from windows desktop to the database server environment (Linux/Unix ).

Here, I have installed ssh client on windows using CYGWIN+OPENSSH installation on your windows environment. We also use the Putty client as ssh client tool.

We need three things to work the below mentioned approach ( Most of the organisations have the this setup to access their databases with in LAN itself)

1. Windows Desktop (Development Desktop )

2. Linux/Unix Development Environment ( SSH server)

3. Oracle Database Server ( ORACLE Database Hosting Server)

Developer is able to connect the ORACLE database from Linux Development Environment . ( Database Host firewall permits access from linux Development BOX)

But when developer tries to connect the database using SQLPLUS or Sql Developer or any other client from Windows Desktop , the request fails with error "Connection Timeout Error".

Here is the sample TNS entry :
xdb=(DESCRIPTION= (ENABLE=BROKEN) (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=databaseserver.test.com)(PORT=64187))) (CONNECT_DATA= (SID=xdb) (SERVER=DEDICATED)))

C:\Users\vireddy>sqlplus username@xdb

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 4 04:44:09 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-12170: TNS:Connect timeout occurred

Reason for the failure is Database Firewall is not permitting the windows desktop to establish the direct connection with database server port.

We can connect to the database server by having Tunneling in place using Putty or CYGWIN+OPENSSH installation on WINDOWS environment.

Open ssh client on windows environment and execute the command line

ssh -L 64187:databaseserver.test.com:64187 vireddy@linuxdevelopment.test.com -N

In the above command , we are forwarding the request on local port 64817 to database server port at 64817 using ssh server on linuxdevelopment box.

Now, we can are able to connect the Database from the Windows environment

xdb=(DESCRIPTION= (ENABLE=BROKEN) (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=64187))) (CONNECT_DATA= (SID=xdb) (SERVER=DEDICATED)))

C:\Users\vireddy>sqlplus username@xdb

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 4 04:45:58 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options


We can also add multiple port forwards using the command line as shown below


ssh -L 64187:databaserver.test.com:64187 -L 64150:Appserver.test.com:64150 vireddy@linuxdevelopemt.test.com -N

General command line format for using ssh client is

ssh -L localport:host:hostport user@ssh_server -N

where:
-L - port forwarding parameters (see below)
localport - local port (chose a port that is not in use by other service)
host - server that has the port (hostport) that you want to forward
hostport - remote port
-N - do not execute a remote command, (you will not have the shell, see below)
user - user that have ssh access to the ssh server (computer)
ssh_server - the ssh server that will be used for forwarding/tunneling

 +----------+<--port 22-->+----------+<--port 64817-->o-----------+
   |SSH Client|-------------|ssh_server|-------------|   Server    |
   +----------+             +----------+             o-----------+
  windows Box:64817           Linux Box              Database Server:64817

We can do the same setup using Putty client as mentioned below

Open the Putty tool and enter the details as mentioned below to connect the SSH server ( It's runing on linux development environment)

Enter the port forwarding as details as

Click Add button to add the port forwarding to be effective ( If we open another putty clent instance , we will be loosing the setting. We can persist it by going to Session tab and clicking on save button).