Printed 2017/07/27 03:41PM

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

By Kenneth Fisher, 2012/10/05

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.

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.