B4J Code Snippet Access Your MySQL Server Remotely Over SSH

Usually we have a Web Server with MYSQL with a provider, but access to the MYSQL DBs is only opened to local ports (eth1) by default for security reasons. If you want to access your database from a client over Internet (from MySQL Query Browser or other tools), normally you’d have to open up access from your local IP address… but that’s not nearly as secure.

So instead, we use port-forwarding through an SSH tunnel, so your MySQL client thinks they're connecting to your localhost machine, but it’s really connecting to the other server through the SSH tunnel (the client connects over Internet to the Public IP address assigned to server/domain)

If you are using the command line ssh in Linux, the command would look like this.
ssh login@server -L 3306:127.0.0.1:3306 (where 3306 is MYSQL service IP Port - we connect the ssh tunnel between our computer to the MYSQL server and forwarding all the source traffic with 3306 port destination with a localhost IP and a remote port 3306)

We can establish this connection in Windows with tools like plink.exe or Putty, but If you want to integrate with your B4J program, you'll have to open with Jshell library.

You can use the JSch library ( http://www.jcraft.com/jsch/) found also on B4X forum, to establish the tunnel and configure Port Forwarding using the library methods with JavaObject (thanks @rwblinn )

#AdditionalJar: jsch-0.1.51.jar

    Dim user As String = "user"
    Dim host As String = "host"
    Dim port As Int = 22
    Dim pw As String = "pass"
    Dim joSch As JavaObject
    'Java: JSch js = new JSch();
    joSch.InitializeNewInstance("com.jcraft.jsch.JSch", Null)
    'Java: Session s = js.getSession("user", "myremotemachine.mycompany.com", 22);
    Dim joS As JavaObject
    joS = joSch.RunMethod("getSession", Array(user, host, port))
    'Java: s.setPassword("mypassword");
    joS.RunMethod("setPassword", Array(pw))
    'Java: Properties config = new Properties();
    Dim joP As JavaObject
    joP = joP.InitializeNewInstance("java.util.Properties", Null)
    'Java: config.put("StrictHostKeyChecking", "no");
    joP.RunMethod("setProperty", Array("StrictHostKeyChecking", "no"))
    'Java: s.setConfig(config);
    joS.RunMethod("setConfig", Array(joP))
    'Java: s.connect();
    joS.RunMethod("connect", Null)
    Log("Connected to Host...")
    joS.RunMethod("setPortForwardingL",Array(3306,"127.0.0.1",3306))'Array(tunnelLocalPort,tunnelRemoteHost,tunnelRemotePort);
    Log("PortFWD to MYSQL...")

After this tunnel is established and port forwarding is configured, you can Query the MYSQL DataBase with standards Querys from the jSQL B4J library and DBUtils








.

 

[alwaysbusy]

I know this is an old thread, but I myself searched a couple of hours to get my setup right using a .ppk file so I decided to post it anyway for future reference:

Dim joSch As JavaObject
'Java: JSch js = new JSch();
joSch.InitializeNewInstance("com.jcraft.jsch.JSch", Null)
'jsch.addIdentity("K:\my-private.ppk", "myppkPassword");
joSch.RunMethod("addIdentity", Array("K:\my-private.ppk", "myppkPassword"))
'Session session=jsch.getSession("myDevLogin", "123.123.123.123", 22);
Dim joS As JavaObject
joS = joSch.RunMethod("getSession", Array("myDevLogin", "123.123.123.123", 22))       
'session.setConfig("StrictHostKeyChecking", "no");
Dim joP As JavaObject
joP = joP.InitializeNewInstance("java.util.Properties", Null)
joP.RunMethod("setProperty", Array("StrictHostKeyChecking", "no"))
joS.RunMethod("setConfig", Array(joP))
'session.setConfig("PreferredAuthentications", "publickey"); // "publickey" is a fixed string here, not 'your' public key
Dim joP2 As JavaObject
joP2 = joP2.InitializeNewInstance("java.util.Properties", Null)
joP2.RunMethod("setProperty", Array("PreferredAuthentications", "publickey"))
joS.RunMethod("setConfig", Array(joP2))
'session.connect();
joS.RunMethod("connect", Null)
joS.RunMethod("setPortForwardingL",Array(3306,"127.0.0.1",3306))

Alwaysbusy