Hi Everyone !
Today we’ll look at a the ListenAll settings of the SQL Server server-side protocol.
When active, this setting will have SQL Server bind all available IPs of the server, and use the same listening port for all of them.
Disabling ListenAll allows to manually select which IPs the SQL Server instance will bind, and also change the listening port for each those IPs.
For exemple, using the following settings (I used the loopback adapter as it was readily available)
Will lead to :
|
2015-10-09 10:59:25.57 spid18s Server is listening on [ 127.0.0.1 <ipv4> 1455]. 2015-10-09 10:59:25.57 spid18s Server is listening on [ x.x.x.110 <ipv4> 1433]. |
Notice the different ports for the loopback adapter and the main IP.
However, using this option has a side consequence. You will notice that after you disable ListenAll, the errorlog will show that SQL Server no longer autoregisters the TCP SPN for the instance.
With ListenAll active :
|
2015-09-25 16:42:03.55 Server The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/servername.domain.com ] for the SQL Server service. 2015-09-25 16:42:03.56 Server The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/ servername.domain.com:1433 ] for the SQL Server service. |
The first SPN is named pipe based, and the second one with the port is the TCP one.
(Of course, this is provided the SQL Service account has the required privileges to register SPN in the first place)
With Listen All disabled :
|
2015-10-09 10:59:25.63 Server The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/ servername.domain.com] for the SQL Server service. |
Only the NamedPipes SPN is auto registered.
Why ?
Well the short answer is that his is by design, and that the code behaves as it is expected to.
But if we look at it more in details, it makes sense.
When using the listenall option, there will be a single TCP port for all the IP that are bound by SQL. Registering the TCP SPN –which includes the port - is therefore straightforward. Just use the listenall port !
But if listenall is disabled, that means that each IP may have a different TCP port. It becomes clear why a single port-based TCP SPN registration is not possible anymore : which port value to select ?
Now there is the scenario where the listenall option is disabled to avoid that SQL Server binds all IP, yet all the individual IPs enabled for SQL still use the same manual value for the port. In that case the SPN auto registration would be possible, but this would need some sizeable added logic within SQL Server to detect this situation.
The decision was thus made to only do TCP SPN autoregistration when listenall is active. Any other situation will require the DBA to manage the TCP SPN registration manually.
Hope that helps !
Guillaume Fourrat
Escalation Engineer
Microsoft France