Why should we use SSH tunnel?The answer is simple: in many reasons. For example:
– we are behind the firewall or
– we do not want to (or not allow) open a port or
– the service was designed to be only use internally or
– just our architecture determinates that we have to use it.
If your task meet one of them, this post will be for you.
I do not want to explain the SSH tunnel background, because I don’t know every small aspect of this topic and also I just would like to focus the some useful working examples (best practices). With Google search everyone can do magic 🙂
1. With winscp transfer data to target (remote) host using putty forwarding
In this scenario we are using Windows application like WinSCP to transfer data to the remote system. We use the remote system SSH services to do this. Because of we do not have direct access to the remote machine in some reason, we use middle server what we call jump server. Important to know where the service is (hostname) and what port is use (listen on). Anyway it is generally true 🙂
– open putty session to the jump server (1.)
– set putty SSH tunnel local port forwarding (2.)
We set the “Source port” to 22222. We can use any free port what is free in our local computer, because it will open a listener. The destination is where we want to forward the local connection. In this case we use the remote destination what is accessible from jump server. The format is: destination server and the port where the remote server listens on.
We are able to check the putty how open the tunnel, the port is listening or not. We have two different way, and of course it is enough just check in one way 🙂
Using Windows command line:
Using Putty log window:
– open WinSCP session using local listener (what is forwarded to the target service) (3.)
Here we will connect to the local port what is forwarded to remote host. The username and password must match with remote service access credentials.
2. Create Oracle client connection using SQLNet
– open putty session to the jump server (1)
– set putty SSH tunnel local port forwarding (2)
Here the first tunnel is from local computer to the jump server. First we are going to forward the local connection to the jump server. That’s why we use for destination address 127.0.0.1, but localhost – in this case – means jump server itself and not the local Windows machine. The destination port matches with the jump server listener port what we are going to open in the next step.
– on the jump server open SSH session to the target service using local port forwarding to the target Oracle service (3)
We use the following syntax:
ssh [remote host] –L [local listening port]:[remote destination ]:[remote listening port]
– Remote host: what we use to build the second SSH tunnel (basically connect to the SSH service)
– Local listening port: is where the jump server will be listening the data what have to push toward the remote service
– Remote destination: is where the remote service is use, in our case this address is ORACLE listener address what is accessible locally.
– Remote listening port: where the remote service is listens on
… and some useful SSH setting if we want to build just the SSH tunnel in background:
– n option tells SSH to associate standard input with /dev/null,
– N option tells SSH to just set up the tunnel and not prepare command stream
– T option tells SSH not to allocate a pseudo-tty on the remote system (And what does it means? It is long story 🙂
– f option tells SSH to go into the background just before it executes the command
We are able to check the putty how open the tunnel, the port is listening or not
Using Windows command line:
And also we are able to check how is working the listener in jump server:
– open Oracle client and test the connection (4)
Here like in WinSCP window before, we connect to the local port what firstly will be forwarded to the jump server local port, what secondly will be forwarded to the remote port, where the ORACLE listener listens on. The other ORACLE specific parameters must be same as the original SQL connection.
3. Transfer data between two remote host using local and revers SSH tunnel
– open putty session to the jump server and connect toward to the target machine where we use SSH server service. On the Windows putty connection is not necessary set any extra option or port forwarding. (1)
– Within this new putty session we open a remote session to target server. (2)
This session open listening port on the jump server what we are going to use with SSH tunnel. The parameters are same as we discussed earlier. Do not forget, here the 127.0.0.1 IP address is destination host localhost where we connected. It is accessible locally or we can use the interface address as well if we wish.
If it is necessary we can check the listener on the jump server
– open putty session to the jump server and connect toward to the source machine where we will connect to the target server. (3)
This session open listening port on source server what we are going to use with revers SSH tunnel. Here the most important parameter is “-R”, this means that the given port (here it is: 12345) on the source (here the server is: 126.96.36.199) host is to be forwarded to the given host (here it is: 127.0.0.1) and port (1234) on the local side. The local side in this content is the jump server. On the jump server we has already opened a local host forwarding connection to the target (destination) server, so our data flow will be pushed easily to the target destination.
If we want to check the source size listener we can do same way how we did before on the jump server. In some cases it is useful (e.g. debugging) and help to understand the reveres tunnel method.
– using the scp program via the revers SSH tunnel, we are transferring some data file …
Here I have to mention two important point:
– First is we must use the -P option for scp because we use different port (tunnel port) not the default 22 SSH port for file transfer.
– Second is when we connect the remote host we have to use the target SSH service authentication user and password. The authentication always happens and of the tunnel 🙂 and very useful to use keys (authenticating with public key)
4. Create Oracle SQLNet connection between two remote database using local and revers SSH tunnel
This method is very similar then pervious one, but here we want to use Oracle service between two remote servers. We can use in many way like:
– use Oracle client (JDBC/OCI/Thin/…) on source server to get access on the target database
– use Oracle DBLINK method from source database to target database
– use Oracle client utility (DATAPUMP/RMAN/tnsping/…) on the source server to connect target database by SQLNet
One important question is how the connection string will look like (the naming method). We will discuss about later, but first the big picture:
– Open two putty sessions to the jump server one of for connects toward to the source machine, one of for target machine. (1) Important to note, until this sessions keep alive, we have tunnels. If any reason one of the sessions will close, the tunnel will be broken automatically.
We should consider some solution to avoid this problem like use SSH options and put in background or use one of the most useful utility in the command line world: screen :). For example open the tunnel sessions in screen window, what we can detach and reattach on demand. For more info see screen man pages.
– Open local port forward SSH tunnel on jump server (2)
This session open listening port (11521) on the jump server what we are going to use with SSH tunnel. The parameters are same as we discussed earlier. Do not forget, here the 10.192.43.16 IP address is what ORACLE listener use. The 1521 is ORACLE listener port.
And as usual we can check port listening check on jump server.
– Open revers port forward SSH tunnel on jump server (3)
This is just simple technical step it is very similar like in pervious scenario. On the source server we use dedicated (number: 21521) port, which is would be important to remember when we define the oracle connection string. The whole SQLNet communication starts here.
And as usual we can check port listening check on source server.
– Test the tunnel using tnsping (4)
As I mentioned beginning of this scenario we can use SQLNet connection for many reason. Here I choose tnsping ORACLE utility to test the connection as simple as possible. But we should follow the same way if we connect the target database using sqlplus. Also if we use or define tnsnames.ora file for name resolving we need same connection string method.
Basically every ORACLE SQLNet connection (connection string) needs three data:
– Host where the service running, which here is the tunnel itself. That’s why we use localhost what is forwarded to jump server, what is forwarded to target server.
– Port where the service listen on. Here 21521 what is port forward to the 11521, what is port forward 1521. 1521 is ORACLE listener port on target server.
– Service name what is very same as everywhere 🙂
Obvious, The ORACLE username and password wouldn’t touched, we use what we have already known.
Happy End! Thank you for reading my post, have nice day!