Introduction
There is no doubt that database servers should always be provisioned in a private subnet. However, in some cases, e.g. test and development, you’d like to connect to your databases over the internet using SQL Developer installed on your local (Windows) computer.
The Environment
My environment for this blog post consists of the following:
- VM DB System provisioned in a private subnet
- Autonomous Database with private endpoint in a private subnet
- Compute VM as a jump server in a public subnet
- SQL Developer installed on my Windows Notebook
SSH Tunnel
The first step is to establish an SSH tunnel using the following command on the local computer where SQL Developer is installed:
ssh -i /path/to/your/private_ssh_key -N -L localhost:<local_port>:<db_private_ip>:<db_port> opc@<jump_server_public_ip>
You can execute this command on Windows machines as well. The path format to your private ssh key depends on the tool you are using:
Command Prompt (cmd)
ssh -i C:\Users\SPETRUS\.ssh\id_rsa -N -L localhost:1521:10.0.2.2:1521 opc@130.61.148.120
Git Bash
ssh -i /c/Users/SPETRUS/.ssh/id_rsa -N -L localhost:1521:10.0.2.2:1521 opc@130.61.148.120
Putty
If you are using Putty, enter the public IP of your jump server and port 22 in the “Session” section
In the “Tunnel” section, enter the local port, the private IP of your database host, and the listener port. Click “Add”.
Go back to the “Session” section, save the changes, and open the connection.
Connecting to Database Cloud Service in Private Subnet
The VM DB Systems’ private IP address is 10.0.2.2. Database listener is using port 1521.
The jump servers’ public IP address is 130.61.148.120.
Using Git Bash to establish the ssh tunnel:
In SQL Developer, use Connection Type “Basic”, enter “localhost” as host name, port 1521, and your database service name. You can use the service name of the CDB or PDB.
Connecting to Autonomous Database with Private Endpoint
My Autonomous Databases’ private endpoint IP address is 10.0.2.3. You’ll find this on the details page of your Autonomous Database:
Database listener is using port 1522. The jump servers’ public IP address is 130.61.148.120.
Using Git Bash to establish the ssh tunnel:
Download the Client Credentials (the zip file) and unzip it into your local folder on your (Windows) machine, in my example in C:\Users\SPETRUS\Documents\spetrus\Cloud\wallets\Wallet_ADBPRIV
Open the tnsnames.ora file and change the “host” value to “localhost”:
In SQL Developer, use Connection Type “Custom JDBC” and enter the following JDBC URL. TNS_ADMIN points to the directory where you unzipped your Client Credentials:
jdbc:oracle:thin:@adbpriv_tpurgent?TNS_ADMIN=C:\Users\SPETRUS\Documents\spetrus\Cloud\wallets\Wallet_ADBPRIV
That’s it!
Keep in mind that your ssh connection to the jump server has to be established all the time.
Do not forget to enable traffic to flow between the public and private subnets by defining the appropriate security list rules in your VCN.
Database Heartbeat 