Tuesday, 17 November 2020

OCI-SSH Tunneling to ATP DB

 Connect to Autonomous Database Private Endpoint from On-Premise SQL Developer using SSH Local Port Forwarding

Assumptions:

  1. Your Bastion/ServiceVM/Bastion Host is “140.x.x.16” using Private Key “/Users/shadab/Downloads/Oracle Content/Keys/mydemo_vcn.priv” which is copied and available on the on-premise client machine
  2. Your Autonomous Database Private Endpoint IP is “10.10.2.11” running over port 1522
  3. There is connectivity from Bastion Host “140.x.x.16” to Autonomous Database “10.10.2.11” running over port 1522
  4. Your Autonomous Database wallet zip file is available on the on-premise client machine

Connect :
— From the On-Premise Client Machine Add the Hostname of the Autonomous Database which is in the tnsnames.ora file from your wallet file —

eg:
atpocipaas_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=hogmsumb.adb.ap-sydney-1.oraclecloud.com))(connect_data=(service_name=pjjuahqavguuilt_atpocipaas_low.atp.oraclecloud.com))(security=(ssl_server_cert_dn=”CN=adb.ap-sydney-1.oraclecloud.com,OU=Oracle ADB SYDNEY,O=Oracle Corporation,L=Redwood City,ST=California,C=US”)))

$ sudo vi /etc/hosts
127.0.0.1 hogmsumb.adb.ap-sydney-1.oraclecloud.com localhost

Now create a SSH tunnel with local port forwarding to forward local port 1522 to remote host 10.10.2.11 and remote port 1522

$ ssh -fNT -v -L 1522:10.10.2.11:1522 opc@140.x.x.16 -i “/Users/shadab/Downloads/Oracle Content/Keys/mydemo_vcn.priv”

Check with telnet

$ telnet hogmsumb.adb.ap-sydney-1.oraclecloud.com 1522
Trying 127.0.0.1…
debug1: Connection to port 1522 forwarding to 10.10.2.11 port 1522 requested.
debug1: channel 3: new [direct-tcpip]
Connected to hogmsumb.adb.ap-sydney-1.oraclecloud.com.
Escape character is ‘^]’.

Now connect with SQL Developer using option ‘cloud wallet’ using any one of the TNS entry and using the “ADMIN” user with which you created the Autonomous Database or any other DB user