Oracle Developer Cloud Service is a hosted team development and delivery platform with all kinds of tools to help your team be more efficient.  In this post, I will cover how to use an SSH tunnel to connect to your database in a build job.

This is current as of October 2018.

Connecting to your database through an SSH tunnel is fairly simple and you won’t have to ask your network admin to open a port in your firewall and or load balancers.

If you’re connecting to an Oracle Cloud Database it should be pre-configured to allow database connections through an SSH tunnel.  If not check with your server admin for assistance.

If you’re not familiar with SSH tunnels you can find out more here.

However, if you’re reading this post you probably just want to skip to the how-to so let’s get started.

Configure your build job

Open your Developer Cloud Service project, click the ‘Build’ tab and select the build you want to work with.

Click the ‘Configure’ button

Authorized SSH Keys

You will need an SSH Key that has been authorized to connect to the database server.  I recommend generating a new key that will only be used from your DevCs builds.  Name the new key pair something that will let you know it’s for this DevCs project.

Helpful links:

• Generate an SSH Key.
• Add your new public key to your Cloud Database.

If you generated a new key pair named DevCsProj1, you should have two files.  The file without an extension ‘DevCsProj1’ is your private key and the file with .pub ‘DevCsProj1.pub’ is your public key.

Add a build environment configuration

Select the ‘Build Environment’ tab, click ‘Add Build Environment’ and choose ‘SSH Configuration’.

1. Open the private key (from above) in a text editor, copy everything in the file and paste it in the ‘Private Key’ file text area.
2. You can do the same for the ‘Public Key’ text area but it is not required for this example.
3. If you created your keys with a password, enter the password in the ‘Pass Phrase’ field.
4. For a little extra security, you could get the public key for your Database Server and enter it in the ‘SSH Server Public Key’ text area.  This will ensure that your build job only connects to that server and will help protect against connecting to a different server if the IP address is re-assigned.  This field is optional.
5. Check ‘Create SSH tunnel’.
6. Enter the SSH username.  (This is not a database user, this is an authorized SSH user on the server.)
7. If you’d rather use an SSH user/password instead of an SSH Key file, you could enter the password in the password field.  I leave it blank in order to only connect with the keys.
8. Enter the port you want to use on the DevCs side of the tunnel in ‘Local Port’.
9. If you leave ‘Remote Host’ empty it will default to localhost.  The remote host is used on the other side of the tunnel to make the connection as if you were on that machine.  Since I’m intending to connect to the database that is on the same server that I’m SSH’d into I can use localhost.  If I wanted to connect to a different server from that side of the tunnel, I could enter the address for the other remote server.  For example, if I had a server that was only accessible from inside of a network that includes the SSH server, I can SSH into the network and the tunnel will end on the other internal server.
10. If you’d like to re-use your keys for other SSH commands you can check ‘Setup files in ~/.ssh for command-line ssh tools’.  It is not necessary for this example.

The ‘Connect String’ displayed at the bottom shows the ssh command DevCs will use to create the tunnel.

ssh -L localhost:1521:localhost:1521 opc@129.111.111.111

Let’s break it down:

• Create a local ssh tunnel.  ssh -L
• On the local DevCs server map to the ‘Local Port’ value.  localhost:1521 (The first one)
• Once connected to the SSH server map the tunnel end to ‘Remote Host’:’Remote Port’.   localhost:1521 (The second one)
• Make the SSH connection as ‘Username’@’SSH Server’.  opc@129.111.111.111

OK, time to use the new tunnel.  I’ll use a SQLcl Builder.

Add / Edit a builder

Select the Builders tab, click Add Builder and choose SQLcl Builder.

If you’ve used a SQLcl builder before this will all be the same, except for the connect string.

1. Enter your database username in the ‘Username’ field.
2. Enter your database password in the ‘Password’ field.
3. If you’re connecting to a database that is using wallet credentials such as Oracle Exadata Express Cloud Service, enter the location of your Credentials file.  I’m not so I will leave it empty.
4. Since I now have an SSH tunnel in place I will connect to the local (DevCs server) end of the tunnel and use the ‘Local Port’ value from the SSH Configuration.  //localhost:1521/[servicename].  Even if I had defined a remote host and/or port other than localhost / 1521 in the tunnel configuration, I would still use localhost:[Local Port].  The tunnel takes care of the mapping on the other end.
5. Enter the SQL File or Inline SQL you want to run.
6. Save the Job Configuration.

Run it

Click the ‘Build Now’ button and when you look at the console output you will see something similar to this.

• SSH is set up.
• The SSH tunnel is opened.
• SQLcl executed my script.  (I did not enable any output in my script so none is displayed.)
• SQLcl disconnects from my Database.
• The SSH tunnel is closed.
• The SSH environment is removed.

Once you’ve added an SSH Build Environment to your build job and tested it, you can start adding them to each Build Job you use a database connection in.  After that’s done you can close port 1521 on your Database server (assuming you don’t need it for other applications).

Oracle Developer Cloud Service is constantly being improved, so let me know in the comments if this guide becomes out of date and I will update it.