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 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.
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.
You first need to install SQL Server 2008 locally (note: SQL Server 2005 does not allow you
to connect to SQL Server 2008). You can download an evaluation version from Microsoft.
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.
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 Java code, you can then use the following
JDBC connection string
static String connUrl = "jdbc:sqlserver://127.0.0.1;database=imdb;";
You do not need to install SQL Server 2008 on your local machine, if you are connecting
using JDBC.