Connecting to MySQL DB using SSH not successful through code but successful through MySQL Workbench

Question

I am trying to connect to remote (AWS server) MySQL DB using Java and SSH Tunneling. When trying to connect to the DB using MySQL Workbench it is successful:

When trying to connect to MySQL DB using Java code:

public class SQL {
    private final String MYSQL_HOSTNAME_CONNECTION = EnvConf.getProperty("mysql.hostname");
    private static final String sshUser = EnvConf.getProperty("ssh.username");
    private static final String sshHost = EnvConf.getProperty("ssh.hostname");

    public static void main(String[] args) throws JSchException {
        establishSSHConnection();
        getJDBCRemoteConnection();
    }

    private static void establishSSHConnection() {
        JSch jsch = new JSch();
        Session session = null;
        try {
            jsch.addIdentity(PATH_TO_PEM_FILE);
            session = jsch.getSession(sshUser, sshHost);
            Properties config = new Properties();
            config.put("StrictHostKeyChecking", "no");
            session.setConfig(config);
            session.connect();
            Channel channel = session.openChannel("shell");
            channel.connect();
            System.out.println("Connected!!!");
        } catch (Exception e) {
            e.printStackTrace(System.out);
        }

    }

    private static Connection getJDBCRemoteConnection() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String dbName = "dbName"
            String userName = "root";
            String password = "password";
            String hostname = "RDS_HOST_NAME";
            String port = "3306";
            String jdbcUrl = "jdbc:mysql://" + hostname + ":" + port + "/" + dbName + "?user=" + userName + "&password=" + password;
            Connection con = DriverManager.getConnection(jdbcUrl);
            return con;
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println(e.toString());
        }
        return null;
    }
}

I am getting the next error message:

com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

==============================EDIT=============================== This is the SSH tunneling info: ubuntu@SSH_HOSTNAME_STRING:22 and using Bastion PEM file to authenticate.

Dont you usually port-forward through SSH in order to access a RDS? Is there a need for the `JSch` Curious if you can just do that instead of doing SSH through Java..? https://stackoverflow.com/questions/12699854/how-to-connect-to-rds-from-local-java-program-via-ec2-ssh-tunnel?rq=1 — JCompetence, Jan 17 '22 at 13:00
@SMA I did not try port-forward through SSH, why using JSch is not a good option? — tupac shakur, Jan 17 '22 at 13:10
Not saying it is not a good option at all., just curious. Could you modify your question to showcase your SSH Configuration as well — JCompetence, Jan 17 '22 at 13:11

score 0 · Accepted Answer · answered Jan 19 '22 at 17:40

OK, I have figured out what was my problem and now I solved it and it is working as expected:

First I have not used the forwarded port which I have created when establishing ssh tunneling connection:

public class SQL {
    private final String MYSQL_HOSTNAME_CONNECTION = EnvConf.getProperty("mysql.hostname");
    private static final String sshUser = EnvConf.getProperty("ssh.username");
    private static final String sshHost = EnvConf.getProperty("ssh.hostname");
    private static int forwardedPort;

    public static void main(String[] args) throws JSchException {
        establishSSHConnection();
        getJDBCRemoteConnection();
    }

    private static void establishSSHConnection() {
        JSch jsch = new JSch();
        Session session = null;
        try {
            jsch.addIdentity(PATH_TO_PEM_FILE);
            session = jsch.getSession(sshUser, sshHost);
            Properties config = new Properties();
            config.put("StrictHostKeyChecking", "no");
            session.setConfig(config);
            session.connect();
            forwardedPort = session.setPortForwardingL( 0, MYSQL_HOSTNAME_CONNECTION , 3306);
            Channel channel = session.openChannel("shell");
            channel.connect();
            System.out.println("Connected!!!");
        } catch (Exception e) {
            e.printStackTrace(System.out);
        }

    }

    private static Connection getJDBCRemoteConnection() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String dbName = "dbName"
            String userName = "root";
            String password = "password";
            
            String port = "3306";
            String jdbcUrl = "jdbc:mysql://localhost:" + forwardedPort + "/" + dbName + "?user=" + userName + "&password=" + password;

            Connection con = DriverManager.getConnection(jdbcUrl);
            return con;
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println(e.toString());
        }
        return null;
    }
}

Connecting to MySQL DB using SSH not successful through code but successful through MySQL Workbench

1 Answers1