SQL SERVER – Database Engine TCP Port and Browser UDP Port

I inspect TCP listeners to find the actual Network port in use. A customer wanted a query rather than an assumption.

SELECT ip_address, port, type_desc, state_desc
FROM sys.dm_tcp_listener_states WHERE type_desc = N'TSQL';
SELECT net_transport, local_net_address, local_tcp_port
FROM sys.dm_exec_connections WHERE session_id = @@SPID;

My original text incorrectly called 1434 the standard static port. The usual default engine TCP port is 1433, but configured and dynamic ports differ. Browser commonly uses UDP 1434. A dedicated administrator connection can separately use TCP 1434.

The listener DMV reports local listening endpoints. The connection DMV reports the current connection. Shared-memory connections can return NULL for address or port. Neither proves remote firewall, routing or DNS access.

Test the intended connection with its verified endpoint and protocol. An unrelated endpoint number does not justify opening a firewall. The linked connection-error and multiple-port articles provide more context.

Reference: Dedicated administrator TCP endpoint.

Related reading

• Comprehensive Database Performance Health Check
• What are Ports Needed to Configure Log Shipping? – Interview Question of the Week #169
• SQL SERVER – How to Listen on Multiple TCP Ports in SQL Server?
• SQL SERVER – Unable to Start SQL Service – Server TCP provider failed to listen on [‘any’ 1433]. Tcp port is already in use.
• SQL SERVER – The server network address “TCP://SQLServer:5023” can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)
• Twitter

A listener inventory is not a connectivity test, it is endpoint information to verify against the intended route.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.