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 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, PostGreSQL, 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, PostGreSQL 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

PostGreSQL:

Likewise, for PostGreSQL, you would connect to server "localhost" on port "5440",
as if you were running the database server on your own machine.

In the case that you are already running software such as PostGreSQL on your own machine,
you can modify the local address to use port 5441.
(-L 5440:localhost:5441)

plink -l ssh_username -pw password -L 5440:localhost:5441 metawerx.net

* This makes plink listen on port 5441 instead, enabling you to keep running
PostGreSQL Server on port 5440
* You would then make connections to localhost:5441 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, PostGreSQL,
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