Toad for Oracle – How To Use Toad over an SSH tunnel

Ever had the worry that when you connect to a database to send some data to it, that some eavesdropper on the network might just be waiting to catch all your passwords (Oracle sends passwords in plain text – by default – go figure) and sensitive data like Credit Card numbers and security codes, bank account details etc?

You have two options, you can ask SQL*Net to encrypt the network traffic, but that then applies to everyone, or you can set up your own system whereby you can tunnel your SQL*Net traffic over an SSH link.

Assume the following:

• Database = perf10g and Oracle Names/Tnsnames.ora has this alias.
• Host = greenbird.
• Port = 1521.
• You have Putty installed.

The process to set things up is as follows.

Edit Tnsnames.ora: You need a new alias for the SSH version of the database, so add the following to your tnsnames.ora. Don’t worry if you are using Oracle Names:

perf10g_ssh.WORLD =
  (DESCRIPTION =
    (SOURCE_ROUTE = OFF)
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 9999))
    )
    (CONNECT_DATA =
      (SID = perf10g)
      (SRVR = DEDICATED)
    )
  )

Note the change in host and port.

Edit Sqlnet.ora: You must be sure that sqlnet.ora will actually look in tnsnames.ora instead of just Oracle Names, so check for the following:

NAMES.DIRECTORY_PATH= (onames, tnsnames)

Obviously, if you don’t use Oracle Names, the onames part is irrelevant.

Run Putty: If you try to tnsping perf10g_ssh or connect to it, you will get a “No Listener” error. This is because the SSH tunnel is not yet set up and there isn’t a listener on localhost:9999.

In a dos command window, type the following – this assumes that Putty is on your %PATH%:

ssh -N -L 9999:greenbird:1521 username@server

The above causes any traffic on localhost:9999 to be forwarded via SSH to port 1521 on server Greenbird. This is where you have a listener already running.

Username@server is simply the username and server name of any server to which you have access via SSH. dba@bluebird will work, for example. You will need to know the password for the chosen username though.

The SSH session will open and prompt you for a password in the normal manner. Type it in and the tunnel will open. You will also have an SSH session connected to username on servername, but because of the -N option, you can’t actually type any commands into it.

Now if you tnsping perf10g_ssh or connect to it using Toad, SQL*Plus or any application, you will send all your data via SSH to the listener and no-one will be able to eavesdrop on what you are sending.

If you have already set  up your SSH keys in the username on servername than you won’t even be prompted for a password

If you remove the -N option from the command line, you will login as usual but you will be able to type commands. However, once you have made your database connection (to perf10g_ssh) you can type the exit command into the SSH session and it will begin the logout process. Until such time as you exit from the Oracle session, it will sit there unmoving. Once you disconnect from Oracle, the logout will complete and the tunnel will vanish.

Remember to connect to a username@perf10g_ssh to use the SSH tunnelling.

Putty Setup