SQL Server 2005 Named Instance via TCP/IP

Here's one for the work at home database developers out there. I spent two hours figuring this out and next week I'll forget how I did it so I'm putting it here.

I wanted to connect to SQL Server 2005 that is running in my office from my home computer. No sweat I thought since I have a VPN connection it will be just like being in the office. Not exactly. I'm not exactly sure why it didn't work but I imagine it has to do with the fact that the server that hosts SQL Server 2005 also hosts SQL Server 2000. The 2005 server was installed last and was therefore installed as a "named instance".

I had to do two things, both from SQL Server Configuration Manager, which I found under the Start menu Programs->Microsoft SQL Server2005->Configuration Tools.
Once launched, I opened SQL Server 2005 Network Configuration and selected Protocols for SQQL2005 (see the screen shot -click the image to get a legible version). Then in the right pane I right-clicked TCP/IP and filled in the TCP Port for IP1 and IPAll to 1435. I was afraid that the normal port of 1433 was in use by SQL Server 2000. Then it was a matter of figuring out how to specify a server by tcp. Hitting F1 for help in this same console app. led me to a section on aliases (which I didn't need) that had the following information:

• At the time of connection, the SQL Native Client component reads the server, protocol, and port values from the registry for the specified alias name, and creates a connection string in the format tcp:[\], or tcp:[\],.

This means I could put in a connection string tcp:192.168.1.171\theNamedInstanceOfMyServer, 1435 and it showed up. Wasn't that easy?

If it doesn't work for you, you must make sure that you have remote connections enabled. You can do this with another tool with the strange moniker of SQL Server Surface Area Configuration (located in the same shortcut area as the configuration manager). Once you launch it, you can click on the link for SurfaceArea Configuration for Services and Connections (it's visible in the background of the second screen shot). Then in the resulting dialog, open up the server->Database Engine->Remote Connections and make sure you have the Local and remote connections clicked. You can choose whichever options makes the most sense for you. Read up on the security concerns on allowing remote connections.