ben may - 2012-04-30

Hey there! So I've been tasked with doing some SSH stuff in java which I've never done before. Below is the use case and code. Any help appreciated!

I am attempting to do the following:

My local hostname is nine.cpmc.columbia.edu

The MySQL server to which I am attempting to connect is on gem.cumc.columbia.edu

I am attempting to SSH through an intermediary server cait.cumc.columbia.edu.

Here is my code snippet:

            JSch jsch = new JSch();
            JSch.setLogger(new MyLogger());
            java.util.Properties config = new java.util.Properties();
            config.put("StrictHostKeyChecking", "no");
            String user = "blm14";
            //String host = "cait.cumc.columbia.edu";
            String rhost = "nine.cpmc.columbia.edu";
            String host = "cait.cumc.columbia.edu";
            int sshPort = 22, lport = 3305, rport = 3306;
            Session session = jsch.getSession(user, host, sshPort);
            session.setConfig(config);
            session.setHost(host);
            session.setPassword("<redacted>");
            session.connect();
            int assigned_port = session.setPortForwardingL(lport, rhost, rport);
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            mysql = DriverManager.getConnection
                ("jdbc:mysql://gem.cumc.columbia.edu:" + lport + "/hiccc_clinicaltrials","blm14","<redacted>");  

and here is the output:

INFO: Connecting to cait.cumc.columbia.edu port 22
INFO: Connection established
INFO: Remote version string: SSH-2.0-OpenSSH_4.3
INFO: Local version string: SSH-2.0-JSCH-0.1.47
INFO: CheckCiphers: aes256-ctr,aes192-ctr,aes128-ctr,aes256-cbc,aes192-cbc,aes128-cbc,3des-ctr,arcfour,arcfour128,arcfour256
INFO: aes256-ctr is not available.
INFO: aes192-ctr is not available.
INFO: aes256-cbc is not available.
INFO: aes192-cbc is not available.
INFO: arcfour256 is not available.
INFO: CheckKexes: diffie-hellman-group14-sha1
INFO: diffie-hellman-group14-sha1 is not available.
INFO: SSH_MSG_KEXINIT sent
INFO: SSH_MSG_KEXINIT received
INFO: kex: server: diffie-hellman-group-exchange-sha1,diffie-hellman-group14-sha1,diffie-hellman-group1-sha1
INFO: kex: server: ssh-rsa,ssh-dss
INFO: kex: server: aes128-ctr,aes192-ctr,aes256-ctr,arcfour256,arcfour128,aes128-cbc,3des-cbc,blowfish-cbc,cast128-cbc,aes192-cbc,aes256-cbc,arcfour,rijndael-cbc@lysator.liu.se
INFO: kex: server: aes128-ctr,aes192-ctr,aes256-ctr,arcfour256,arcfour128,aes128-cbc,3des-cbc,blowfish-cbc,cast128-cbc,aes192-cbc,aes256-cbc,arcfour,rijndael-cbc@lysator.liu.se
INFO: kex: server: hmac-md5,hmac-sha1,hmac-ripemd160,hmac-ripemd160@openssh.com,hmac-sha1-96,hmac-md5-96
INFO: kex: server: hmac-md5,hmac-sha1,hmac-ripemd160,hmac-ripemd160@openssh.com,hmac-sha1-96,hmac-md5-96
INFO: kex: server: none,zlib@openssh.com
INFO: kex: server: none,zlib@openssh.com
INFO: kex: server:
INFO: kex: server:
INFO: kex: client: diffie-hellman-group1-sha1,diffie-hellman-group-exchange-sha1
INFO: kex: client: ssh-rsa,ssh-dss
INFO: kex: client: aes128-ctr,aes128-cbc,3des-ctr,3des-cbc,blowfish-cbc
INFO: kex: client: aes128-ctr,aes128-cbc,3des-ctr,3des-cbc,blowfish-cbc
INFO: kex: client: hmac-md5,hmac-sha1,hmac-sha1-96,hmac-md5-96
INFO: kex: client: hmac-md5,hmac-sha1,hmac-sha1-96,hmac-md5-96
INFO: kex: client: none
INFO: kex: client: none
INFO: kex: client:
INFO: kex: client:
INFO: kex: server->client aes128-ctr hmac-md5 none
INFO: kex: client->server aes128-ctr hmac-md5 none
INFO: SSH_MSG_KEXDH_INIT sent
INFO: expecting SSH_MSG_KEXDH_REPLY
INFO: ssh_rsa_verify: signature true
WARN: Permanently added 'cait.cumc.columbia.edu' (RSA) to the list of known hosts.
INFO: SSH_MSG_NEWKEYS sent
INFO: SSH_MSG_NEWKEYS received
INFO: SSH_MSG_SERVICE_REQUEST sent
INFO: SSH_MSG_SERVICE_ACCEPT received
INFO: Authentications that can continue: gssapi-with-mic,publickey,keyboard-interactive,password
INFO: Next authentication method: gssapi-with-mic
INFO: Authentications that can continue: publickey,keyboard-interactive,password
INFO: Next authentication method: publickey
INFO: Authentications that can continue: password
INFO: Next authentication method: password
INFO: Authentication succeeded (password).
Could not open MySQL conn
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Last packet sent to the server was 0 ms ago.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2120)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:723)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:302)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:282)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:185)
at TrialLoader.openMySQLConnection(TrialLoader.java:81)
at TrialLoader.main(TrialLoader.java:27)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Last packet sent to the server was 0 ms ago.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:335)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2043)
… 13 more
Caused by: java.net.ConnectException: Connection refused
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)
at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)
at java.net.Socket.connect(Socket.java:525)
at java.net.Socket.connect(Socket.java:475)
at java.net.Socket.<init>(Socket.java:372)
at java.net.Socket.<init>(Socket.java:215)
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:253)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:284)
… 14 more