Month: May 2017

CONFIGURE SQLYOG TO CONNECT TO A DATABASE THROUGH AN SSH TUNNEL

KEYWORDS
sqlyog, sql yog, ssh, encryption, vpn alternative, ssh tunnel, mariadb, mysql, authentication

SUMMARY
Outside connections to databases can be a real pain as databases should not be directly addressed by the general internet. Databases sit behind a firewall and are accessed normally through web browsers that connect to web servers, which in turn connect to app servers, which then connect to databases.

To access databases from the general internet, one must bypass the firewall. A couple of ways of doing that (and there are other ways) are to use a VPN (Virtual Private Network) or use an SSH (Secure SHell) Tunnel. Tunnel implies a secure connection.

Both of these methods bypass the firewall and encrypts the connection to insure the connection remains secure. Both methods take some upfront setup. VPN in installing the software and the VPN keys. SSH is generating the SSH keys.

The scope here is how to get started with SSH within SQLyog. The SSH Tunnel is really just the first of two connections to the database. The first connection is a network connection from the client side to the SSH Relay Host (SSH Tunnel Host, or several other names) to bypass the firewall. Then there is a normal database connection from the SSH Relay Host to the database server/s.

GENERATE SSH KEYS

Download Putty

Right click on on it in the windows launch tray, then select ‘Run PuTTYgen’

Select ‘Parameters’, ‘Type of key to generate’ of ‘SSH-2 RSA’. 2048 bits in the generated key. Click the [Generate] button.

Browse to a directory to permanently save your keys. Right click and select ‘New Text File’,  rename the file to ‘public_key.pem’, double click on the file; if necessary, ‘select program from a list  of installed programs, and select ‘notepad’. On the PuTTY Key Generator screen, right click in the ‘Public key for pasting…’,  choose ‘select all’, then right click again and choose ‘copy’, then paste that into (notepad) public_key.pem. File/Save, Close notepad.

Enter a pass phrase twice, Click [Save public key], enter the proper directory and name (public_key.ppk). Click [Save private key], enter the proper directory and name (private_key.ppk)

CONFIGURE SSH TUNNEL SERVER

• This is as exercise outside the scope of this blog.

TEST SSH TUNNEL

• To test the ssh tunnel, launch putty
• Enter the SSH Host Address in the Host Name (or IP address) field
• In the Connection/Data screen, add an Auto-login username.
• In the Connection/SSH/Auth screen, add a path to Private key file for authentication that references your private key (.ppk).
• In the Session screen, click [Open]
• You should be logged in after optionally entering the password that encrypts your private key.

SQLYOG CONNECTION

Start SQLyog Choose File/Connect, then click the [New] button in the upper left. Enter a connection name. Click OK. Click the SSH tab, check the ‘Use SSH Tunneling’ chekbox, enter the SSH Host address, the username, SS port (normally 22),  choose the ‘public key’ radio button, enter the pass phrase for you private key (see above), and the path to the private key file (private_key.ppk)

Click the MySQL tab, enter the database host, database username, database password, enter the database port, and the database name to complete the database connection.  Click the [Save] button at the top of the window.