Set Static Port SQL Server 2012

How to assign a static port to a SQL Server named instance

While Books Online clearly mentions the steps to Configure a Server to Listen on a Specific TCP Port we still see people missing out on one small but important detail in these steps: they forget to delete the entry (0 or some random port) for dynamic port. This firstly results in confusion and occasionally can result in connectivity problems as well. Let me explain how using an example from one of our lab setups.

As a first step, let’s see what the ‘administrator’ (in this case, yours truly Smile) had done:

As you can see, they have added the static port for ‘IPAll’ with a value of 1450. That part is fine. The problem though is they forgot to remove the entries for the dynamic ports (0 or some random port). That means that when they restarted SQL, the dynamic port setting is still valid. In fact if we query sys.tcp_endpoints, you will still see the engine thinks it is listening on dynamic port:

SELECT        name, protocol_desc, type_desc, state_desc, is_admin_endpoint, port,        is_dynamic_port, ip_address 
FROM            sys.tcp_endpoints

The important observation is that the engine reports that it is still using a dynamic port. It does not report the static port number 1450 which we selected in Configuration Manager. Let’s double-check in the errorlog to see if indeed the static port is being picked up at all. And lo and behold:

Server is listening on [ ‘any’ 1450]. 
Server is listening on [ ‘any’ 1450]. 
Server is listening on [ ‘any’ 49626]. 
Server is listening on [ ‘any’ 49626].

In our case, sqlservr.exe has a PID of 1240. Using the command netstat –ano, we can see what it is listening on.

  Proto  Local Address       Foreign Address                State                  PID 
  TCP      0.0.0.0:1450                0.0.0.0:0                           LISTENING             1240 
  TCP      0.0.0.0:49626             0.0.0.0:0                           LISTENING             1240 
  TCP      127.0.0.1:49627          0.0.0.0:0                           LISTENING             1240 
  TCP      192.168.1.101:1450    192.168.1.200:49386      ESTABLISHED       1240 
  TCP      192.168.1.101:1450    192.168.1.200:49396      ESTABLISHED       1240 
  TCP      [::]:1450                      [::]:0                                   LISTENING             1240 
  TCP      [::]:49626                   [::]:0                                   LISTENING             1240 
  TCP      [::1]:49627                  [::]:0                                   LISTENING            1240

So it is not only listening on the static port, but also on the dynamic port 49626. The DAC is listening on TCP port 49627. The values with a local address of [::] are the IPv6 ‘All’ address.

So depending on what got cached earlier in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI11.0\LastConnect on the client (you can find some detail about the LastConnect registry key and the caching mechanism in this KB article), the client might attempt to connect to the previous dynamic port (which is still valid based on our observation above.)

FYI, if we run NetMon, we can see that the SSRP response from the SQL Browser correctly gives back 1450 as the port for this named instance:

For clarity I’ve reproduced the response from SQL Browser (using the SSRP protocol) back to my client (SQLCMD.exe):

.^.ServerName;SOMESERVER;InstanceName;SOMEINSTANCE;IsClustered;No;Version;11.0.2100.60;tcp;1450;;

From the above it is clear that SQL Browser is correctly sending the static port assignment. But if you are like me, I feel uneasy till I fix the root cause, which is to delete the dynamic port assignment!

To summarize here is what we saw in this walkthrough:

The official steps (captured in Books Online) to assign a static port for a named instance involve also deleting the value (0 or some random port) for the dynamic port.

Failure to delete the dynamic port value in SQL Configuration Manager will cause SQL to listen on both the static as well as the dynamic ports.

This means that clients will succeed to connect to the erstwhile dynamic port if they had that cached in the LastConnect client side registry key.

For clients which do not have cached connection details, SQL Browser seems to pickup the static port and sends that back to the client.

So follow the steps in the BOL article to the T and delete the dynamic port value right after you type in the static port value, and in any case before you restart the instance.

FYI the steps to fix a static port for the Dedicated Admin Connection (DAC) are in the KB article How to configure SQL Server to listen on a specific port under the section ‘Configuring an instance of SQL Server to use a static port’.