Search This Blog

Wednesday, June 3, 2009

Using Navicat SSH Tunneling for secure MySQL Server management

Many of the web hosting companies has blocked port 3306 to prevent access from outside, to defend against from security threats. As a consequence, users are required to use web-based client to access their MySQL Server provided by the hosting companies. These web-based clients are usually not visually appealing with annoying page refreshes.

However, with the increasing demand from users to connect from remote MySQL clients, some web hosting companies provides SSH Connection which allows user to login remotely across the Internet and support connection through the software based clients.

SSH stands for Secure Shell and is a communication protocol for connecting to remote computers over TCP/IP. Encryption provides confidentiality and data integrity, and SSH uses public-key cryptography to authenticate the remote computer and to allow the remote computer to authenticate the user if necessary.

There are several benefits to using SSH:

  • Connect to a MySQL server from behind a firewall when the MySQL server port is blocked.
  • Automate the authentication of users, no passwords sent in plain text to prevent the stealing of passwords.
  • Offers Multiple strong authentication methods that prevent such security threats as spoofing identity.
  • Offers Encryption and compression of data for security and speed.
  • Secure the file transfer.


Setting up a SSH Connection to your MySQL Server with Navicat

To successfully establish a SSH connection, set the SSH connection properties in the corresponding boxes: Host name/IP address, Port number, User name, Authentication Method and Password.
    1. Click or choose File -> New Connection to set up the Connection Properties.
    2. Select the SSH tab and enable Use SSH Tunnel.
    3. Fill in the required information in the SSH Tab:
      Host name/IP address
      A host where SSH server is activated.

      Port
      A port where SSH server is activated, by default it is 22.

      User name
      A user on Linux machine. (It is a Linux user. It is not the user of MySQL Server.)

      Authentication Method
      Choose Password Authentication

      Password
      The password of your user account in linux



4. In the General Settings Tab, the settings should be set relatively to the SSH server. For example, host_of_mysqldatabase shown below is the host address, which is provided by your hosting company of your remote MySQL database.
    Connection Name
    A friendly name to best describe your connection.

    Host name/IP
    address The host where MySQL Server is located in point of view SSH server. If SSH and MySQL Server are on the same machine, it is equal to SSH Host, or may be 'localhost'.

    Port
    The port of MySQL Server on Remote Host, by default it is 3306.

    User name
    The username of your MySQL Server.

    Password
    The password of your MySQL user.

By clicking OK, the SSH connection is made.

Hosting Companies providing SSH Connections
Listed below are some hosting companies which provide SSH connections and remote MySQL Connection can be setup with Navicat.

1&1

1and1 Hosting

1Host Web Hosting

3FN.net

A2 Hosting

Advanced Network Hosts

AN Host

AN Hosting

Aplus.net

Apollo Hosting

Argon Hosting

Blue Host

Bounceweb

Cirtexhosting

Crucial Paradigm

Dotservant.com

Dreamhost

eChristian Web Hosting

FastDomain

Flux Services

Hagen Hosting

HostGator.com

HostHead

Hostican

Hostland

HostMonster.com

Hostpapa

HostRocket.com

Imhosted

InMotion Hosting

Intermedia

iTeraWeb Solutions

IX Web Hosting

Joyent

Liquid Web

Lunarpages.com

ME Webhost

Media Temple

Naked Hosting

Netfirms

Net-Trend

Omnis Hosting

PowWeb

Practical Webhost

Server Pronto

ServerPro Web Hosting

SimpleHelix

Start Logic

Superbhosting.net

Tbhost.com

techhosting.com

The Host Group

ThinkHost

UK2NET

Velcom.com

Verio

Vision Web Hosting

Web Hosting Buzz

Web Hosting Pad

WebWizards.net

WestHost

WireNine

YourServing

Your-Site.com

In the following section, we have selected one of a popular hosting companies and show how a SSH Connection to the MySQL Database can be established with Navicat.

Tutorial on how to establish SSH Connection to a MySQL database hosted in Dreamhost

DreamHost provides shell access to all of its customers, but it needs to be specifically enabled for each user added to a customer's account. By default, the Type of the user is set to be “FTP”, you will need to enable the shell access before you can connect through SSH.

Enabling the Shell access for an existing user:

    1) In your Control Panel, Visit User > Manage Users.
    2) Click the [ edit ] link located next to the user you'd like to enable shell access for.



    3) Check the 'Enable ssh/telnet?' box for that user.
    4) Leave the 'Type' as it is unless you need it to be something other than bash.
    5) Click Save
Keep in mind that it takes about 20 minutes for this change to take effect.


After changing the user account to Shell Type, you’ll also need to grant rights for your local machine to connect to the database server, before you can connect remotely by Navicat. This must be done for all MySQL user accounts you plan on logging in from your local machine.

Granting rights for your SSH Host:


    1) Login to your Dreamhost Control Panel
    2) Select the Manage MySQL link under the Goodies section.
    3) Under the Database(s) on this server section, find the desired database and click the username you wish to grant access to.
    4) Under the section titled Allowable Hosts, type in the domain or subdomain or your IP Address the machine which your SSH Host identifies itself as on the public internet.
    5) Click Save

You have completed the settings required in your Dreamhost account, now you will go to Navicat to create the Connection.

In Navicat, click the Connection button to New a Connection.



In the General Tab of the Connecting Setting box,

Hostname/IP address: the hostname of your mysql server at dreamhost, you can check this at Control Panel > Goodies > Manage MySQL
Port: 3306 (this is the default port number for the MySQL Server)
Username: username with access right granted in Manage MySQL section
Password: password



In the SSH Tab of the Connecting Setting box,

Hostname / IP Address: machine_name.dreamhost.com
Port: 22 (this is the port for SSH connection)
Username: username which you have changed the account to Shell type in Dreamhost
Password: password



To test if the Connection settings are correct, you may click the “Test Connection” button to check.

Connecting to your MySQL Database from remote client Navicat offers you a greater flexibility on working with your data in MySQL. With Navicat, you can easily import your data in to your MySQL database from various file formats, build queries visually, set a schedule to perform backup, generate report from your raw data and more.

For more information on how Navicat helps you to manage your MySQL database effectively, please visit here: http://mysql.navicat.com/details.html

No comments:

Post a Comment