SSH (PLINK and PuTTY) Info/Tips for Metawerx Customers

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.

If you found this information helpful, additional guides can be found at
http://www.metawerx.net/admin under the 'Resources' link.

END OF DOCUMENT