30 SQL Developer Tips in 30 Days, Day 17: Using SSH Tunnels

thatjeffsmith SQL Developer 24 Comments

Tell Others About This Story:

You want to connect to the database, but:

  • you can’t get to the server directly, or
  • you can’t talk to the listener port

If ONLY there were a way you could smuggle your database connection through another network connection that could do those things.

Well there is, and it’s called an SSH tunnel.

SSH talks on port 22 – which is more often than not open on your locked down servers.

So if you can get a SSH connection going, you could send SQL*Net traffic over that connection…and on the other end of the tunnel, you could establish connections to the database.

That’s not the most eloquent description of the concept, but we have Wikipedia to help with that. And I’m guessing that everyone reading this will already be intimately aware of SSH tunnels.

Instead of having to do it outside of SQL Developer, now you can define/create those tunnels with the tool itself.

So, after you…

Upgrade to Version 4.0.3…

…you can now do this 🙂

So click on the 'advanced' button...

So click on the ‘advanced’ button…

And provide your authentication details

Use keys if you can

Use keys if you can

Now, my ssh connection is a bit weird. I’ve simply created a port forwarding scheme in my VM – traffic on port 22 on the host is forwarded to port 22 on the Linux VM. And then from there, the server will open a connection to the database.

If you’re using an IP address or server name in your regular Oracle connection properties – that info HAS to make sense to the server that you have SSH’d into.

Today and Tomorrow

Today, the tunnel is defined at the connection level. Now, you will probably need to create tunnels for more than just one connection, and many of them might be shared.

So, in an upcoming release, we see having the ability in the preferences to define your tunnels, and reuse them as you define each connection.

A connection being established, first the tunnel, then the database

A connection being established, first the tunnel, then the database

Now the ‘Tomorrrow’ Bit

We updated the ssh tunneling interface for version 4.1. You can define 1 tunnel and use it for multiple connections. The tunnels are defined and managed in the View > SSSH panel.

New location to define tunnels in 4.1

New location to define tunnels in 4.1

Defining the SSH tunnel AND the port forward:

When you create a tunnel, we also offer to setup the port forward.

When you create a tunnel, we also offer to setup the port forward.

Now use the tunnel in your connection properties:

Don't do as I do, don't connect as 'SYS'

Don’t do as I do, don’t connect as ‘SYS’

Oracle ACE Danny Bryant has a nice 4.0 vs 4.1 SSH Tunneling overview here.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 24

  1. It is very useful feature, but I would need to set an ssh option without which I cannot establish the connection (KexAlgorithms=diffie-hellman-group14-sha1). I work with Fedora and tried to modify the ssh_config file, but it works only in console.
    Can it be setup for the SQLDeveloper’s connection?
    Thank you,
    Enrico

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  2. Hi Jeff, is it possible only with key? I tried to connect with an SSH-Host with username and hoped to get question about my password. But I got only this error: An error occurred while opening the host connection:
    com.jcraft.jsch.JSchException: Auth fail
    🙁

    Thanks,
    Eugen

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  3. Hi, Jeff, I’m trying out the 4.1EA2 (4.1.0.18.37), but can’t figure out, where to find the “SSH tab”.
    I can’t get it displayed while setting any value from the “Connection type” dropdown.

    PS: I use ssh tunnels extensively, so I find this a quite nice feature.

    1. thatjeffsmith Post
      Author
      1. Thanks, I found it a while later. And another while later I remembered, I also use sqlplus, so I still have to use the tunnels/tnsnames combo.
        Nice feature, however.
        If I didn’t need sqlplus so often, I would definitely switched to it.

        1. thatjeffsmith Post
          Author
          1. Hi,
            I’m trying to get this nice feature to work on Version 4.1.0.19.
            When I test the freshly created connection I get a stack trace:

            com.jcraft.jsch.JSchException: The cipher ‘aes256-cbc’ is required, but it is not available.
            at com.jcraft.jsch.KeyPair.loadPPK(KeyPair.java:1035)
            at com.jcraft.jsch.KeyPair.load(KeyPair.java:591)
            ….
            Any ideas?

  4. I’m using 4.0.3.16.84 and the SSH tunnel doesn’t appear to work. I can see it’s able to log me into the ssh server with my ssh key. A test of the oracle connection fails. When I do a tcpdump, I see the problem. The connection made out to the Oracle DB tries to connect as the username set in the SSH tab, not the Connection’s username which is the username for the Oracle DB. This sounds like a bug in the sqldeveloper ssh tunnel code.

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
  5. Hi – I can’t get the SSH tunneling to work, the only examples I can find are where the SSH “jumphost” is the same as the DB host. I have a situation where:

    Mac -> jumphost:22 -> dbhost:1525

    SQL Developer connects to the jumphost just fine, but it cannot connect to the DB host.

    This is the tunnel I use to get around SQL Developer:

    ssh -L 1525:dbhost:1525 [email protected]

    Thanks,
    David

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *