Skip to main content Skip to navigation

How do I remotely connect to the database on my server?

Connecting from an application on another server

Tell us about that and we can put in place the relevant configuration to make that work.

Connecting using an application on your desktop/laptop

The best way to do this is over an SSH tunnel. Reasons to use an SSH tunnel:

  • All communication is done over an encrypted channel.
  • It works from anywhere SSH connections are accepted from, so it doesn't matter if the IP address of your desktop/laptop changes.
  • The tunnel needs to be established using a University username, and that only works if login has been enabled for that username. (If there is someone who you want to be able to remotely connect to the database on your server, but you do not want them to be able to log in to the server and modify files, we can limit connectivity for a username to the ability to establish a tunnel.)
  • It does not require any configuration changes on the server.

See also: How do I request that someone else be allowed to log in to my server?

The guides below use port 3306 which is the port MariaDB listens on. If you want to connect to a Postgres database replace 3306 with 5432.
Using Linux or macOS

First create the tunnel.

$ ssh -L 9999:localhost:3306 u1234567@foo.lnx.warwick.ac.uk -N

N.B. After being prompted for your password and entering it correctly, there will be no further output from the command.

That command sets up a tunnel from local port 9999 to port 3306 on foo.lnx.warwick.ac.uk, authenticating as the user u1234567. You can confirm this has worked by connecting to port 9999. You should see something like

$ telnet localhost 9999
Trying ::1...
Connected to localhost.
Escape character is '^]'.
R
5.5.56-MariaDB�_8;\@jv1��.4X?rC5!Af^$mysql_native_password

If you don't have the telnet client installed you can also test with curl.

$ curl telnet://localhost:9999
Warning: Binary output can mess up your terminal. Use "--output -" to tell 
Warning: curl to output it to your terminal anyway, or consider "--output 
Warning: " to save to a file.
$

The above output indicates curl was able to connect to port 9999 but didn't display the output it received. Compare to a failed connection

$ curl telnet://localhost:1234
curl: (7) Failed to connect to localhost port 1234: Connection refused
$

Once you have established the tunnel, configure your application to connect to the database via 127.0.0.1 port 9999.

Using Windows

If you have Windows 10 with the 1803 update you should have an SSH client installed which you can use at a command prompt per the Linux and macOS instructions above. Otherwise use PuTTY as described below.

An SSH Tunnel can be established using PuTTY. The following is based on an IT Services managed Windows desktop with PuTTY installed from Software Centre.

Locate Putty in the Start Menu, right click it's icon and select "Open file location".

winsshtunnel_01.jpg

(click images to enlarge)

Create a Shortcut to the PuTTY executable

winsshtunnel_02.jpg

and move the new Shortcut to your Desktop. Right click the new Shortcut, select Properties and append to the Target field

-L 9999:localhost:3306 u1234567@foo.lnx.warwick.ac.uk -N

winsshtunnel_03.jpg

Click OK, then double click the Shortcut to initiate the connection. N.B. After being prompted for your password and entering it correctly, you will not get a command prompt. Once the connection is established you can verify the tunnel is established by using PuTTY to establish a telnet connection to localhost and port 9999

winsshtunnel_04.jpg

You should see something like this

winsshtunnel_05.jpg

Then configure your application to connect to the database via 127.0.0.1 port 9999.