First, take a look to the following (wrong configuration) configuration on a SQL Server to set a static TCP/IP port:

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 Server, 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

We'll see that the field is_dynamic_por will have value 1 (that means that SQL Server is using dynamic ports and not the static one we've specified). 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.

When SQL Server is started it logs an event message as 'Server is listening on [ 'any' ' in windows event logs. Here will be actual port number on which SQL Server is listening. To view this using Event Viewer:

Step 1: Click Start > Administrative Tools > Event Viewer.

Note: If Administrative Tools are not available on Start menu, go to Start > Control Panel > System and Maintenance > Administrative Tools > View event logs.

Step 2: Navigate to Event Viewer > Windows Logs > Application.

Step 3: Since huge amount of event are logged, you need to use filtering to locate the required logs. Right click on Application and select Filter Current Log....

Step 4: You can filter the events by Event ID and Event source. The event we are interested in has Event ID of 26022, and it's source is SQL Server Instance. You need to filter by both Event ID and SQL Server Instance if you have multiple instances installed, for a single instance you can filter by Event ID only. Click on OK to apply the filter.

Step 5: Once the filter is applied, Locate message 'Server is listening on [ 'any' ...'. As we can see from below screenshot that SQL Server Instance is running on TCP Port 61499.

When SQL Server is started it also logs an message to SQL Server Error Logs. You can search for port number in SQL Server Error Logs by opening SQL Server Error Log in notepad or via T-SQL using extended stored procedure xp_ReadErrorLog as below:

EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC'


GO

As we can see from the output that SQL Server Instance is listening on 61499.

Note: This method does not work if SQL Server Error Logs have been cycled.

Using the command netstat –ano, we can see what it is listening on (SQL Server process was using PID 1240, in this example):

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 

The client might attempt to connect to the previous dynamic port (which is still valid based on our observation above).

The Fix

Finally, the fix to this situation is: to delete the dynamic port assignment! Here we go:

1. In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration, expand Protocols for , and then double-click TCP/IP.
2. In the TCP/IP Properties dialog box, on the IP Addresses tab, several IP addresses appear in the format IP1, IP2, up to IPAll. One of these is for the IP address of the loopback adapter, 127.0.0.1. Additional IP addresses appear for each IP Address on the computer.
3. Right-click each address, and then click Properties to identify the IP address that you want to configure.
4. If the TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports, delete the 0.
5. In the IPn Properties area box, in the TCP Port box, type the port number you want this IP address to listen on, and then click OK.
6. In the console pane, click SQL Server Services.
7. In the details pane, right-click SQL Server () and then click Restart, to stop and restart SQL Server.