Jaspersoft/Pentaho - connect to MySQL via SSH

0
votes

I'm looking for a way to connect from my localhost:8081 (jasperserver) to a remote server, where my mysql DB is. I'm connecting to the DB through SSH (only way) in MySQL Workbench, it works good. I did a lot of research, I found out I need to make a SSH Tunnel on my local machine, but how to do that? How to connect to my jasperserver via ssh? It gives me 'connection refused' when I try to ssh localhost:8081. I have installed openssh for windows. I also tried the Pentaho, which works similar to jasper, and the connection method is the same. Thanks...

1
mysqlsshpentahojasperserverssh-tunnel
What is the reason that you want to use ssh? Can't you reach the remote server directly since this should be the preferred way. If you need to do it due to security reasons you can establish ssh directly without having your local Pentaho server do it for you. Once the link is established ANY process can use the forwarded ports. - Marcus Rickert
The thing is Ive been told ssh is the only way I can connect the DB. I cannot connect with host and user only. So I have to do this via ssh. And I can't figure out how to make that tunnel properly. - radzik
Can you establish an ssh connection to the remote server from a terminal? - Marcus Rickert
Im using git repo on the same server via Git Bash commandline, connecting with ssh. I also have filezilla SFTP set up for that server, works fine. - radzik

1 Answers

3
votes

The way to correctly set up a tunnel is the following:

ssh <user>@<host that has access to database> -L <local port>:<db server>:<remote port>

Then you connect to localhost:<local port>.

Example: Your db server db.mydomain.com runs on port 3306 but you cannot access it. You first need to ssh to another machine, myproxy.mydomain.com, as user "username" which has access to port 3306 on the db server. On that machine, try 

mysql -h db.mydomain.com -P 3306 -u <your db username> -p<your db password> <db name>

to verify the access to the db. Then you start by setting up the tunnel:

ssh [email protected] -L 3306:db.mydomain.com:3306

Once the SSH connection is established you can connect to your db by using the URL localhost:3306 or 127.0.0.1:3306.

What the tunnel is doing is redirecting traffic to port 3306 on your local machine to the remote db server's port 3306, using your proxy machine as a relay.