5 Replies Latest reply on Mar 14, 2015 4:16 PM by jmarton

    SQL Developer and SSH Tunnel

    implode

      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.  The Oracle instance is clustered with scan VIPs, but I don't think that's relevant.

        • 1. Re: SQL Developer and SSH Tunnel
          Adric

          If tcpdump is able to capture recognizable data, then most likely your DB connection isn't being tunneled for some reason. Do you have SQL developer configured to use the OCI/Thick driver with an full client (Oracle Home), by any chance? If so, you might try reconfiguring it as an Instant Client (probably don't need to change the actual client location).

           

          I just posted an issue here, about tunneling seemly working only for JDBC Thin and Instant Clients configurations. At first glance, it seems plausible that we're encountering the same problem.

          • 2. Re: SQL Developer and SSH Tunnel
            implode

            I ran the tcpdump on the ssh tunneled server and from there, that's where I dumped outbound traffic to port 1521 and saw the traffic. The ssh tunnel to the ssh server is fine as I can see my session get logged in /var/log/secure

             

            SQL Developer is configured to use connection type Basic.  In the preferences for Databases, Advanced, it's not using Oracle Client nor OCI/Thick driver.

            In About Oracle SQL Developer, Properties, sqldeveloper.oci.available is false

            • 3. Re: SQL Developer and SSH Tunnel
              jmarton

              I tried to reproduce the behaviour with your settings (Basic conn. type, thin JDBC driver). I used password authentication for the ssh tunnel.

               

              Using SQL Developer 4.1 EA1 (4.1.0.17.29), connection is estabilished and works fine over the tunnel.

               

              Using SQL Developer 4.0.3 (4.0.3.16.84), it seems for me that the SSH tunnel is built, and then closed immediately. After that, SQL Developer connects directly to the database server bypassing the (already closed) tunnel. SSH server and the Oracle DBMS are also fed with the corresponding username and password. (I checked this in the corresponding logs, using also name resolution tricks, and also configuring an SSH server for tunneling that is blocked on the DB server's firewall. So I'm pretty sure, as the connection was succesfully estabilished to the DBMS and the DBMS reports my IP address for the client, not that of the tunnelling server's :-)

               

               

              Thus, your initial comment sounds like a bug in 4.0.3 (4.0.3.16.84), which has been corrected in 4.1 EA1 (4.1.0.17.29).

              1 person found this helpful
              • 4. Re: SQL Developer and SSH Tunnel
                implode

                I'm now trying with 4.1 EA2 (4.1.0.18.37) and I still can't connect.  I took a look at:
                SSH Tunnel with #SQLDev 4.1 EA1 and EA2 side by side

                 

                I managed to connect when I used an actual server instead of the SCAN VIP for my ssh destination.  Was your reproduction using a SCAN VIP?

                • 5. Re: SQL Developer and SSH Tunnel
                  jmarton

                  My SSH destination was a jump box running linux and hosting no Oracle software. The port forward target host was an actual server hosting a listener for a non-clustered database installation.

                   

                  I'm not sure how a SCAN connection works. If my assumption below is correct, then using SSH local port forwarding seems to be very tricky to make working (if possible).

                  1. DNS resolves scan-listener to 10.0.0.101 (2 other addresses are 10.0.0.100 and 10.0.0.102)
                  2. oracle client lib connects to the listener on 10.0.0.101 telling which resource (database service) it asks for
                  3. listener replies the client to use address 10.0.0.102
                  4. client connects to 10.0.0.102

                  For step(2) above, there might be a local port forward opened, but not step(4), there is no one.

                   

                  The application should use a properly configured java.net.ProxySelector in the JVM instance to deal with dynamically changing host addresses. But this should mean SOCKS proxying (dynamic port forwarding) instead of local port forwarding provided by the recent SQL Developer SSH developments.