I am not able to connect to mysql via ssh and I don't know why using Java

Question

Ok - so I have been working on this for some time (sometimes not working on it), and I am ALMOST there but not yet. I am trying to tunnel into a mysql db and was able to successfully connect ssh using Jsch. But when I try to connect to the database, it gives me an "Access denied for user 'usernamne'@'localhost' (using password: YES)

Here is the following code: (of course I masked out sensitive info)

public void connect(){
    String user = "user";
    String password = "password";
    String host = "host.com";
    try {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        Connection conn = null;
        Properties info = new Properties();

        JSch jsch = new JSch();
        Session session = jsch.getSession(user, host, 22);
        jsch.addIdentity("/Users/user/.ssh/id_rsa", password);

        session.setConfig("StrictHostKeyChecking", "no");
        System.out.println("Establishing Connection...");
        if(session.isConnected()){
            session.disconnect();
        }
        session.connect();
        conn = DriverManager.getConnection("jdbc:mysql://localhost:4417/dbname","dbuser", "dbpassword");
        System.out.print("Connection successful!" + "\n\n");
        System.out.print("Connection:" + conn + "\n\n");

        Statement stmt = conn.createStatement();
        ResultSet resultSet = stmt.executeQuery("SELECT * FROM TABLENAME limit 1");
        System.out.print(resultSet);
    } catch (InstantiationException e) {
        e.printStackTrace();
    } catch (IllegalAccessException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (JSchException j){
        j.printStackTrace();
    }


}

I've read other posts on here but I have not found the exact solution to my problem. I am able to ssh in correct. And what's even more interesting is that if I do the same exact procedure command_line, it works fine. So I don't know why it wouldn't work here.

Answer 1

Try this. it might help you . Already tested in my setup. Enjoy. :)

create db user in remote mysql db.

CREATE USER 'myuser'@'clientip' IDENTIFIED BY 'pass';

CREATE USER 'myuser'@'%' IDENTIFIED BY 'pass';

grant ALL ON mydb.* to 'myuser'@'clientip';

grant ALL ON mydb.* to 'myuser'@'%';

FLUSH PRIVILEGES;

---

Code for ssh tunneling.

    int assigned_port;   
    final int local_port=8080;

    // Remote host and port
    final int remote_port=3306;
    final String remote_host="192.168.150.139";

    try {
        JSch jsch = new JSch(); 

        // Create SSH session.  Port 22 is your SSH port which
        // is open in your firewall setup.
        session = jsch.getSession("sshuser", remote_host, 22);
        session.setPassword("sshpassword");

        // Additional SSH options.  See your ssh_config manual for
        // more options.  Set options according to your requirements.
        java.util.Properties config = new java.util.Properties();
        config.put("StrictHostKeyChecking", "no");
        config.put("Compression", "yes");
        config.put("ConnectionAttempts","2");

        session.setConfig(config);

        // Connect
        session.connect();            

        // Create the tunnel through port forwarding.  
        // This is basically instructing jsch session to send 
        // data received from local_port in the local machine to 
        // remote_port of the remote_host
        // assigned_port is the port assigned by jsch for use,
        // it may not always be the same as
        // local_port.

        assigned_port = session.setPortForwardingL(local_port, 
                remote_host, remote_port);

    } catch (JSchException e) {            
        LOGGER.log(Level.SEVERE, e.getMessage()); return;
    }

    if (assigned_port == 0) {
        LOGGER.log(Level.SEVERE, "Port forwarding failed !"); 
        return;
    }

    // Database access credentials.  Make sure this user has
    // "connect" access to this database;

    // these may be initialized somewhere else in your code.
    final String database_user="myuser";
    final String database_password="pass";
    final String database = "mydb";

    // Build the  database connection URL.
    StringBuilder url =
            new StringBuilder("jdbc:mysql://localhost:");

    // use assigned_port to establish database connection
    url.append(assigned_port).append ("/").append(database).append ("?user=").
            append(database_user).append ("&password=").
            append (database_password);

    try {
        Class.forName(
                "com.mysql.jdbc.Driver");
        System.out.println(url.toString());
        java.sql.Connection connection =
                java.sql.DriverManager.getConnection(url.toString());

       String q="select * from customer";
       PreparedStatement ps=connection.prepareStatement(q);
       ResultSet rs= ps.executeQuery();
       while (rs.next()) {
        System.out.println(rs.getInt(1)+rs.getString(2));
    }


    } catch (ClassNotFoundException |
            java.sql.SQLException e) {
        LOGGER.log(Level.SEVERE, e.getMessage());
        e.printStackTrace();
    }
    finally{
        session.disconnect();
    }