Unable to connect to MySQL Database with Java through SSH (Solved)

I know that there are many questions regarding this problem already, and believe me I've looked through all of them.

Some background, I'm using IntelliJ and I've been able to both connect to the database with IntelliJ's data source tool and I've been able to SSH into the server itself using the terminal. For some reason, even if I try to do the same thing with Java it doesn't work. Here's my code:

String serverIP = "123.45.67.890"
String username = "root";
String password = "password";

// Establish SSH tunnel with server if not running locally
try {
    if (!Inet4Address.getLocalHost().getHostAddress().equals(ServerIP) {
        System.out.println("Remote connection detected.");
        System.out.println("Establishing SSH Tunnel...");
        JSch jSch = new JSch();
        Session session = jSch.getSession(username, ServerIP, 22);
        session.setConfig("StrictHostKeyChecking", "no");
        session.setPassword(password);
        System.out.printf("Connecting to remote server at %s...\n", ServerIP);
        session.connect();
        System.out.println("Connected!");
        session.setPortForwardingL(3306, ServerIP, 3306);
    }
} catch (Exception e) {
    e.printStackTrace();
}

// Load mySQL Driver
System.out.println("Loading driver...");
try {
    Class.forName("com.mysql.jdbc.Driver");
    System.out.println("Driver loaded!");
} catch (ClassNotFoundException e) {
    throw new IllegalStateException("Cannot find the driver in the classpath!", e);
}

String url = String.format("jdbc:mysql://%s:3306/db_name", ServerIP);

// Connect to database
System.out.println("Connecting to database...");

try (Connection connection = DriverManager.getConnection(url, username, password)) {
    System.out.println("Database connected!");
    this.connection = connection;
} catch (SQLException e) {
    this.connection = null;
    throw new IllegalStateException("Cannot connect to database!", e);
}

And here's the error I get:

Remote connection detected.
Establishing SSH Tunnel...
Connecting to remote server at 123.45.67.890...
Connected!
Loading driver...
Driver loaded!
Connecting to database...

java.lang.IllegalStateException: Cannot connect to database!

    ...
    a bunch of crap
    ...
Caused by: java.sql.SQLException: null,  message from server: "Host 'my.personal.computer.local.hostname' is not allowed to connect to this MySQL server"
    ...
    a bunch more crap
    ...

It seems to me like for some reason, the Java code isn't using the SSH tunnel I put so much effort into copying StackOverflow code to make, and is instead rudely trying to connect normally. What am I doing wrong?

Some more info: The server itself is configured to listen for SQL on 0.0.0.0 and I also set the firewall to accept connections on port 3306.

Edit for clarity: I understand that I can probably fix this by giving my local computer privileges on the server, but I want to be able to connect through SSH.

SOLUTION (pursuant to xkcd 979) The reason SSH wasn't working was because I needed to use a local open port, connect to SQL through that, then forward that to the remote port 3306. Also, I needed to port forward through localhost, not the server address. (That is, change the session.setPortForwardingL(3306, ServerIP, 3306); line to session.setPortForwardingL(some-open-port, "localhost", 3306); and "jdbc:mysql://%s:3306/db_name" to "jdbc:mysql://%s:same-open-port/db_name".

1 answer

  • answered 2018-04-17 05:49 Budhdi Sharma

    My understanding is that you want to access a mysql server running on a remote machine and listening on let's say port 3306 through a SSH tunnel.

    To create such a tunnel from port 1234 on your local machine to port 3306 on a remote machine using the command line ssh client, you would type the following command from your local machine:

    ssh -L 1234:localhost:3306 mysql.server.remote
    

    To do the same thing from Java, you could use JSch, a Java implementation of SSH2. From its website:

    JSch allows you to connect to an sshd server and use port forwarding, X11 forwarding, file transfer, etc., and you can integrate its functionality into your own Java programs. JSch is licensed under BSD style license.

    For an example, have a look at PortForwardingL.java. Once the session connected, create your JDBC connection to MySQL using something like jdbc:mysql://localhost:1234/[database] as connection URL.