I’ve been unable to connect to a server via one of its aliases for about a week.  When I looked at the TCP/IP Properties for the instance I saw the following:

Note that the Active flag for IP1 is “No”. BOL defines the Active flag as “Indicates that the IP address is active on the computer.” However I know that the IP is active since it is working for another instance on the same server.

I tried changing the Active flag to “Yes”, then applying the changes. When I brought back up the page it still showed “No”. Next I restarted the instance, no change. Then I restarted the server, still no luck.

Eventually I checked the registry. They key for IP1 is: [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008\MSSQLServer\SuperSocketNetLib\Tcp\IP1]

(The part MSSQL10.SQL2008 represents the instance. Change it to match the instance you are checking.)

Under this key is an entry “Active” that is 0×00000001 (1) for “Yes” and 0×00000000 (0) for “No”. I first changed the flag from 0 to 1. When I went back to SSCM the TCP/IP Properties page now showed:

Once I restarted the instance I could connect normally!

I’m a little surprised that SQL doesn’t update this flag without a registry change and I wouldn’t recommend it normally, but in this case it seems necessary.