Can't connect to MySQL server (hosted by strato) over SSH

Question

I am trying to connect to a MySQL server hosted by strato.

I am connecting to the DB using PuTTy as instructed on this page. Now connecting to my database is no problem if I am using the terminal:

But once I am trying to connect to the MySQL server using NaviCat or MySQL Workbench it gives me this error: .

What am I doing wrong? How can I connect to the DB using preferably NaviCat? And I also want to connect to this DB via Java but what line should I use to connect, what should I fill in at the host? Just localhost? So should I just use a function like this to connect:

public static void connectToSQL() {
    try {
        @SuppressWarnings("unused")
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/DB3262523", "U3262523", "....");
        System.out.println("Connection success");
    } catch (Exception e) {
        //TODO: handle exception
        System.err.println(e);

}
}

Brightwork

Your problem is not a Java problem as you have the same issue from non-Java applications, I have removed the tag. I also strongly suggest you post the console output and the error messages as text not as screenshots. – Interim

Sidenote: your navicat screenshot shows a different screenshot than the MySQL Workbench screenshot. Are you sure you're using the right port. – Interim

@MarkRotteveel Im now trying to not use PuTTy but use NaviCat's SSH feature instead, but Im still getting the same error I am not sure if Im using the correct port, Im connecting like Im instructed at strato.com/faq/en_us/article/567/… but for some reason I cant connect to the DB using anything else then PuTTy...What should be the port besides 3306 and what should be the hostname, localhost or rdbms? (two screenshots of how my navicat settings r configured: puu.sh/zkHI4/a133bad8d3.png and puu.sh/zkHIb/a13cfd340a.png) – Brightwork

@MarkRotteveel I got the hostname, port and username from PuTTy and filles that into NaviCat but it still gives me the same error: 2013 - Lost connection to MySQL at 'reading intial communication packet', system error: 0 Screenshot of the info I got from PuTTy: puu.sh/zkJ5J/2b8f844115.png And two screenshots from my NaviCat config: puu.sh/zkJc6/c848d3ebd2.png and puu.sh/zkJdv/6feb584e3a.png – Brightwork

Have you tried resolving what rdbms pointing to? nslookup rdbms. Then using whatever that as host? – Abott

Answer 1

Finally, I got it, you want to connect mysql database service using SSH tunnel only, you don't want to open the firewall and want to access database via JumpBox only. I have used JSch liberary to do SSH tunning.

Here goes your code.

import java.sql.DriverManager;
import java.sql.SQLException;

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;
import java.sql.Connection;


public class MySqlConnOverSSH {


    public static void main(String[] args) throws SQLException {

        int local_proxy_port=4567;

        String database_host="rdbms.strato.de";
        String ssh_host="ssh.strato.de";
        int database_port=3306;
        String user="ssh_username";
        String privateKey = "In case you want to use private key to do SSH";

        String password="sshpassword";
        String dbuserName = "yourDBName";
        String dbpassword = "YourDBPass";
        String url = "jdbc:mysql://localhost:"+local_proxy_port+"/your-database-name";
        String driverName="com.mysql.jdbc.Driver";
        Connection conn = null;
        Session session= null;
        try{
            java.util.Properties config = new java.util.Properties(); 
            config.put("StrictHostKeyChecking", "no");
            JSch jsch = new JSch();
            //Only in case you need to access SSH using key.
            //jsch.addIdentity(privateKey);
            session=jsch.getSession(user, ssh_host, 22);
            session.setPassword(password);
            session.setConfig(config);
            session.connect();
            System.out.println("Connected");
            int assinged_port=session.setPortForwardingL(local_proxy_port, database_host, database_port);
            System.out.println("localhost:"+assinged_port+" -> "+database_host+":"+database_port);
            System.out.println("Port Forwarded");

            Class.forName(driverName).newInstance();
            conn = DriverManager.getConnection (url, dbuserName, dbpassword);
            System.out.println ("Connected!!!");
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            if(conn != null && !conn.isClosed()){
                System.out.println("Hurry Done!!");
                conn.close();
            }
            if(session !=null && session.isConnected()){
                System.out.println("Hurry Done!! Closing SSH");
                session.disconnect();
            }
        }
    }

}

Make sure to include to include mysql-connector-java-xxx-bin.jar; to your classpath. Where xxx is version of mysql jdbc jar e.g. 5.0.8

Post further error you see after running this code if it doesn't work. I could reattempt to answer.

Renettarenew

I did try the java solution, however that one fails because it is not connecting via ssl. I did not attempt to build a java ssl connection but as Mark Rotteveel already commented the problem does not seem to be java related as the other applications don't work either. – Gnomon

I am not quite sure how this is supposed to work without the ssl login. Please take a short look at the picture in the question, that shows the console. It demonstrates how you have to connect via ssl. It works via console, but when using the same ssl configuration via mysql workbench it doesn't work. Btw java exception is java.sql.SQLException: No suitable driver found for jdbc:mysql://rdbms.strato.de:3306/DB3462419?verifyServerCertificate=true&useSSL=true&requireSSL=true – Gnomon

Great job, you are very close of solving your problem, please include mysql jdbc jar to your classpath and also, add Class.forName("com.mysql.jdbc.Driver"); to your code as I have modified my answer. – Renettarenew

Right, I am sorry I forgot I used a different project last time I tried. This happens. com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure Do you know where I have to input my ssl login? I mean it can't work without that can it? – Gnomon

The ssl login is required. Please take a look at the console image. And It does not work. two comments up I already posted this exception. com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure – Gnomon

@Gnomon check the latest code and run if it works. I have modified the code and tested in my aws infrastructure. It works fine with SSH jump box. – Renettarenew

Answer 2

Since you can connect to the database using a terminal, you can actually inspect the database itself to see why, from the server point of view, a connection fails.

Try this query:

SELECT * FROM performance_schema.host_cache

Each connection error is accounted for in a different column, so this can be helpful to pinpoint the root cause of a denied connection.

Reference manual:

https://dev.mysql.com/doc/refman/5.6/en/host-cache-table.html

Odelia

good idea, but sadly the hosting company strato denies me access to this data table :/ – Gnomon