SQL Server Browser port

SQL Server Browser port
Image by David Schwarzenberg from Pixabay

When an instance of SQL Server starts, if the TCP/IP or VIA protocols are enabled for SQL Server, the server is assigned a TCP/IP port. If the named pipes protocol is enabled, SQL Server listens on a specific named pipe. This port or pipe will be used by that specific instance to exchange data with client applications. During installation, port 1433 and pipe \sql\query are assigned to the default instance, but those can be changed later by the server administrator by using SQL Server Configuration Manager. Because only one instance of SQL Server can use a port or pipe, different port numbers and pipe names are assigned for named instances, including SQL Server Express. By default, named instances are configured to use dynamic ports, so an available port is assigned when SQL Server starts. A specific port can be assigned to a SQL Server instance. When connecting, clients can specify the desired port. However, if the port is dynamically assigned, the port number can change any time SQL Server is restarted, so the correct port number is unknown to the client.

On startup, SQL Server Browser starts and claims UDP port 1434. SQL Server Browser reads the registry, identifies all SQL Server instances on the computer, and notes the ports and named pipes that they use. When a server has two or more network cards, SQL Server Browser will return all ports enabled for SQL Server. SQL Server 2005 and SQL Server Browser support ipv6 and ipv4.

When SQL Server 2000 and SQL Server 2005 clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port or named pipe of the desired instance.

If the SQL Server Browser service is not running, you can still connect to SQL Server if you provide the correct port number or named pipe. For example, you can connect to the default instance of SQL Server with TCP/IP if it is running on port 1433.

The following connections will not work:

  • Any component that tries to connect to a named instance without fully specifying all the parameters, such as the TCP/IP port or a named pipe.
  • Any component that generates or passes server or instance information that could later be used by other components to reconnect.
  • Connecting to a named instance without providing the port number or pipe. This includes Data Mirroring to a named instance and clustering a named instance.
  • Dedicated Administrator Connections to a named instance, or the default instance if not using TCP/IP port 1434.
  • The OLAP redirector service.
  • Enumerating servers in SQL Server Management Studio, Enterprise Manager, or Query Analyzer.

If you are using SQL Server in a client-server scenario, for example, when your application is accessing SQL Server across a network, if you stop or disable the SQL Server Browser service, you must assign a specific port number to each instance and write your client application code to always use that port number. This approach has the following problems:

  • You must update and maintain client application code to make sure it is connecting to the proper port.
  • The port you choose for each instance might be used by another service or application on the server, causing the SQL Server instance to be unavailable.

In SQL Server 2000, the identification of the server connection endpoints was performed by the SQL Server service. SQL Server 2005 replaces that function with the SQL Server Browser service. If you install SQL Server on a computer that is also running SQL Server 2000 or MSDE, they must be upgraded to SP3 or later. Versions earlier than SP3 do not properly share port 1434 and might not make your SQL Server instances available to requesting client applications. Although you can change the startup order so that the SQL Server Browser service starts before SQL Server 2000 or MSDE, the recommended resolution is to update all older versions of SQL Server to the latest service pack.

Source: technet.microsoft.com