SSH tunneling to remote access MySQL database

java mysql remote-access ssh-tunnel blackboard
Hei picture Hei · Feb 20, 2013 · Viewed 9.4k times · Source

I am trying to write Java program to access a remote mySQL database using ssh tunneling.

Below is my code:

int lport = 5656;

int rport = 3306;

String rhost = "111.222.333.444";

String host = "111.222.333.444";

String user = "username";

String password = "password1234";

String dbUser = "mySQLuser";

String dbPass = "mySQLpassword1234";

String schema = "test_db";





Connection conn = null;


try {


Properties config = new Properties();

config.put("StrictHostKeyChecking", "no");


JSch jsch = new JSch();

jschSession = jsch.getSession(user, host, 22);

jschSession.setPassword(password);

jschSession.setConfig(config);

jschSession.connect();

logger.info("Connected");


int assigned_port = jschSession.setPortForwardingL(lport, rhost, rport);

logger.info("localhost:" + assigned_port + " -> " + rhost + ":" + rport);

logger.info("Port Forwarded");




Class.forName("com.mysql.jdbc.Driver").newInstance();

String url = "jdbc:mysql://localhost:" + rport + "/" + schema;

conn = DriverManager.getConnection(url, dbUser, dbPass);

logger.info("Database connection established");


Statement stmt = conn.createStatement();


String sql = "SELECT * FROM TEST_TABLE";

ResultSet rs = stmt.executeQuery(sql);


while(rs.next()) {

logger.info(rs.getInt(1) + " " + rs.getString(2));

}


rs.close();

stmt.close();


logger.info("DONE");


} catch (Exception e) {

e.printStackTrace();

logger.error(e.getMessage());

if (jschSession != null && jschSession.isConnected()) {

System.out.println("Closing SSH Connection during error");

logger.error("Closing SSH Connection during error");

jschSession.disconnect();

}


} finally {

if (conn != null && !conn.isClosed()) {

System.out.println("Closing Database Connection");

logger.info("Closing Database Connection");

conn.close();

}


if (jschSession != null && jschSession.isConnected()) {

System.out.println("Closing SSH Connection");

logger.info("Closing SSH Connection");

//jschSession.disconnect();

}


}

I am pretty sure that all the usernames and passwords are correct.

However, when I am trying to run the above code, it always fail in the following line:

conn = DriverManager.getConnection(url, dbUser, dbPass);

with the following exception:

INFO   | jvm 1    | 2013/02/20 17:25:24 | java.sql.SQLException: Access denied for user 'mySQLuser'@'localhost' (using password: YES)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:885)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3421)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1247)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at com.mysql.jdbc.Connection.createNewIO(Connection.java:2775)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at com.mysql.jdbc.Connection.<init>(Connection.java:1555)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at java.sql.DriverManager.getConnection(DriverManager.java:582)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at java.sql.DriverManager.getConnection(DriverManager.java:185)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at com.prject.testing.Test.<init>(Test.java:97)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.BeanUtils.instantiateClass(BeanUtils.java:147)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:76)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory$4.run(AbstractAutowireCapableBeanFactory.java:997)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at java.security.AccessController.doPrivileged(Native Method)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateBean(AbstractAutowireCapableBeanFactory.java:995)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:955)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:487)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:458)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:294)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:225)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:291)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.DefaultListableBeanFactory.findAutowireCandidates(DefaultListableBeanFactory.java:874)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:816)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:731)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:485)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:92)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:284)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1118)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:519)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:458)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:294)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:225)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:291)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:605)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:926)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:477)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.web.servlet.FrameworkServlet.configureAndRefreshWebApplicationContext(FrameworkServlet.java:638)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.web.servlet.FrameworkServlet.createWebApplicationContext(FrameworkServlet.java:595)
INFO   | jvm 1    | 2013/02/20 17:25:24 |       at org.springframework.web.servlet.FrameworkServlet.createWebApplicationContext(FrameworkServlet.java:652)

I have also checked the mySQL, the "host" for the user "mySQLuser" has already set as "%" in the "User" table.
Could anyone give me some suggestions?

Update:

Adopting the suggestion from @bmorris591,

I have changed String url = "jdbc:mysql://localhost:" + rport + "/" + schema; into String url = "jdbc:mysql://localhost:" + lport + "/" + schema;

However, there is a new exception appear:

java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1997)
    at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:573)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1044)
    at com.mysql.jdbc.Connection.createNewIO(Connection.java:2775)
    at com.mysql.jdbc.Connection.<init>(Connection.java:1555)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
    at java.sql.DriverManager.getConnection(DriverManager.java:582)
    at java.sql.DriverManager.getConnection(DriverManager.java:185)
    at com.project.testing.Test.<init>(Test.java:98)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    at org.springframework.beans.BeanUtils.instantiateClass(BeanUtils.java:147)
    at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:76)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory$4.run(AbstractAutowireCapableBeanFactory.java:997)
    at java.security.AccessController.doPrivileged(Native Method)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateBean(AbstractAutowireCapableBeanFactory.java:995)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:955)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:487)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:458)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:294)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:225)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:291)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.findAutowireCandidates(DefaultListableBeanFactory.java:874)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:816)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:731)
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:485)
    at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:92)
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:284)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1118)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:519)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:458)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:294)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:225)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:291)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:605)
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:926)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:477)
    at org.springframework.web.servlet.FrameworkServlet.configureAndRefreshWebApplicationContext(FrameworkServlet.java:638)
    at org.springframework.web.servlet.FrameworkServlet.createWebApplicationContext(FrameworkServlet.java:595)
    at org.springframework.web.servlet.FrameworkServlet.createWebApplicationContext(FrameworkServlet.java:652)
    at org.springframework.web.servlet.FrameworkServlet.initWebApplicationContext(FrameworkServlet.java:514)
    at org.springframework.web.servlet.FrameworkServlet.initServletBean(FrameworkServlet.java:455)
    at org.springframework.web.servlet.HttpServletBean.init(HttpServletBean.java:138)
    at javax.servlet.GenericServlet.init(GenericServlet.java:212)
    at sun.reflect.GeneratedMethodAccessor120.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.apache.catalina.security.SecurityUtil$1.run(SecurityUtil.java:270)
    at org.apache.catalina.security.SecurityUtil$1.run(SecurityUtil.java:269)
    at java.security.AccessController.doPrivileged(Native Method)
    at javax.security.auth.Subject.doAsPrivileged(Subject.java:517)
    at org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:302)
    at org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:163)
    at org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:117)
    at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1200)
    at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:1026)
    at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:4421)

and still the error happened in this line of code:

conn = DriverManager.getConnection(url, dbUser, dbPass);

The error messages make me quite confuse,

Could any give me some more suggestions?

Answer

Boris the Spider picture Boris the Spider · Feb 20, 2013

It would seem to be that the error is on this line:

String url = "jdbc:mysql://localhost:" + rport + "/" + schema;

Given than rport is 3306, i.e. the mySQL port on the remote machine, you are connecting to

"jdbc:mysql://localhost:3306/" + schema;

Which is your local machine. You need to connect to:

String url = "jdbc:mysql://localhost:" + lport + "/" + schema;

Which would be the port on the local machine that forwards to 3306 on the remote machine.

Related questions

Error in MySQL Connection when accessing a remote server

I've hosted a MySQL DB in a webserver. I've granted all privledges and allowed my IP to connect to this database remotely from my local computer. It gets connected and I'm able to retrieve data from the database to my …

Read More
Solving a "communications link failure" with JDBC and MySQL

I'm trying to connect to the local MySQL server but I keep getting an error. Here is the code. public class Connect { public static void main(String[] args) { Connection conn = null; try { String userName = "myUsername"; String password = "myPassword"; String url = "…

Read More
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

I'm working on getting my database to talk to my Java programs. Can someone give me a quick and dirty sample program using the JDBC? I'm getting a rather stupendous error: Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.…

Read More