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

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 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:localhost: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.

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 server.


 
===========================================================================
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 RSync/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 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 0.54.
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 3306:localhost:3306 -L 3050:localhost:3050 -L 1433:localhost: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 3306:localhost:3306 -L 3050:localhost:3050 -L 1433:localhost: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:

For example, in MySQL Console, you would connect to server "localhost" on port "3306",
as if you were running the MySQL server on your own machine.

In the case that you are already running software such as MySQL on your own machine,
you can modify the local address to use port 3307.
(-L 3307:localhost:3306)
 
	plink -l ssh_username -pw password -L 3307:localhost: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

FireBird:

	Likewise, for FireBird, you would connect to server "localhost" on port "3050",
	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,
	you can modify the local address to use port 3051.
	(-L 3051:localhost:3050)
	 
	plink -l ssh_username -pw password -L 3051:localhost: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
	* Example connection command for mysql.exe:
		mysql -hlocalhost -P3307 -uUSERNAME -pPASSWORD

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.