Use SSH Tunnel to connect to Oracle Autonomous Data Warehouse

Why

Before we start to introduce how to use SSH Tunnel to connect to Oracle Autonomous Data Warehouse instance, I think we should explain why we have to do so. Because in some cases, the user's client machine is in a company network, and for security consideration, this kind of network doesn't allow any external access or just open very limited port, such as the SSH port 22.

In such case(SSH port 22 is open), if the user needs to access ADW Instance, we could build an SSH tunnel to make the connection.

Preparation

In this post, I assume you already have an ADW instance with name ADWDVD2 and you are trying to make a connection to this instance from the Oracle Data Visualization Desktop (DVD). Unfortunately, due to the reason I mentioned above, you are only allowed to access the SSH port 22. At least for now, the port we use to connect to ADW instance is fixed to be 1522, that means you can not access the ADW instance directly.

The lucky thing is that we still have SSH port 22 being open, therefore we could build SSH tunnel to forward all of our traffic. To make an SSH tunnel, We need to meet 3 prerequisites.

1. SSH Port 22 is open for the client machine. This is the assumption of this post.
2. An SSH client is installed on the client machine. We use Git Bash to install the SSH client.
3. An SSH Server which can communicate with the target. Assuming we already have a compute instance in OCI(Oracle Cloud Infrastructure) to work as the SSH server.

Note: in this post, we assume the client is Oracle Data Visualization Desktop, but SSH tunnel works well for any client which is facing this kind of network issue.

Install the SSH Client

Although Git Bash is developed to provide better and consistent user experience of Git for Windows user, it brings us most of the *NIX tools including SSH.

Installing of Git Bash is quite simple, just download the installation package from the official website and install it. Same as what you always do with any other Windows application.

Once you installed the Git Bash, you could start to use it by clicking the Git Bash icon in your Start menu. Then the bash terminal will open and you could check the version of bash and SSH client by issuing following commands.

$ bash --version
$ ssh -V

The Compute Instance in OCI

This compute instance will work as the SSH server to receive our traffic from the client and forward all of the traffic to the target ADW instance.

Following are the necessary information of the compute instance.

• Public IP, you could get it from the OCI web console
• A valid user who can access this server with SSH public key authentication. Such as the default user opc

Make the connection to ADW instance through SSH Tunnel

What we should see if the network is not limited

If the network is not limited, we should be able to create a connection to the ADW instance without any problem.

Create connection

Select [Oracle Autonomous Data Warehouse Cloud]

Input the necessary information

[Client Credentials] is the wallet file which you could download from the ADW web console. Please refer to below screenshot.

[Username] is the valid user you will use to connect to ADW. Here we use the default user admin

[Service Name] is the database service you are going to connect to. Please select the service properly base on your workload type.

Connection created successfully

Yes! this is the expected behavior!

What if only SSH port 22 is open

For test purpose, the client machine we are using for DVD is an OCI compute instance with Windows 2012 R2 and it is placed in Tokyo region. The ADW instance is deployed in Ashburn region.

Now let's modify the Egress Rules of the security list as shown below to block all the outbound traffic except through port 22.

Try to create a connection in DVD again. As expected, we get an error "Failed to save the connection".

It's not a surprise, because the connection of host and port to ADW instance is specified in the tnsnames.ora file which is included in the wallet file.

According to the Engress Rules we set, only outbound traffic to port 22 is allowed. When we try to create a connection to ADW instance, the client(DVD) will refer to the specified wallet file and use the connection information in tnsnames.ora file, that means the client will send an outbound request to the host and port specified in the tnsnames.ora file. Obviously, it will fail, since the request to port 1522 will be blocked.

Create SSH Tunnel

Then, how to resolve this problem? Maybe you could contact the network administrator and request to open the port 1522? But this is related to network security, and this kind of network change will impact multiple users in the same network. So I don't think your request will be approved easily.

SSH Tunnel will save you here since the port 22 is open. Actually, we already prepared everything for building an SSH Tunnel, we only need to open Git Bash terminal and issue a command as below to start it. You will get an SSH Tunnel to the SSH server(the compute instance in OCI)

$ ssh -i path-to-the-private-key -N -L local-port:target-host:target-port user@SSH-server-IP

I know that you are still feeling confused, below image explains the meaning of each option in this command.

Below is the command I executed.

adb.us-ashburn-1.oraclecloud.com:1522 part is easy to understand, that's the target host and target port. We want all of our traffics will be forwarded to this port(1522) on the host(adb.us-ashburn-1.oraclecloud.com).

opc@SSH-server-IP part is the SSH server you are going to log on to.

11522 What's this?? According to the explanation above, it is the given port on the local (client) host is to be forwarded to the given host and port on the remote side. It is a port on the localhost. Actually, we can use any port except the reserved ones. In this case, we use port number 11522.

Once we issued this command, you should see that the terminal is hanging there and you cannot issue other commands. That means the SSH tunnel has been built and you can use it now. You can use the option -f to put the tunnel at the background, but I think it is easier to close the SSH tunnel if it is at the foreground. You just need to press Ctrl + C or click the X on the terminal window.

Now the SSH tunnel is ready, in the client machine, any traffic to port 11522 on the localhost(127.0.0.1) will be sent to the SSH server, and the server will forward all the traffic to the target port on the target host.

Create connection through SSH Tunnel

Remember the tnsnames.ora file which will be referred by the client? We just need to edit it and force the client to send all traffic to the localhost on port 11522, then the SSH tunnel will handle everything.

As shown in below screenshot, for the service adwdvd2_high, host and port are updated to be 127.0.0.1 and 11522.

Create a connection in DVD and specify the Service Name as adwdvd2_high

The connection created successfully

We can also create Data Set through this connection.

Although we know it will fail, we still have a test to the false case. Let's try to create a connection through the Service Name adwdvd2_low which is not using the SSH tunnel.

The SSH Tunnel works very well. Anytime you need to connect to the ADW instance to create some amazing reports, just start an SSH Tunnel and you can fly.

Clean up

Press Ctrl + C to terminate the SSH tunnel, or click X on the Git Bash terminal window, you will get back to normal.

References

• Oracle Data Visualization Desktop
• Git Bash
• Explain Shell

Other language versions

• English
• Chinese
• Japanese