SQL Server Named Instance is not listening on Port 1434

Question

We just installed SQL Server 2016 on a Windows 2016 server. I am having issues connecting to the Named Instance of that server. I have noticed that the server isn't listening on port 1434 either. Normally we would see it in log as:

Server is listening on [XX.XX.XX.XX <ipv4> 1434>

But the only thing I see in the logs are:

Server is listening on [ 127.0.0.1 <ipv4> 63229].
Server is listening on [ ::1 <ipv6> 63229].

I can seem to connect to this named instance other than on the local server. We have apps running that will hit this Named Instance but it seems like we are having issues connecting.

I also did a netstat -ao | findstr /I "processID" and I couldn't see the 1434 ports. I had configured my TCP/IP to use the TCP Port 1434 for my IP but it's still not working.

Any ideas why it's not listening on 1434?

I checked in Configuration Manager and everything looked fine. TCP/IP was enabled and SQL service restarted. It doesn't seem to be listening on that port when it starts up.

The SQL Browser service is turned on. The TCP Port 1434 is assigned to the IP but the TCP Dynamic Ports and TCP Port is blank in the IPALL.

Maybe the question is how is the named instance supposed to be set up? This was to replace an old server that was running SQL 2012 and that server had it set this way and it's been running fine. So I copied the same setting and we can't seem to connect to the named instance.

Answer 1

Some additional investigation is needed here. First, it is possible that you are filtering out too much of the netstat output in your findstr filter. Instead of "processID", check for the port number using ":1434". Also, using the -q switch instead of -a might be better as it will show bound, nonlistening ports. For example:

netstat -qof | findstr ":1434"

OR, if you start the command prompt as an administrator, you can add the -b switch to print out the name of program associated with each process (saves the step of needing to map the Process ID to the program). When doing this, the output of each entry is on 2 lines, so you won't see the additional info if you use findstr as those lines will never have the matching string. Instead, just pipe to more as follows:

netstat -qofb | more

Now, using netstat is handy to research to see if a port is in use, but for this issue it is more direct to see what SQL Server thinks is going on, and fortunately there are some views that have the info so you don't need to scan / parse the error log. You should check the following:

• sys.tcp_endpoints
• sys.dm_tcp_listener_states

So just execute the following:

SELECT * FROM sys.tcp_endpoints;
SELECT * FROM sys.dm_tcp_listener_states;

If the first view, sys.tcp_endpoints, shows that "Dedicated Admin Connection" has a state_desc other than "STARTED", then there is an issue.

Now, in the second view, sys.dm_tcp_listener_states, if you do have "::1" (IP v 6) and/or "127.0.0.1" (IP v 4) for ip_address then it is only listening locally (i.e. remote administrative access is OFF). If you instead see "::" (IP v 6) and/or "0.0.0.0" (IP v 4), then remote admin access is ON.

By default remote administrative access is OFF. You can enable it by executing the following (this is not an "advanced" option):

EXEC sp_configure N'remote admin connections', 1; RECONFIGURE;

Answer 2

In General, allow firewall access to the following ports to connect to SQL Server remotely:

For Default Instances

• TCP port 1433
• TCP port 1434 (For DAC Connection)

For Named Instances

• TCP Port (Whichever port the SQL Server is listening on – Check the Error Log for Port Details)
• UDP Port 1434 (For Browser Services)
• UDP (and TCP) Port on which Dedicated Admin connection is listening (UDP is what worked for me). Check the SQL Server log to note as to on which port was Dedicated admin connection was established:

“Message Dedicated admin connection support was established for listening locally on port XXXXX”

For Named instances, make sure that the Browser Service is running if the external applications are connecting using the Instance name.

Also note that for Named instances, it is better to configure SQL Server to listen on a static port otherwise firewall management will become difficult as SQL Server will listen on a different port every time it’s restarted.