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! 🙂
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
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
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
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
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
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
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
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
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