Web Development Home
CS U675 & CS G175
http://www.ccs.neu.edu/teaching/web/
Introduction Syllabus Requirements and Expectations
Startup Accessing SQL Connection Strings
Connecting to SQL 2005 Server
This page consists primarily of screen snapshots from Rasala’s laptop that show the steps for connecting to the Microsoft SQL 2005 Server installed at CCIS using the program SQL Server Management Studio. The same technical information may be used to set up a SQL connection from within Visual Studio 2008.
The SQL Server in CCIS is located at the following internet address:
MSSQL2005.development.ccs.neu.edu
How you access the server depends on whether you are inside the firewall, that is, in the lab, or if you are using an external system such as your own personal machine.
These notes assume that you have already registered for an ASP.NET account and for a SQL account and that you have received the auto-generated SQL password. If you have not done this, then first go to Startup
Connecting to SQL 2005 Server in the Lab
If you are working in the lab on a Windows machine, you can launch SQL Server Management Studio and click Connect in the lower left of the menu bars. You will then see a connection dialog which should be filled in as follows:
So the Server Type is Database Engine, the Server Name is the internet address listed above, the Authentication is SQL Server Authentication, the Login name is your usual username, and the Password is your special SQL password.
I strongly urge you not to click the Remember password check box on a lab machine!
Go to What You See After Connection to see approximately what a successful connection will look like.
Setting Up the SHH Tunnel for Remote Access
If you are working remotely, you must use a Secure Shell (SSH) tunnel to access the SQL 2005 Server. This tunnel should be added to the tunnel list in the settings you use to get to denali or any similar UNIX machine in CCIS. Do not use the SSH profile that gives you secure FTP access to the development server for this SQL tunnel since this mode will not work.
In SSH, pick the profile you use for standard logins. This is either “Quick Connect” or some profile you have defined and named.
In the Profiles menu, pick Edit Profile, then select your profile, and click on the Tunneling tab on the right.
You will see a dialog similar to the one below except that since I have already created the tunnel so it appears in the snapshot.
Click the Add button. You will then get a second dialog which should be filled in as follows:
The Display Name can be anything you want but “SQL” makes sense. The type should be TCP and both ports should be 1433. The Destination Host should be the internet address of the SQL 2005 Server given above. Check “Allow Local Connections Only” to prevent evil hackers from tunneling through your machine to the server.
Click OK each time to exit from the pair of dialog boxes. You then must select Save Settings from the SSH File menu or SSH will forget what you have just done! After this, close SSH and reopen it to make the settings effective.
Connecting to SQL 2005 Server Remotely
Make sure your SSH 1433 tunnel is running and any local copy of SQL Server is stopped. Launch SQL Server Management Studio and click Connect in the lower left of the menu bars. You will then see a connection dialog which should be filled in as follows:
So the Server Type is Database Engine,
the Server Name must be 127.0.0.1,
the Authentication is SQL Server Authentication,
the Login name is your usual username,
and the Password is your special SQL password.
Using 127.0.0.1 means that the connection
to SQL Server will pass through your SSH 1433 tunnel.
Warning: You may not use localhost in
place of 127.0.0.1. The connection will fail
with a puzzling error message. What is happening is that
SQL Server Management Studio does not recognize
localhost as a standard substitution for
127.0.0.1 and so attempts to interpret
localhost as a “named pipe”
which it is not.
Warning: If you have installed a local copy of SQL Server 2005 on your machine and this server is running (which is the default) then this local server will take over port 1433 and will prevent your access to the CCIS SQL Server. In the past, many students have been caught by this feature/bug. I recommend that you do not install SQL Server 2005 locally or that, if you do, you take extreme care to see that it is stopped when you want to do remote access.
Note: I clicked Remember password here since I was working from my own laptop.
What You See After Connection
Below is a snapshot of approximately what a successful connection will look like. Notice that you will see a list of all databases. You will however only have access to your own database.
Final Comment
This Web Development course uses databases as one possible source for backend data but it cannot possibly take on the task of teaching databases in general or SQL in particular. Some students in the course will know tons about databases while others will be complete newbies. I hope that those of you with expertise will be kind enough to help those who need basic information.