Java Program to connect to MySQL over SSH using JSch and JDBC

Filed Under: Java

Recently in one of my project, I had to connect to a remote database that was set up on the SSH-enabled server. Since it was on SSH-enabled server, we can’t connect it directly using JDBC connection API. To achieve this, we first need to create SSH session and then using Port Forwarding we can forward the request to server and connect to database.

I achieved this using JSch Port Forwarding and then connecting to database.

Java Program to Connect to Remote SSH MySQL Database using JSch

Here is the program I wrote for port forwarding and connecting to a remote database.


package com.journaldev.java.ssh

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

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


public class MySqlConnOverSSH {

	/**
	 * Java Program to connect to remote database through SSH using port forwarding
	 * @author Pankaj@JournalDev
	 * @throws SQLException 
	 */
	public static void main(String[] args) throws SQLException {

		int lport=5656;
	    String rhost="secure.journaldev.com";
	    String host="secure.journaldev.com";
	    int rport=3306;
	    String user="sshuser";
	    String password="sshpassword";
	    String dbuserName = "mysql";
        String dbpassword = "mysql123";
        String url = "jdbc:mysql://localhost:"+lport+"/mydb";
        String driverName="com.mysql.jdbc.Driver";
        Connection conn = null;
        Session session= null;
	    try{
	    	//Set StrictHostKeyChecking property to no to avoid UnknownHostKey issue
	    	java.util.Properties config = new java.util.Properties(); 
	    	config.put("StrictHostKeyChecking", "no");
	    	JSch jsch = new JSch();
	    	session=jsch.getSession(user, host, 22);
	    	session.setPassword(password);
	    	session.setConfig(config);
	    	session.connect();
	    	System.out.println("Connected");
	    	int assinged_port=session.setPortForwardingL(lport, rhost, rport);
	        System.out.println("localhost:"+assinged_port+" -> "+rhost+":"+rport);
	    	System.out.println("Port Forwarded");
	    	
	    	//mysql database connectivity
            Class.forName(driverName).newInstance();
            conn = DriverManager.getConnection (url, dbuserName, dbpassword);
            System.out.println ("Database connection established");
            System.out.println("DONE");
	    }catch(Exception e){
	    	e.printStackTrace();
	    }finally{
	    	if(conn != null && !conn.isClosed()){
	    		System.out.println("Closing Database Connection");
	    		conn.close();
	    	}
	    	if(session !=null && session.isConnected()){
	    		System.out.println("Closing SSH Connection");
	    		session.disconnect();
	    	}
	    }
	}

}

Output of the above program is:


Connected
localhost:5656 -> secure.journaldev.com:3306
Port Forwarded
Database connection established
DONE
Closing Database Connection
Closing SSH Connection

Here I am connecting to MySql database but you can connect to any other database or Enterprise Information Systems using this approach.

Comments

  1. Oscar says:

    Excellent! 😀

  2. Ankur Srivastava says:

    This is the exact scenario i was looking for .Thanks anyways for the post .

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.util.Properties;

    import com.jcraft.jsch.JSch;
    import com.jcraft.jsch.JSchException;
    import com.jcraft.jsch.Session;

    public class CTestDriver
    {
    private static void doSshTunnel( String strSshUser, String strSshPassword, String strSshHost, int nSshPort, String strRemoteHost, int nLocalPort, int nRemotePort ) throws JSchException
    {
    final JSch jsch = new JSch();
    Session session = jsch.getSession( strSshUser, strSshHost, 22 );
    session.setPassword( strSshPassword );

    final Properties config = new Properties();
    config.put( “StrictHostKeyChecking”, “no” );
    session.setConfig( config );

    session.connect();
    session.setPortForwardingL(nLocalPort, strRemoteHost, nRemotePort);
    }

    public static void main(String[] args)
    {
    try
    {
    String strSshUser = “ssh_user_name”; // SSH loging username
    String strSshPassword = “abcd1234”; // SSH login password
    String strSshHost = “your.ssh.hostname.com”; // hostname or ip or SSH server
    int nSshPort = 22; // remote SSH host port number
    String strRemoteHost = “your.database.hostname.com”; // hostname or ip of your database server
    int nLocalPort = 3366; // local port number use to bind SSH tunnel
    int nRemotePort = 3306; // remote port number of your database
    String strDbUser = “db_user_name”; // database loging username
    String strDbPassword = “4321dcba”; // database login password

    CTestDriver.doSshTunnel(strSshUser, strSshPassword, strSshHost, nSshPort, strRemoteHost, nLocalPort, nRemotePort);

    Class.forName(“com.mysql.jdbc.Driver”);
    Connection con = DriverManager.getConnection(“jdbc:mysql://localhost:”+nLocalPort, strDbUser, strDbPassword);
    con.close();
    }
    catch( Exception e )
    {
    e.printStackTrace();
    }
    finally
    {
    System.exit(0);
    }
    }
    }

  3. Jochen Seliger says:

    I nedd a solution to let client applications of my system to connect from anywhere through a JSch-session to a central DB.Server.
    In all cases I’ve read, there is am missmatch of the remote server.
    In my case the central DB.server is running in a central LAN.
    First I#we forwarded the SSH port 22 from the shell publishing mashine to its ISP gateway.
    That service I may accces via an dynamic DNS name,.
    After establishing the session+channel I may connect on the console with the central MySQL.
    But I need the jdbc.mysql-Driverto accerss the DB.
    I’ve forwarded nor also the MySQL-Port 3306 to the gateway.
    Further more I’ve set PortForwardingL at the session with lport 1097, rhost= my domainame anr rport 3303.
    So I try to access the DB at 127.0.0.1:1097, but get errors ‘The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.’

  4. Radhakrishna says:

    Hello Pankaj,
    Can you please send me a java servlet program to connect remote mysql database server.

    import java.sql.*;
    import java.io.*;
    import javax.servlet.*;
    import javax.servlet.http.*;
    public class MyServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    protected void doGet(HttpServletRequest request,HttpServletResponse response)throws ServletException,IOException {
    String url=”http://localhost:8080/jdbc:mysql://10.11.12.141:3306/”;
    String dbName = “Radhakrishna”;
    String driver = “com.mysql.jdbc.Driver”;
    String userName = “root”;
    String password = “Ubuntu1204”;
    try
    {
    Class.forName(driver).newInstance();
    Connection conn = DriverManager.getConnection(url+dbName,userName,password);
    Statement st = conn.createStatement();
    int val = st.executeUpdate(“insert into PaswordDetails (empid,empname,password) values (106,’Prasanna’,14500)”);
    if(val==1)
    System.out.print(“Successfully inserted value”);
    //conn.close();
    ResultSet res = st.executeQuery(“SELECT * FROM PaswordDetails”);
    while (res.next())
    {
    int id = res.getInt(“Empid”);
    String msg = res.getString(“Empname”);
    String pwd = res.getString(“Password”);
    System.out.println(id + “\t” + msg+ “\t” +pwd);
    }

    }
    catch (Exception e)
    {
    e.printStackTrace();
    }
    }
    }

    I am getting error as
    HTTP Status 404 – /JDBC/servlet/MyServlet

    ——————————————————————————–

    type Status report

    message /JDBC/servlet/MyServlet

    description The requested resource is not available.

    ——————————————————————————–

    Apache Tomcat/7.0.52

    1. Abhinay says:

      you cannot do that. I am not sure what you are trying to achieve, but you cant get a connection from a servlet unless you are using the servlet as an instantiated class.

  5. Naveen says:

    Hiii,

    I tried to implement the same code for Oracle DB and I’m getting errors.

    Can you pls provide step by step code for Oracle DB ? Pls. explain lport and rport briefly.

  6. Ashok N says:

    I have tried connecting to Sybase using isql.

  7. Ashok N says:

    Hi this Code works fine…Thanx..
    I have ssh connection to a server which has Sybase in it.
    I tried connecting to the database using isql in the same server and it works.
    I tried connecting to the database after forwarding the port to my local machine it states login failed …
    need suggestion on this to move forward..
    ——————————————-
    Connected to SSH server[xxx.xxx.xxx.xxx]
    localhost:5655 -> xxxx.xxx.xxx.xxx:5025
    Port Forwarded
    Establishing connection to

    java.sql.SQLException: JZ00L: Login failed. Examine the SQLWarnings chained to this exception for the reason(s).
    Closing SSH Connection
    ——————————————-

    1. Pankaj says:

      Did you first tried to connect to Sybase using a simple java program on the SSH server? Port forwarding just sends the same command to the server port and it should work fine if the normal program works.

  8. Vasif says:

    Usefull post. thanks 🙂

  9. Abubakar Gurnah says:

    i need your help, how can I copy paste this to your code

    ssh -L 3307:xxxx:5123 yyyyy@zz.zz.zz.zz -p bbbb

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages