How can I connect to Oracle Database 11g server through ssh tunnel chain (double tunnel, server in company network)?

Question

I have SSH access to 'public' server, which is also the gateway to company network. There is another server in the network, where local Oracle Database server is running (There is no access from outside of this server, only localhost DB connections are accepted). And of course, I have another SSH access to this server.

Is there any way to join to this Oracle Database 11g Server from outside of the network ? I am asking if there is something like ssh tunnel chain, and how i configure it. This can be usefull, for example, for TOAD for Oracle (ORACLE client).

EDIT: Here is image

Thanks

Cordellcorder

Answer 1

Yes, it's possible. E.g. on Linux, run

ssh -N -Llocalport:dbserver:dbport yourname@connectionserver

where

• localport is the port on your machine which will be forwarded (can be 1521 if there is no local instance of oracle running)
• dbserver is the name or IP of the database server
• dbport is the port of the database (usually 1521)
• yourname is the login on the connectionserver
• connectionserver is the machine where you have ssh access

The same can be done on Windows using Plink (which comes with Putty):

plink -N -L localport:dbserver:dbport yourname@connectionserver

Do this on both machines (your local machine and the server you have access to) to chain the ssh tunnels. Example:

Connection server (assuming Linux):

ssh -N -L1521:dbserver:1521 dblogin@dbserver

Your PC:

plink -N -L 1521:connectionserver:1521 connlogin@connectionserver

The tnsnames.ora entry must look like you are running a local database, e.g.

prodoverssh =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prod)
    )
  )

Milkandwater

I am facing very similar setup, but now the gateway on "my side" (in the green circle). I would also like to tunnel communication to oracle server. However, I cannot use yoursuggestion, because I am not administering "ORACLE SERVER", just the "GATEWAY" on the network schema in the original question. I can only communicate with the Oracle server on the "ORACLE SERVER" machine (listening on 1521) from "GATEWAY", clients are installed on both "GATEWAY" and "MY PC". Is there some solution for me? – Tolyl

j_maly: If the DB server accepts connections from the gateway, it's even easier. Just run plink -N -L 1521:dbserver:1521 gwuser@gateway from your PC. – Milkandwater

Thanks for the reply. I tried your suggestions, but I get ORA-12170: TNS:Connect timeout occurred error. This is what I did: 1) I set up an ssh server on gateway (and I verified that I can connect using putty) 2) I ran the command as you suggested (plink asks for password on gateway, I enter the password, plink does not say anything else -- should it output some kind of "ok" message?) 3) the IP of 'dbserver' is only recognized on 'gateway', not on my local pc, I hope that should not be a problem. 4) I copied the TNS entry from gateway to my local PC (without changes) – Tolyl

Most likely it's the TNS entry. On your local PC, it must point to localhost, since this is the entry to the tunnel! – Milkandwater

In this kind of chained tunnel configuration, make sure that the addresses used in the tunnel correspond, and don't simply belong to different interfaces of the same machines. See superuser.com/a/346982/285945 for more details. – Jansen

If looking to do this on Windows with PuTTY GUI vs plink, you can check out the answers here serverfault.com/questions/340865/… – Glaswegian

Hello, where would you put the passwords? I'm trying to make my local instance of sqldeveloper connect to the oracle server via the ssh port (22) – Puklich

@Puklich This kind of port forwarding does not require knowledge of the oracle passwords. You need ssh passwords for the connection, though. You can use the authorized_keys file of ssh to allow the connection without having to enter the password every time. – Milkandwater

Answer 2

Thanks!

I called ssh -N -LXXXX:server:YYYY login@server twice.

First, I called

ssh -L 9998:127.0.0.1:9997 [email protected]

on my PC.

Then, on this server (during the SSH session), I called

ssh -L 9997:localhost:1521 [email protected]

where 192.168.105.111 is server where ORACLE was running.

So what I did is following redirection:

1521 (COMPANY ORACLE SERVER) 
  -> 9997 (COMPANY GATEWAY SERVER)
     -> 9998 (LOCAL PC)

So I got ORACLE access in my local PC at port 9998 !

Cordellcorder

Answer 3

I had the same issue as @j_maly here in comments, it is possible to connect using just one tunnel to the "GATEWAY" doing this in "MY REMOTE PC" (linux):

ssh -L 1521:DATABASE_URL_OR_IP:1521 USER@GATEWAY

But after getting ORA-12170: TNS:Connect timeout occurred over and over again. I've done this:

• In GATEWAY, connect something against oracle that works, in my case, sqldeveloper (sqlplus should work too). Let's guess the oracle working url in sqldeveloper is database.company.ex

• In GATEWAY run netstat -putan | grep 1521, and here is the issue:
  I found that connection has other database ip and domain than the previous known and supposedly connected (database.company.ex).

• So You should put the ip or url showed in the result of netstat -putan | grep 1521 in "GATEWAY"

  ssh -L 1521:DATABASE_URL_OR_IP_SHOWED_IN_NETSTAT:1521 USER@GATEWAY

I don't know why this happens exactly, but I found several ips to connect to the same database, and I can't connect from some of them, to solve the problem we must find the correct ip.

It's important to mention that you could also change the local port (11000 in this case) and run this command with no output (-fN)

ssh -fN -L 11000:DATABASE:1521 USER@GATEWAY

Literal

This is gold, thank you! – Insight

Answer 4

you can add as well the -f option which run the ssh command in the background.

Amylopectin