Connecting to IISQLSRV from home

How can I connect to IISQLSRV from home?

If you are working on CSE 444 projects from home, you might need to connect to our SQL server, IISQLSRV. However, for security reasons, it is not possible to directly connect to IISQLSRV from outside the CSE network.

Despite this restriction, there are two ways you can connect to IISQLSRV. One is to connect to a computer in CSE that has Management Studio, and access IISQLSRV from there. Another is to use SSH to create a TCP tunnel, which redirects a TCP connection to a local port on your computer to a remote IP and port, in this case IISQLSRV's port 1433. You can create the tunnel as part of a normal SSH connection to a CSE Linux computer.

Connecting via Remote Desktop

You can use Remote Desktop to connect to a Windows machine in the CSE network, and use that computer to connect to IISQLSRV. This is much easier than setting up a TCP tunnel. However, you'll need a fast Internet connection to use Remote Desktop comfortably. In addition, in some projects you will be asked to write Java programs that connect to IISQLSRV with JDBC, and such programs will have to be run on the Windows machine you are connected to, rather than your own.

CSE support maintains two Remote Desktop servers, aria.cs and aqua.cs, which both have Management Studio and thus are ready to connect to IISQLSRV. However, these computers cannot access IISQLSRV over JDBC. Support also discourages using aria and aqua for class assignments, because they are shared by many people and occasionally become overloaded.

Connecting via an SSH tunnel to IISQLSRV

Creating a TCP tunnel to IISQLSRV has two advantages over using Remote Desktop: you can write a Java program and have it connect directly to IISQLSRV without having to rely on what's installed on the Remote Desktop machine, and you don't have to worry about the slowness of your Internet connection.

However, setting up a TCP tunnel is a bit harder than using Remote Desktop. You need to do the following:

  1. Use an SSH client to login to a CSE Linux machine, but also set up the connection to tunnel IISQLSRV's port 1433, through the Linux machine, to your computer's port 1433.
  2. Set up programs that connect to IISQLSRV to talk to your local computer (127.0.0.1) instead of IISQLSRV (iisqlsrv.cs.washington.edu).

If you are running Windows, you can use PuTTY, a Windows SSH client. If you have a Mac or Linux machine, you can use the command line SSH command.

Creating a tunnel using PuTTY

  1. In the initial "PuTTY configuration" window, specify host name attu.cs.washington.edu (or another CSE Linux host you can access):
    Host name: attu.cs.washington.edu
  2. Select Connection → SSH → Tunnels in the left hand pane.
  3. Enter "Source port": 1433 , and "Destination": iisqlsrv.cs.washington.edu:1433 , then click "Add." The window will now look like this:
    Forwarded ports: L1433 -> iisqlsrv.cs.washington.edu:1433
  4. Select Connection → SSH, then check the box labeled "Don't start a shell or command at all":
    Checked the box 'Don't start a shell or command at all'
  5. Click Open to connect, and enter your username and password. If you get nothing in response, not even an error message, then you are connected:
    Connected to attu - no messages from server
  6. Leave the window open until you want to close the tunnel.

Creating a tunnel using command-line SSH

Open a shell and run the following command:
ssh -N -L 1433:iisqlsrv.cs.washington.edu:1433 YOUR_CSE_USERNAME@attu.cs.washington.edu
Similar to PuTTY, the ssh program will appear to hang after you type your password, with no messages (not even an error message). To close the tunnel, press Ctrl-C to exit ssh.

If you'd like the tunnel to persist until you log out, add an extra -f option to ssh:
ssh -f -N -L 1433:iisqlsrv.cs.washington.edu:1433 YOUR_CSE_USERNAME@attu.cs.washington.edu
The ssh program will now appear to exit immediately, but is actually running in the background and will keep running even after you exit the shell.

Connecting using SQL Server Management Studio

Now that you've set up the TCP tunnel, you can connect to IISQLSRV over the tunnel by pointing your client at the local machine (127.0.0.1). To connect using SQL Server Management Studio, you need to install SQL Server locally, since Management Studio comes with SQL Server. You can download an evaluation version from Microsoft, or you can download a perpetually licensed version through CSE's Microsoft software program.

You can then start SQL Server Management Studio locally and choose the following connection settings:

Server Name: 127.0.0.1
Authentication: SQL Server Authentication
User: YOUR IISQLSRV USERNAME
Password: YOUR IISQLSRV PASSWORD

Here, the server name changes from iisqlsrv to 127.0.0.1, but the other settings are the same as if you were connecting from within the CSE network.

Remember to keep your SSH connection open while you are using Management Studio.

Connecting using JDBC

You can also connect to IISQLSRV using JDBC. Again, ensure that you have an SSH connection and set up a tunnel as explained above. You can now connect using a JDBC connection URL like the following:

jdbc:sqlserver://127.0.0.1;database=imdb

where the server name changes from iisqlsrv to 127.0.0.1, but other parameters stay the same.

You do not need to install Management Studio on your local machine, if you are connecting using JDBC; you only need the JDBC driver for SQL Server.