Sql-server – SQL Server Configuration Manager 2012 – unable to set IP All = No

clusteringconfigurationNetworksql serversql-server-2012

I have a 4-node SQL Server 2012 SP1 cluster, and under Network Configuration -> TCP/IP -> Protocol (tab), I am able to switch Listen All from Yes to No. However, when I hit OK (or Apply), I get the message:

Any changes made will be saved; however they will not take effect until the service is stopped and restarted.

OK, fine.

Now, if I re-open the TCP/IP properties, Listen All is back to Yes.

This happens even after I restart the SQL Server service, and it happens on all 4 instances.

I have checked the version of Configuration Manager – 2011.0110.2100.060. I've compared this with a (non clustered) SQL Server 2012 where I can flip the IP All field at whim, the versions are the same (as are the SQL Server versions, 11.0.3000.0 Build 7601).

I've also ensured that under IP Addresses, the one that I want to use has Enabled = Yes, all other IPs have Enabled = No.

Neither Books Online nor Google have any useful information for me.

Has anyone seen this before?

Best Answer

You cannot set IPAll to "no" on a failover cluster instance (as mentioned before), see:

How to configure SQL server to listen on different ports on different IP addresses?

More than likely, though, you are wondering how to secure your failover cluster instance of SQL Server to a certain IP address, or to verify that it isn't indeed listening on all IP addresses registered.

The failover cluster instance will be listening on the IP address(es) registered for the virtual network name in the cluster resource group with the Windows failover cluster.

But you want to verify which IP addresses SQL Server is listening on?

select
    ip_address,
    port,
    is_ipv4,
    type_desc,
    state_desc
from sys.dm_tcp_listener_states;

That will give you the real-time snapshot of what IP addresses SQL Server is listening on. That'll be your verification.

Related Question