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. You do not need this particular program to access SQL. The same technical information may be used to set up a SQL connection from within Visual Studio 2010.

The focus here is on how you interact directly with the SQL server to create tables and then examine or modify the data in tables. Interactions via code on your web site is the topic of the sections on Connection Strings and on LINQ.

The SQL Server in CCIS is located at the following internet address:

MSSQL2005.development.ccs.neu.edu

How you access the server interactively depends on whether you are inside the firewall, that is, in the lab, or if you are using an external remote 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

Back to the top

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:

To summarize:

• Server Type: Database Engine
• Server Name: MSSQL2005.development.ccs.neu.edu
• Authentication: SQL Server Authentication
• Login Name: Your CCIS username
• Password: Your special SQL password

I strongly urge you not to click the Remember password check box on a lab machine!

Back to the top

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:

login.ccs.neu.edu

Do not use the SSH profile that gives you secure FTP access to the development server. This method will not work.

The tunnel will in fact take two hops:

Your remote system
     ---> login.ccs.neu.edu
     ---> MSSQL2005.development.ccs.neu.edu

In SSH, pick the profile you use for standard logins to login.ccs.neu.edu.

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:

To summarize:

• Display Name: May be anything but “SQL” makes sense
• Type: TCP
• Ports: Both ports must be 1433
• Destination host: MSSQL2005.development.ccs.neu.edu

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.

Back to the top

Bitvise Login

Bitvise Options

Bitvise C2S Forwarding

Beware:

• Make sure your SSH 1433 tunnel is running.
• Make sure any SQL-like software that you may have installed on your machine is stopped.

Over the years, there have been countless students who could not connect to the SQL server because they failed to heed the instructions above. Do not go there. ☺

If you use SQL Server Management Studio then launch the application and click Connect in the lower left of the menu bars. You will see a connection dialog which should be filled in as follows:

To summarize:

• Server Type: Database Engine
• Server Name: 127.0.0.1
• Authentication: SQL Server Authentication
• Login Name: Your CCIS username
• Password: Your special SQL password

Using 127.0.0.1 means that the connection will pass through the SSH 1433 tunnel from your machine to the SQL Server.

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: You may not use MSSQL2005.development.ccs.neu.edu in place of 127.0.0.1. This will cause the software to attempt to connect via an insecure network connection rather than through the 1433 tunnel and this attempt will be rejected.

Warning (Again): If you have installed a local copy of SQL Server 2005 or similar SQL-like software on your machine and this server is running (which is the default) then the 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.

Note: If you connect via Visual Studio 2010, the settings are identical.

Back to the top

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.

Back to the top

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.

Back to the top