Metawerx Java Hosting Small Logo

Metawerx Support - How to set up a remote SQL Server connection

Downloading the tools

For these instructions, you will need:

  • plink on Windows, or ssh on Linux/Unix - to establish the tunnel - download here
  • a SQL Server client, we recommend SQL Server 2005 Management Studio Express, or SQL Enterprise Manager for SQL7/2000.
  • preferably a cable/ADSL connection - GUI admin tools are quite 'chatty' and access will be slow over dial-up

Download and install these tools before continuing.

Starting the tunnel

We provide access to your SQL Server database over an SSH tunnel for security. You will need to start the tunnel before connecting with your client.

Note that you cannot have SQL Server running locally on your own machine on port 1433 (the default port) if you want to connect with Management Studio. This is because Management Studio seems to only like connecting on port 1433. Therefore, you should stop your own instance of SQL Server before continuing with this tutorial.

On Windows, the tunnel is established using plink. On Linux, the ssh tool is used. These are both command line tools.

You will have been provided with:

  • a metawerx username, which will be referred to as metausername below
  • a metawerx password, which will be referred to as metapassword below
  • a SQL Server server name and port, which will be referred to servername:serverport below
    • for SQL 7 this will be sql.metawerx.net:1433
    • for SQL 2005 this will most either be sql2005-1.metawerx.net:1433, sql2005-1-B.metawerx.net:1433 or sql2005-2.metawerx.net:1433

You will also use a new username for the SSH connection. This will always be the same as your normal metawerx username, but with ssh_ on the front. eg: ssh_neale

To start the tunnel, execute the following command on Windows:

plink -C -v -l ssh_metausername -pw metapassword -L 127.0.0.1:1433:servername:serverport ssh-meta1.metawerx.net

or on Linux:

ssh -Cv -L 127.0.0.1:1433:servername:serverport ssh_metausername@metawerx.net

Example, if your username is cassie, password cassiepass, server sql2005-1.metawerx.net:1433, and you are using Windows, use the following command:

plink -C -v -l ssh_cassie -pw cassiepass -L 127.0.0.1:1433:sql2005-1.metawerx.net:1433 ssh-meta1.metawerx.net

This will open a tunnel between your localhost (127.0.0.1) port 1433, and our SQL Server on port 1433.

  • The first time you connect, or at other times we regenerate our key files, you will be asked to store the key in your cache. Answer "y" (yes) to continue.
  • All data transmitted through the tunnel will be encrypted by SSH
  • A prompt will appear, awaiting commands. Do not enter any commands - just leave the window open and minimize it.
  • When you close this window, the tunnel will be closed, and your connection will be lost
  • You are now ready to start your SQL Server client
  • If you want to know how this works, or are creating your own tunnel, see the SSH Tunnel page for a full explanation


Using SQL Server 2005 Management Studio Express

  • Ensure the tunnel is running, then start Management Studio
  • Enter the following details:
    • Server Type: Database Engine (this setting only appears in Management Studio SP2 and above)
    • Server Name: 127.0.0.1
    • Authentication: SQL Server Authentication
    • Username: your metawerx username
    • Password: your metawerx password
  • Click OK
  • Management Studio can work in Tabbed Mode or MDI Mode. In Tabbed Mode, multiple panels and toolbars are shown in the same window. MDI splits each section into separate windows.
  • In Tabbed Mode, three panels appear after connecting, your Registered Servers list, the Object Explorer and a summary of the current database.
  • Click Databases in the Object Explorer, find your database, and then right click the Tables item.
  • New tables can be added here, or structures of existing tables can be edited.
  • To run SQL queries, select the New Query button on the task bar at the top of the window. The main pane will clear, allowing the entry of raw SQL. Click the Execute button on the task bar to run the query. Results and messages are displayd underneath the query panel.

Using JDBC and JNDI

  • To connect to the database from a Java application running on your local machine, you can connect to 127.0.0.1:1433, which is the IP and port your tunnel is listening on.
  • An example JDBC URL is as follows:
jdbc:mysql://127.0.0.1:1433/YOUR_DATABASE_NAME

See Also

--Neale Rudd

navigation
metawerx specific
search
Share
tools
help

referring pages

Share