SSH tunneling with Java, a database connection example

Wednesday November 07, 2012 ()

SSH tunnel

This blog is about using SSH tunnel in Java through jsch, a Java an implementation of SSH2.   As an example, we connect to an MySQL installation in a remote host behind a firewall which not accessible from the outside.   What an SSH tunnel does is forward accesses from a local port to a remote port on a remote host and transfer data in a secure manner.

First download jsch here http://www.jcraft.com/ and add it to your project.    The only requirements are,   you have to have an SSH account on the remote host,  and for the database example,    a database account with a "connect" permission.

Here is the code. Please refer to the inline comments from the class below.


import java.util.logging.Logger;
import com.jcraft.jsch.*;
import java.util.logging.Level;

public class MysqlManager {
    
    // Logger
    private final static Logger LOGGER =
            Logger.getLogger(MysqlManager.class.getName());
    
    public static void main(String args[]) {
        MysqlManager mng = new MysqlManager ();
        mng.connect();
    }

    public void connect() {
        
        // 
        int assigned_port;   
        final int local_port=3309;
        
        // Remote host and port
        final int remote_port=3306;
        final String remote_host="kahimyang.info";
        
        try {
            JSch jsch = new JSch(); 
            
            // Create SSH session.  Port 22 is your SSH port which
            // is open in your firewall setup.
            Session session = jsch.getSession("user", remote_host, 22);
            session.setPassword("ssh_password");

            // Additional SSH options.  See your ssh_config manual for
            // more options.  Set options according to your requirements.
            java.util.Properties config = new java.util.Properties();
            config.put("StrictHostKeyChecking", "no");
            config.put("Compression", "yes");
            config.put("ConnectionAttempts","2");
            
            session.setConfig(config);
            
            // Connect
            session.connect();            
            
            // Create the tunnel through port forwarding.  
            // This is basically instructing jsch session to send 
            // data received from local_port in the local machine to 
            // remote_port of the remote_host
            // assigned_port is the port assigned by jsch for use,
            // it may not always be the same as
            // local_port.
            
            assigned_port = session.setPortForwardingL(local_port, 
                    remote_host, remote_port);
            
        } catch (JSchException e) {            
            LOGGER.log(Level.SEVERE, e.getMessage()); return;
        }
        
        if (assigned_port == 0) {
            LOGGER.log(Level.SEVERE, "Port forwarding failed !"); 
            return;
        }

        // Database access credintials.  Make sure this user has
        // "connect" access to this database;
        
        // these may be initialized somewhere else in your code.
        final String database_user="user";
        final String database_password="password";
        final String database = "database";
        
        // Build the  database connection URL.
        StringBuilder url =
                new StringBuilder("jdbc:mysql://localhost:");
        
        // use assigned_port to establish database connection
        url.append(assigned_port).append ("/").append(database).append ("?user=").
                append(database_user).append ("&password=").
                append (database_password);
        
        try {
            Class.forName(
                    "com.mysql.jdbc.Driver").newInstance();
            java.sql.Connection connection =
                    java.sql.DriverManager.getConnection(url.toString());

            java.sql.DatabaseMetaData metadata = connection.getMetaData();

             // Get all the tables and views
            String[] tableType = {"TABLE", "VIEW"};                       
            java.sql.ResultSet tables = metadata.getTables(null, null, "%", tableType);
            String tableName;
            while (tables.next()) {
                tableName = tables.getString(3);

                // Get the columns from this table
                java.sql.ResultSet columns = 
                        metadata.getColumns(null, tableName, null, null);

                String columnName;
                int dataType;
                while (columns.next()) {
                    columnName = columns.getString(4);
                    dataType = columns.getInt(5);
                    
                    // Your actual task;
                }
            }

        } catch (ClassNotFoundException |
                IllegalAccessException |
                InstantiationException |
                java.sql.SQLException e) {
            LOGGER.log(Level.SEVERE, e.getMessage());
        }
        
    }
}

SSH uses remote host configuration information from the options specified in line 41 in the code above and,   in the $HOME/.ssh/ssh_config file from your local sysytem.   The code above will fail if this file ($HOME/.ssh/ssh_config) is not present.    This is usually the case if you have never used ssh in your local machine before.   To create this file enter the following:

ssh remote_host

Enter yes to when prompted.

Please visit Jcraft's website for more about jsch.

That's it Good Luck.


12,803

Comments (SSH tunneling with Java, a database connection example )