Connecting to OCI DB System with SQLDeveloper via Bastion Box

IT by javier mugueta27 April, 20196 March, 2020

Recipe for creating a secure connection between sqlDeveloper in our local machine and an Oracle Cloud Infra DB System created in a private subnet of a Virtual Cloud Network network not opened to internet

Steps

Create a Virtual cloud network (VCN) with, at least 2 subnets, one public and the other private
Create a bastion host VM in the public subnet
Create an internet gateway in the VCN
Create a route for the public subnet so that the traffic to 0.0.0.0/0 goes through the internet gateway (no other routes are needed, routing between both subnets is enabled by default in the VCN)
Create a new DB System in the private subnet and grab the private IP of the database system node

Create a compute VM with public IP exposed
In your remote client machine open a ssh tunnel this way:

ssh -i privatekeyfile -N -L localhost:1521:dbnodeprivateip:1521 opc@jumpboxpublicip

Grab the database connection details

Create a connection in sqlDeveloper

Test the connection

Hope it helps! 🙂

10 Comments

Chiranjib NandyChiranjib on 6 March, 2020 at 1:02 pm
Reply

Hi Jav,Whenever I need to access OCI DB in private subnet through sql developer I need to create the tunneling right ?

SSH tunneling command you mentioned (ssh -i privatekeyfile -N -L localhost:1521:dbnodeprivateip:1521 opc@jumpboxpublicip ) where do I need to execute that ?Do I need to run this bastion compute VM (Bastion Jumpbox)?

LikeLike
1. javier mugueta on 6 March, 2020 at 8:07 pm
  Reply
  
  Hi, I’ve updated the post with more instructions. You need to create a VCN with one public and one private subnets, then create the dbsystem in the private and a bastion vm machine in the public.
  
  LikeLike
Chiranjib Nandy on 6 March, 2020 at 1:03 pm
Reply

Hi Jav,Whenever I need to access OCI DB in private subnet through sql developer I need to create the tunneling right ?

SSH tunneling command you mentioned (ssh -i privatekeyfile -N -L localhost:1521:dbnodeprivateip:1521 opc@jumpboxpublicip ) where do I need to execute that ?Do I need to run this bastion compute VM (Bastion Jumpbox)?

LikeLike
Rajith on 8 October, 2020 at 11:26 am
Reply

I followed the above steps but still I am not able to connect. Please help.
1) created bastion host (linux vm) in public subnet using pvt1.key
2) created DB system in private subnet using pvt2.key
3) created Internet Gateway and associated with Public subnet
4) created NAT Gateway and associated with Private subnet
5) created SL for public and private subnets respectively and opened port 22 and 1521
6) connection from local to bastion host can be done using putty via pvt1.key
7) connection from bastion to DB can be done via pvt2.key (copied this pvt2.key to bastion host)
8) SSH tunneling command you mentioned (ssh -i privatekeyfile -N -L localhost:1521:dbnodeprivateip:1521 opc@jumpboxpublicip) I am executing it in bastion host but it is throwing me error as permission denied.
Any help would be much appreciated.

LikeLike
1. javier mugueta on 8 October, 2020 at 7:45 pm
  Reply
  
  Hi, dont run it bastion, do it in your local computer. The idea of this construct is to reach the database iver internet in a secure way
  
  LikeLike
  1. Rajith K on 9 October, 2020 at 11:31 am
    Reply
    
    Thanks I am using windows local machine. From putty I am doing SSH tunneling but still not able to connect from SQL Developer. Error I am getting is Status : Failure -Test failed: Listener refused the connection with the following error: ORA-12514, TNS:listener does not currently know of service requested in connect descriptor.
    
    LikeLike
    1. javier mugueta on 11 October, 2020 at 11:05 pm
      Reply
      
      Seems like the name of the service you have configured is not correct, the format is as follows: [database unique name].[fqdn of the subnet in which the database is wired into], for example: mydb_xyzt.myprivsubnet.myvcn.oraclevcn.com
      
      LikeLike
Rajith on 8 October, 2020 at 2:09 pm
Reply

SSH tunneling command you mentioned (ssh -i privatekeyfile -N -L localhost:1521:dbnodeprivateip:1521 opc@jumpboxpublicip ) where do I need to execute that ?Do I need to run this bastion compute VM (Bastion Jumpbox)?

LikeLike
1. javier mugueta on 8 October, 2020 at 7:42 pm
  Reply
  
  yes, create in oracle cloud a vm on a public subnet in the same vcn where the database was created, and set it to have a public ip, create and associate a keypair to the vm and then issue the command putting the real value of the public ip assigned after vm creation and the file containing the private key, grab also the private ip of the database listener and
  
  LikeLike
adhikarexuss on 25 January, 2021 at 2:43 am
Reply

This is a good article javier and I thank you for that.
I tried the same thing as well in Windows 10, but apparently for Windows, we need to generate another key pair that is supported by windows 10 then the tunneling will work.Windows 10 currently only supports ed25519 keys.

LikeLike