How to setup a SSH tunnel to access MySQL from outside.

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • KyleC
    Senior Member
    • Mar 2004
    • 291

    How to setup a SSH tunnel to access MySQL from outside.

    Question:

    Is direct connection to Dathorn MySQL server possible, without need to use web style admin?

    Answer:

    Yes, but...

    For security reasons native remote direct connection to MySQL server is disabled. So, you cannot use MySQLFront or other similar MySQL admin application to connect directly. However, since there is resonable need for such connections Dathorn allows connection using secure tunneling protocol SSH.


    Question:

    SSH? Tunnelling?

    Answer:

    SSH (Secure SHell) is protocol which allows user to remotely connect to server and use specified service in same manner as he is directly connected on local console. This proces is called tunelling. SSH connection may be imagined as communication tunnel. You send information through one side of tunnel and it exits on other side. Tunnel is secured by excellent security methods.

    How it works: When you establish a tunnel, it's like you translate remote server to yourself (or if you like, translate yourself to remote server). For an example, lets talk about accessing MySQL server. It receives connections on port 3306. When you establish SSH tunnel, any traffic you send to local port 3306 on your computer will be translated to remote server's port 3306 and vice versa, any reponse from server will get to you as coming from your local computer.

    Look it this way: when you establish tunnel to remote server it looks like as you started server on your computer. This means that you may use any software to access server and set it up as you have server on your computer. Tunnel will do all translation to remote server. Security is not an issue since SSH tunnel is highly secured.

    If you want to find more info take a lok at http://www.ietf.org/ids.by.wg/secsh.html or seerch Google.


    Question:

    Ok, got it, but how to implement?

    Answer:

    First, you have to setup SSH tunnel.

    There are several solutions. Most popular solution is to use freeware PuTTY (OpenSSH) package. It contains application plink.exe which does all the SSH tunneling job. Actually, that exe is only one file needed from the whole PuTTY package. Unix users should check for OpenSSH. Since I am Windows user cannot help more about Unix usage of SSH.

    plink.exe is console application and it must be started from command prompt. It receives lots of parameters but very few are needed to establish connection.

    It is best to create batch fle to run SSH tunnel like:

    --- start of plink.bat ----
    plink.exe -L 3306:localhost:3306 username@domain
    --- end of plink.bat ----

    Replace username with cpanel username for domain which databases you want to access. Replace domain with domain. For instance if you want to access domain mydomain.com with cpanel username admin you will run plink as plink.exe -L 3306:localhost:3306 admin@mydomain.com.

    Parameter -L sets which local port to listan and where to (host and port) forward everything received on local port. For most cases specified setup will do the job. It instructs plink to listen on port 3306 (default MySQL server port). You may understand this as command to imitate MySQL server on local computer. Everything it receives on that port will be sent to tunnel and when it exits on other side will be sent to host address localhost and port 3306. This means when you try to access MySQL server on local computer, information you send will be forwareded to remote server. Automaticaly, any responce from server will be forwarded back to your computer. You may say this is perfect imitation of local MySQL server.

    When you start plink it will connect to remote host to establish tunnel. It will ask you for your cpanel password during the process. After it establishes link it will stay in open command promp. Do not close this window. It must stay open. You may minimise it.

    Finally when you want to stop tunnel, press Ctrl-C in plink console and wait for it to stop operation. Then you may close command prompt.



    After tunnel is set you have to set your applications to use it.

    Setting is actually very easy. Just pretend MySQL server is on your local computer but use username and password set on remote server. This means you set MySQL server address as localhost, or 127.0.0.1, or any other address your local computer owns. For username and password use mysql username and password you created using cpanel. Port is 3306 and database name is not needed to be set. Set this parameters and try to establish connection. It should work.


    Question:

    Hey this works. Great! However, this console window is annoying. Can I remove it?

    Answer:

    Actually yes. There are several Windows applications that can establish SSH connections. Most of them relay of plink.exe. Most of them are also not free. There is even a MySQL admin application that can establish SSH connection internally (sorry, cannot remember which one).

    There is fine solution called SSH Tunnel Client, made by Delight Software gmbh, http://www.delight.ch. Site is whole in german.

    SSH Tunnel Client is comercial but free for noncomercial use (no nags, no advertizement, no nothing, it simply works). Version I tried is 2.0.2.36. It is front end for plink.exe. You have simple but good user interface to set application and several connections. it sits in systray and uses plink without console window. It even shows you if conection is established or not by changing its icon in systray. In one word excellent tool. You may download it from here

    To set up connection start application, go to Main Menu / Tunnel / Add and fill fileds with apropriate information:

    Tunnel name: MyNewTunnel (or whatewer you like)
    SSH HOST: mydomain.com
    SSH Port: 22
    Local port: 3306
    Remote Host: localhost
    Remote port: 3306
    Username: admin
    Password: enter your password
    Check SSH2 radio button

    Save setup and double click newly created icon to start connection. After connection is established icon will be enriched with yellow locker. You should check in Windows processes (Task manager / Processes) if plink.exe is running. If it is there everything is ok and you may use the tunnel.



    Question:

    Awesome! There is one glitch here. I do run local MySQL server and port 3306 is occupied!?!?!

    Answer:

    Solution is easy. Change listening port for plink to any port number that is free. I use 3307 as it is easy to remember. For that case you should run plink as

    plink.exe -L 3307:localhost:3306 username@domain

    This means it will listen onport 3307 but forward to port 3306. You also have to change MySQL server port in your admin tool's connection parameters to port 3307.

    If you need tunnel connections to several remote MySQL servers, you have to use different listening port number for each of them. You may have several tunnels ast the same time as long as they do not use the same listening port. Use SSH Tunnel Client it will do this smoothly for you.


    Question:

    plink.exe seems as universal tool. May I use it for other services, not just MySQL?

    Answer:

    Sure. All you have to do is to set listening and forwarding ports and host parameters. And be sure that remote host supports SSH. Dathorn is. I am sure that SSH is required for Shell access and MySQL. Mail is also availsble via secured connection but that is manageable within mail clients and there is no need to use SSH. I do not know if there are other services using this, at least I did not need them yet.

    By Pedja
    -Kyle

Working...