CODEWITHSUNDEEP
javamysqlsshmysql-workbench
trevdro
trevdro

Reputation: 23

Connecting a remote database that is behind a firewall via SSH

I am trying to write some Java code that connects to a remote database from my personal computer. I can currently ssh to these machines using a private/public key authorisation. I can access the GUI of these machines by creating SSH tunnels (with Putty).

I don't think the problems is with my code as I can create a DB using MySQL Workbench and can successfully query the db. When trying to access the db I use the same tunnell address that I use for the GUI. So my questions is down to how should I connect to the DB? Can anyone shed some light to my question? My code is posted below.

import  java.sql.Connection;        
import  java.sql.Statement;     
import  java.sql.ResultSet;     
import  java.sql.DriverManager;     
import  java.sql.SQLException;      
public class  SQLConnector {                
        public static void  main(String[] args) throws  ClassNotFoundException, SQLException {                                                  
                //Connection URL Syntax: "jdbc:mysql://ipaddress:portnumber/db_name"        
                String dbUrl = "jdbc:mysql://localhost:9092/db";                   

                //Database Username     
                String username = "root";   

                //Database Password     
                String password = "root";             

                //Query to Execute      
                String query = "select *  from employee;";  

                //Load mysql jdbc driver        
                Class.forName("com.mysql.jdbc.Driver");         

                //Create Connection to DB       
                Connection con = DriverManager.getConnection(dbUrl,username,password);

                //Create Statement Object       
               Statement stmt = con.createStatement();                  

                // Execute the SQL Query. Store results in ResultSet        
                ResultSet rs= stmt.executeQuery(query);                         

                // While Loop to iterate through all data and print results     
                while (rs.next()){
                            String myName = rs.getString(1);                                        
                            String myAge = rs.getString(2);                                                
                            System. out.println(myName+"  "+myAge);     
                    }       
                 // closing DB Connection       
                con.close();            
        }
}

The error I get is:

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.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.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

Upvotes: 0

Views: 759

Answers (2)

Serge
Serge

Reputation: 6095

If I get you right, the mysql is listening at port 9092 at the host you are connecting with ssh.

Then use this ssh command to connect:

ssh -L9092:localhost:9092 username@192.168.1.233

It creates a tcp listening socket at your local machine and tunnel any connections to port 9092 at local machine to the port 9092 at remote machine

Edit: I missed that you are using putty. Then open connection settings dialog, then browse to ssh and find port forwarding. the notation is nearly the same: local port, to some ip:port at remote site: here you can find a step-by-step guide with screenshots

Upvotes: 0

vath
vath

Reputation: 153

This should probably be a comment, but my reputation doesnt allow for comments. I would venture to guess that the port on the server's network is blocking that port (whatever port you're using) but is allowing for ssh. What is this server/where/can you change the settings on any firewall to allow for connection?

Upvotes: 1

Related Questions