Using Remote Desktop or Tunneling to connect to IISQLSRV

Why do we need to use tunneling or remote desktop?

Some of you may want to connect to our SQL Server database on IISQLSRV from your computer at home. However, for security reasons, it is not possible to directly connect to IISQLSRV from outside the CSE network. All connections must go through a terminal server.

However, it is possible to connect via remote desktop to a server in the CSE network or 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. The tunnel can be established as long as you have an SSH connection to one of our department's linux machines.

Connecting via Remote Desktop

Instructions on connecting to a terminal server via several remote desktop clients is described here.

Creating a tunnel to IISQLSRV

For our class, we can use this tunnel in two ways: First, we can install SQL Server Management Studio (the client program for SQL Server) locally, connect to IISQLSRV through the tunnel, and directly run SQL queries using a graphical interface. Second, we can write a Java program which establishes a JDBC connection to IISQLSRV through the tunnel. In this case we do not need to install SQL Server locally.

In both cases, we first need to create a tunnel. If you are running Windows, you can use a GUI SSH client. If you have a Mac or Linux machine, you can use the command line SSH command.

Using a GUI SSH client

  1. Start your SSH client. If you don't have an SSH client installed, you can download Tectia (formerly called SSH Secure Shell) from http://www.washington.edu/uware. The following instructions are based on SSH Secure Shell, but it should be straightforward to apply them to other SSH clients that support tunneling.
  2. Go to Edit > Settings > Tunneling, and add a new outgoing connection. Choose the following settings:
    Display Name: SQL
    Type: TCP
    Listen Port: 1433
    Destination Host: iisqlsrv.cs.washington.edu
    Destination Port: 1433
  3. Save, and then connect to any department linux machine that you have access to from home, for example attu.cs.washington.edu.
  4. Leave the SSH connection window open. TCP port 1433 on IISQLSRV is now being forwarded to port 1433 on your local machine.

Using Command Line SSH

Open a shell and run the following command:
ssh -L 1433:iisqlsrv.cs.washington.edu:1433 attu.cs.washington.edu

Connecting using SQL Server Management Studio 2008

Before connecting to IISQLSRV with 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 UW 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
Make sure your SSH connection remains open while you are using Management Studio.

Connecting using JDBC

You can also connect to IISQLSRV using JDBC. Again, ensure that you have SSH connection and set up a tunnel as explained above. In your dbconn.config, you can then use the following JDBC connection string
"jdbc:sqlserver://127.0.0.1;database=imdb_new;"
You do not need to install SQL Server on your local machine, if you are connecting using JDBC; you only need the JDBC driver for SQL Server.