Active flag on the TCP/IP Properties page of SQL Server Configuration Manager

1

October 5, 2012 by Kenneth Fisher

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.

About these ads

One thought on “Active flag on the TCP/IP Properties page of SQL Server Configuration Manager

  1. Antonette says:

    Heya i am for the first time here. I found this board and I to find It really helpful & it helped me out a lot. I hope to present something again and aid others such as you aided me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 173 other followers

%d bloggers like this: