SSH (PLINK and PuTTY) Info/Tips for Metawerx Customers - v1.2

This document describes the use of PLINK and PuTTY and includes tutorials for remote connections to MySQL and SQL/Server as well as help on setting up a connection using PLINK. This information is intended for use by hosting customers of Metawerx Developer Hosting, however, it is useful for similar environments where a secure, encrypted remote connection is required.
Contents
1. Fast-Start Guide - MySQL 2. Fast-Start Guide - SQL/Server 3. SSH Explained - What are PLINK and PuTTY? 4. Starting a connection with PuTTY (simple guide for MySQL users) 5. Starting a connection with PLINK (the command line version of PuTTY)
1. Fast-Start Guide - MySQL
This is a quick getting started guide. For further information, see the other sections. If you get errors while connecting, see the MySQL Trouble-Shooting Guide. Download PLINK from http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html Quick link for Windows (use above link if this does not work): http://the.earth.li/~sgtatham/putty/latest/x86/plink.exe Open a Command Prompt Switch to the folder you put PLINK into (eg: CD \plink) Use the following command (replace YOURUSERNAME and YOURPASSWORD with your username and password): plink -l ssh_YOURUSERNAME -pw YOURPASSWORD -L 3307:mysql.metawerx.net:3306 metawerx.net This will connect and ask a question if this is your first connection: Update cached key? (y/n, Return cancels connection) Answer yes, a dollar sign ($) should appear. Leave this window open running until you are finished. Open a new Command Prompt Switch to your MySQL folder (eg: CD \mysql\bin) Use the following command (replace YOURUSERNAME and YOURPASSWORD with your username and password): mysql -hlocalhost --port=3307 -uYOURUSERNAME -pYOURPASSWORD YOURUSERNAME You are now connected to your live database on the metawerx server. Try this (don't forget the semicolon at the end): show tables; Or: select * from SOME_TABLE; Type QUIT to exit, then close both Command Prompts. You may also use GUI-based tools such as MySQL Query Browser or MySQLCC. Note that you will always need to have the PLINK command running in order to connect, to maintain the SSH tunnelled connection to the server. For faster execution of queries under 200k, or a series of small queries, consider using the metawerx online SQL/Console instead, as this will run directly on the metawerx servers.
2. Fast-Start Guide - SQL/Server
This is a quick getting started guide. For further information, see the other sections. Download PLINK from http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html Quick link for Windows (use above link if this does not work): http://the.earth.li/~sgtatham/putty/latest/x86/plink.exe Open a Command Prompt Switch to the folder you downloaded PLINK into (eg: CD \plink) Use the following command (replace YOURUSERNAME and YOURPASSWORD with your username and password, and remember your username must have ssh_ at the front like in the example below): plink -l ssh_YOURUSERNAME -pw YOURPASSWORD -L 1434:localhost:1433 metawerx.net This will connect and ask a question if this is your first connection: Update cached key? (y/n, Return cancels connection) Answer yes, a dollar sign ($) should appear. Leave this window open running until you are finished. Open a new Command Prompt Now we have to set up an ALIAS in SQL/Server Client Network Utility so your machine knows how to use the SSH tunnel. Go to Start -> Microsoft SQL Server -> Client Network Utility Switch to the Alias tab Click Add Enter the following details: Network libraries: TCP/IP Server Alias: Metawerx Server Name: localhost Untick the "[x] Dynamically Determine Port" checkbox Port: 1434 Click OK The following entry should now appear in the Alias list: metawerx TCP/IP localhost,1434 Click Apply then OK Open Enterprise Manager Right Click SQL/Server Group and select New SQL Server Registration Click Next The new "Metawerx" alias should appear in the "Available Server List" Double click it, so that it appears under "Added Servers" Click Next Select "The SQL Server login information that was assigned to me by the system administrator [SQL Server Authentication]" Click Next Select a Login option (automatic or manual, enter your normal metawerx username and password if you select manual) Click Next Select a group, or create a new group (this is just for arranging the new server on the Enterprise Manager list) Click Next, then Finish As long as PLINK is still running, Enterprise Manager will now connect and the new entry will appear in your server list. You are now connected to your live database on the metawerx server. Note that you will always need to have the PLINK command running in order to connect, to maintain the SSH tunnelled connection to the server. When you exit, first close Enterprise Manager, then close PLINK. Please do not leave the connection running when you are not using it, as SQL/Enterprise is a very "chatty" program. Query Analyser is much better for running queries, as it doesn't talk to the server continually. For faster execution of queries under 200k, or a series of small queries, consider using the metawerx online SQL/Console instead, as this will run directly on the server.
3. Introduction to SSH - PLINK and PuTTY
This document contains our PLINK/PuTTY documentation. Services such as MySQL, FireBird, SQL/Server and RSync are blocked from external access over the internet, to provide protection against malicious attacks and data encryption for the transfer of sensitive data. We use an SSH tunnel to provide access to these services remotely, so that they can still be accessed by our customers, over a secure encrypted connection. PuTTY is an SSH client capable of creating a "tunnel" from your machine to the server. Using PuTTY, you can connect to MySQL, SQL/Server and RSync from outside the server (eg: from your office or home). If you do not yet have SSH installed on your site, and you would like them to be installed, please contact support.
4. Starting a connection with PuTTY
This guide assumes you want to use the GUI version of PLINK. This is easier to set up and use, but more advanced users may prefer the command line version (see section 5 below). Download the latest version of PUTTY and PLINK from the following address: http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html As of writing this file, the latest version was 2.0.2. Ensure that you download at least version 0.53. Version 0.51 will not allow tunnelling. Install to a folder anywhere on your machine. If you are running MySQL already on your machine, stop it before continuing. * Hint: you can create a connection on a different port number to run MySQL locally at the same time. Open the folder and start PuTTY. Before trying to connect, go to the "Connection->SSH->Tunnels" option page. (Optional for MySQL users) Add a new "forwarding port" for MySQL. Source: 3307 Destination: mysql.metawerx.net:3306 Click [Add] The text "L3307 mysql.metawerx.net:3306" will appear in the "Forwarded Ports" list. * See the fast-start-guide for MySQL above for more info about MySQL (Optional for MySQL5 users) Add a new "forwarding port" for MySQL. Source: 3507 Destination: mysql5.metawerx.net:3506 Click [Add] The text "L3507 mysql5.metawerx.net:3506" will appear in the "Forwarded Ports" list. * See the fast-start-guide for MySQL above for more info about MySQL (Optional for SQL/Server users) Add a new "forwarding port" for SQL/Server. Source: 1434 Destination: sql.metawerx.net:1433 Click [Add] The text "L1433 sql.metawerx.net:1433" will appear in the "Forwarded Ports" list. * See the fast-start-guide for SQL Server above for more info about SQL/Server Now go to the "Session" page and enter Host: metawerx.net Port: 22 (the default) In the Saved Sessions field, type METAWERX and click Save. Now click "Open". A black window will be displayed. This is the PuTTY terminal, which must remain connected at all times while you are connected to the database. A dialog box will be displayed (PuTTY Security Alert). This is because it's the first time to connect to this server. Just hit YES. It will ask for your username, use the username provided by metawerx for "SSH Connections". (This will be your normal username with "ssh_" at the beginning. eg: ssh_cooljavastuff Password: (your normal password) If all goes well, you should now have something like the following displayed: Last login: Tue Oct 4 01:42:37 2005 from l2-202-89-171-18.iinet.net.au $ You are now connected over a secure connection "tunnel" to the metawerx server. Your computer has opened a port, which tunnels through PuTTY to the metawerx database port. You can now connect using MySQLCC or other similar tools. Tools like MySQLCC will think that MySQL is running on your local computer, when really, it is talking through the tunnel to the metawerx server. At any time, if you want to make sure PuTTY is definitely connected, click the PuTTY window and hit the ENTER key. A new $ prompt should show each time you hit the Enter key. Click here for instructions on getting started with MySQLCC
5. Starting a connection with PLINK (the command line version of PuTTY)
Download the latest version of PUTTY and PLINK from the following address: http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html As of writing this file, the latest version was 2.0.2. Ensure that you download at least version 0.53. Version 0.51 will not allow tunnelling. You can tell the version of plink or putty by running it from the command line without arguments. Install to a folder anywhere on your machine. You will find a series of tools in the folder, such as PLINK (which we will use in the example below) and PuTTY (the graphical version of PLINK). Start PLINK to start the SSH tunnelling system: plink -l ssh_YOURUSERNAME -pw YOURPASSWORD -L 873:localhost:873 -L 3307:mysql.metawerx.net:3306 -L 3507:mysql5.metawerx.net:3506 -L 3051:firebirder.metawerx.net:3050 -L 1434:sql.metawerx.net:1433 metawerx.net or putty -l ssh_YOURUSERNAME -pw YOURPASSWORD -L 873:localhost:873 -L 3307:mysql.metawerx.net:3306 -L 3507:mysql5.metawerx.net:3506 -L 3051:firebirder.metawerx.net:3050 -L 1434:sql.metawerx.net:1433 metawerx.net For example, if your username is MyCompany and your password is MyPassword, use the following command: plink -l ssh_MyCompany -pw MyPassword -L 873:localhost:873 -L 3307:mysql.metawerx.net:3306 -L 3507:mysql5.metawerx.net:3506 -L 3051:firebird.metawerx.net:3050 -L 1434:sql.metawerx.net:1433 metawerx.net or putty -l ssh_MyCompany -pw MyPassword -L 873:localhost:873 -L 3307:mysql.metawerx.net:3306 -L 3507:mysql5.metawerx.net:3506 -L 3051:firebird.metawerx.net:3050 -L 1434:sql.metawerx.net:1433 metawerx.net Notice that the username is specified as "ssh_username", not just "username". This should start a shell in the command window, listening on localhost, tunnelling to metawerx.net. The shell does nothing, but plink serves a secondary purpose which is allowing you to tunnel through to ports on the server. This is how you will connect to local services on the server "metawerx.net" using an external client. What this actually does, is open 3 ports on your local machine (873,3306,1433). When you connect to these ports using RSYNC, MySQL clients, or SQL/Server clients, PLINK will tunnel you through to the server (metawerx.net) which is running an SSH server, which in turn will make a local connection to port 873, 3306 or 1433 locally on the server. All traffic you send to your local machine's port (localhost), is encrypted and sent through to the port on the server (metawerx.net). All traffic is encrypted, and you need a password to connect using SSH, which is more secure than a plain connection. After running this command, with the Plink window open, you can connect to MySQL, RSYNC, FireBird or SQL/Server by making a connection to localhost on your own machine, instead of connecting to the server directly. MySQL 4: For example, in MySQL Console, you would connect to server "localhost" on port "3307", as if you were running the MySQL server on your own machine, on port 3307. In the case that you are already running software such as MySQL on your own machine, (which will be on port 3306), we are using the port 3307 here. (-L 3307:mysql.metawerx.net:3306) plink -l ssh_username -pw password -L 3307:mysql.metawerx.net:3306 metawerx.net * This makes plink listen on port 3307 instead, enabling you to keep running MySQL on port 3306 * You would then make connections to localhost:3307 to access the server * Example connection command for mysql.exe: mysql -hlocalhost -P3307 -uUSERNAME -pPASSWORD MySQL 5: For example, in MySQL Console, you would connect to server "localhost" on port "3507", as if you were running the MySQL server on your own machine, on port 3507. In the case that you are already running software such as MySQL on your own machine, (which will be on port 3306), we are using the port 3507 here. (-L 3507:mysql5.metawerx.net:3306) plink -l ssh_username -pw password -L 3507:mysql5.metawerx.net:3306 metawerx.net * This makes plink listen on port 3507 instead, enabling you to keep running MySQL on port 3306 * You would then make connections to localhost:3507 to access the server * Example connection command for mysql.exe: mysql -hlocalhost -P3507 -uUSERNAME -pPASSWORD FireBird: Likewise, for FireBird, you would connect to server "localhost" on port "3051", as if you were running the FireBird server on your own machine. In the case that you are already running software such as FireBird on your own machine, (which will be on port 3050), we are using port 3051 here. (-L 3051:firebird.metawerx.net:3050) plink -l ssh_username -pw password -L 3051:firebird.metawerx.net:3050 metawerx.net * This makes plink listen on port 3051 instead, enabling you to keep running FireBird Server on port 3050 * You would then make connections to localhost:3051 to access the server PuTTY does the same thing, but through a GUI. You can't use it in a batch file, and it forces you to enter the password every time you start the connection. Try it out if you like, personally I prefer plink, the command line version. The easiest way to set it up, is to have a batch file which executes your connection command and place this in easy reach (eg: the start menu or desktop). Whenever you need to connect, start PLINK then the client (MySQL, RSync, FireBird, SQL/Enterprise etc..), do what you need to do, then close the client, and then finally close the PLINK window.
COPYRIGHT NOTICE
All material is (c)Copyright Neale Rudd, Metawerx Pty Ltd, 1997-2014. If you found this information helpful, additional guides can be found at http://www.metawerx.net/admin under the 'Resources' link.
END OF DOCUMENT