Posted by : Arjun Lagisetty Monday, April 10, 2017

Scenario: 

When a database is hosted on a public cloud, security policies often prevent the admins from exposing direct db port i.e. 1521 to the world. In this scenario, admin asks oracle developers to use ssh tunneling a.k.a port forwarding to connect to the database from our ODI Studio. So, how we do this?

High-Level Steps:

  1. You need to have ssh access to the database server in addition to DB login credentials. (One time)
  2. Download and install putty (One time)
  3. Create putty profile with port forward called mysshprofile  (One time)
  4. Connect to putty and launch a session with mysshprofile. (Before every login)
  5. Create a new connection pointing to local port in ODI studio (One time)
  6. Launch ODI Studio and connect to the remote database.


The Nitty Gritty:


How to download and install putty?


How does port forwarding work?


How connect to the remote database using port forward?



Assumptions:

Remote database host IP: 192.168.205.205
SSH Port: 22
Unused local port on localhost: 9000
Remote database port: 1521
Remote database sid: orclsid


Connecting to remote server, creating a tunnel and creating a putty profile:

  1. Open Putty and enter IP address  and SSH port

  1. Navigate to Tunnels menu on the right-hand side.

  1. Add Source Port, this is a local port which listens for traffic and forwards it to remote DB port. This port should not be used by any system, I used 9000 port. Add Destination in  Remote IP: DB Port format (See assumptions)

  1. Click on Add button

  1. Go back to session tab on left menu and add profile name "tunnelremotedb" in the Saved Sessions field

  1. Click save and open.
  2. You will now be prompted to enter username and password: Please enter ssh login credentials which you got from DBA
  3. Completing these steps will create a successful tunnel into the remote server. Please note, if you close the ssh session you will close the tunnel.
  4. Subsequently, you can connect to the database using putty saved session. You will not need to repeat steps 1-5

Connecting to ODI repository from ODI Studio:

Creating a connection to cloud repository and a regular database have very similar steps but the major different would be the jdbc URL.
"jdbc:oracle:thin:@localhost:9000:orclsid"
Where 9000 is the Source port entered in Step 4 of the putty connection section.

Putting it all together:

Once the setup is done you should
  1. Create SSH connection
    1. Open putty
    2. Load the saved session
    3. Log in to the remote server.
  2. Connect to ODI repo using MyRemoteRepConnection

In the next installment, we will talk about how to automate putty connection using plink

Leave a Reply

Subscribe to Posts | Subscribe to Comments

Popular Post

Labels

Blog Archive

Copyright © ODI Pundits - Oracle Data Integrator - Maintained by Arjun Lagisetty