I’m in the process of moving to a two-machine development environment so my SQL Server processing will occur on one machine and my application will run on another machine.  I’m making this change since I’ve found that a single machine with 4GB can’t handle both processing 4 million records AND running an application that processes those 4 million records.

Anyway, this article isn’t about that.  It’s about how to configure SQL Server when you have to rename a machine.  I received my new machine which I made my development machine and moved my previous development machine over to be my database server.  The thought didn’t even cross my mind that I might need to do something special with SQL Server.  I already have a procedure that I know works every time for renaming a domain-joined machine:

1. Leave the domain (aka join a workgroup).
2. Rename the machine.
3. Re-join the domain.

For the most part the machine seemed to do really well after performing those steps.  However, once I finally got my new development machine configured and attempted to connect to SQL Server, the real trouble began.

It turns out there that SQL Server doesn’t like it when you change the name of the machine.  I’ve run into this with other server software (e.g. Server Clusters, aka Wolfpack) so it’s not too surprising.  I found some articles on the Internet that seemed to claim that you can recover a SQL Server installation in this state (see Renaming a SQL Server by Brian Knight, written on 4/11/2001) but those steps didn’t work for me.  The article basically says to run the installation procedure to repair it, reboot, then run a couple stored procedures to change permissions (or something like that).  As I read through the article again to write this one, it appears that those steps are only necessary for SQL Server 7.0; I’m running SQL Server 2005!  Oh well.  What I found is that the repair option found some errors with ASP.NET.  Further trials revealed that I was unable to fix them by uninstalling and reinstalling IIS as simply uninstalling IIS 7 failed.

Fine.  I’ll do it the hard way.  I uninstalled SQL Server and reinstalled.  After a few reboots, ignoring warnings about SQL Server not working on Vista, and installing SP2, I finally got it up and running.

But my development machine still couldn’t connect to my database server.  Ugh!

Here’s the trick.  You have to enable remote access to the server.  I found a great article called SQL Server 2005 Remote Connectivity Issue TroubleShoot by Ming Lu on the SQL Server Protocols team at Microsoft.  Here’s how you do it.

Server Side

1. Enable remote named pipes and/or TCP/IP.  This is done by running the SQL Server Surface Area Configuration tool which you can find under Microsoft SQL Server 2005 » Configuration Tools.  Click the Surface Area Configuration for Services and Connections link near the bottom of the window and you will be presented with another window where you can specify which services run, whether to start them automatically or not, and whether to support local connections only or local and remote connections.  While you’re here, set the SQL Server Browser service to start automatically.
   
2. Restart the SQL server instance.
3. If you enabled TCP/IP:
   1. Find the port SQL Server is listening on.  To do this, go to the Application event log and find a 26022 event that looks like this:

      Server is listening on [ 'any' <ipv4> 1433].

   2. Verify the server is listening on the correct port (1433 in my case).  Do this by running the following command in a command prompt window:

      netstat -ano | findstr 1433

4. Make sure the SQL Server Browser service (aka SQLBrowser) is running.
5. Firewall configuration:
   1. Enable File and Printer Sharing in the Firewall exception list.
   2. Either add the TCP port or sqlserver.exe (e.g. C:\Program Files\Microsoft SQL Server\MSSQL.1\Binn\sqlserver.exe) to the exception list in the firewall.
   3. Either add the TCP port 1434 or the SQL Browser service (e.g. C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe) to the firewall exception list.  Note that if you are running on a 64-bit platform this image is located below the Program Files (x86) folder.

Client Side

The article gives some steps to perform on the client side, although once I performed the server-side steps my problem was steps.  I’ll reproduce the steps here in case they are useful to someone.

1. Verify your connection to the server (call it MYSERVER).
   1. Verify the client gets the correct IP address:

      ping MYSERVER

   2. Verify the connection on the port the SQL Server instance is listening on:

      telnet MYSERVER 1433

   3. Verify the client can access file shares on the server:

      \\MYSERVER

2. Verify the SQL Native Client is configured to use the same protocols the server is listening on.
   1. Go to SQL Server Configuration Manager » SQL Native Client Configuration » Client Protocols.
   2. Make sure named pipes and/or TCP/IP are enabled to match the server.  It is recommended that you set TCP/IP above named pipes.
      
3. If you are using MDAC (Drive={SQL Server} or SQLOLEDB.x) in your client application, you will want to run the cliconfg.exe program to enable TCP/IP and named pipes and to set the order so that TCP/IP is above named pipes.
   
4. Use sqlcmd.exe to see whether connections work.

   sqlcmd -S MYSERVER -E

Hopefully my experiences will be helpful to others that run into this problem.  All this seems to be a lot more complicated than it should be - both renaming servers and enabling remote support.  Hopefully SQL Server 2008 will address these issues.