Unable to connect to MySQL Database with Java through SSH

Question

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.

xkcd 979: Also, if you get an error because you're making changes after the connection is closed, that third `try` block actually closes the connection after it's done; kind of like the Java equivalent to Python's `with` statement. — ImportAntigrav, Apr 17 '18 at 06:17

score 0 · Accepted Answer · answered Apr 30 '18 at 10:53

Solution by OP.

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".

Unable to connect to MySQL Database with Java through SSH

1 Answers1