Sql server browser что это

What is Microsoft SQL Server Browser Service?

The Browser Service was introduced in SQL Server 2005 as a simple way to provide SQL Server connection information to client applications on workstations.

Microsoft SQL Server contains multiple components that work together to provide data access to a variety of client applications, such as Acctivate. One of the key components for Acctivate is the SQL Server Browser Service.

sqlserverbrowserservice

The Browser Service was introduced in SQL Server 2005 as a simple way to provide SQL Server connection information to client applications on workstations. The Browser Service responds to client requests by providing a list of SQL Server Instances (e.g., ACCTIVATE) and the corresponding TCP/IP port number. The client application then re-connects to the SQL Server on the given port number.

The Browser Service listens on UDP Port 1434 on each computer running Microsoft SQL Server. You must allow access from Acctivate workstations to the server on UDP 1434. Read our firewall exceptions article for more information.

Microsoft has an article on starting and stopping the SQL Server Browser Service. By default, the Browser Service may be disabled.

For more knowledge on the SQL Browser service, please check out this article on from the Microsoft Docs page.

SQL Server Browser Service

The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser contributes to the following actions:

Browsing a list of available servers
Connecting to the correct server instance
Connecting to dedicated administrator connection (DAC) endpoints

For each instance of the Database Engine and SSAS, the SQL Server Browser service (sqlbrowser) provides the instance name and the version number. SQL Server Browser is installed with SQL Server, and provides this service for earlier versions of SQL Server that are running on that computer, starting with SQL Server 7.0.

SQL Server Browser can be configured during setup or by using SQL Server Configuration Manager. By default, the SQL Server Browser service starts automatically:

When upgrading an installation.
When installing side-by-side with an instance of SQL Server 2000.
When installing on a cluster.
When installing a named instance of the SQL Server Database Engine including all instances of SQL Server Express.
When installing a named instance of Analysis Services.

Background

Prior to SQL Server 2000, only one instance of SQL Server could be installed on a computer. SQL Server listened for incoming requests on port 1433, assigned to SQL Server by the official Internet Assigned Numbers Authority (IANA). Only one instance of SQL Server can use a port, so when SQL Server 2000 introduced support for multiple instances of SQL Server, SQL Server Resolution Protocol (SSRP) was developed to listen on UDP port 1434. This listener service responded to client requests with the names of the installed instances, and the ports or named pipes used by the instance. To resolve limitations of the SSRP system, SQL Server 2005 introduced the SQL Server Browser service as a replacement for SSRP.

How SQL Server Browser Works

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,» is used by that specific instance to exchange data with client applications. During installation, TCP port 1433 and pipe \sql\query are assigned to the default instance, but those can be changed later by the server administrator 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, when enabled, both named instances and SQL Server Express are configured to use dynamic ports, that is, an available port is assigned when SQL Server starts. If you want, a specific port can be assigned to an instance of SQL Server. When connecting, clients can specify a specific port; but if the port is dynamically assigned, the port number can change anytime SQL Server is restarted, so the correct port number is unknown to the client.

Upon startup, SQL Server Browser starts and claims UDP port 1434. SQL Server Browser reads the registry, identifies all instances of SQL Server 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 returns the first enabled port it encounters for SQL Server. SQL Server Browser support ipv6 and ipv4.

When SQL Server 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.

For information about starting and stopping the SQL Server Browser service, see How to: Start and Stop the SQL Server Browser Service.

Using SQL Server Browser

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

However, if the SQL Server Browser service is not running, the following connections do 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 named pipe).
Any component that generates or passes server\instance information that could later be used by other components to reconnect.
Connecting to a named instance without providing the port number or pipe.
DAC to a named instance or the default instance if not using TCP/IP port 1433.
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 ensure it is connecting to the proper port.
The port you choose for each instance may be used by another service or application on the server, causing the instance of SQL Server to be unavailable.

Side-by-Side Installation with SQL Server 2000

In SQL Server 2000, the identification of the server connection endpoints is 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, you must make sure that SQL Server 2000 or MSDE is upgraded to Service Pack 3a (SP3a) or later. Versions earlier than SP3a do not properly share port 1434 and may not make your instances of SQL Server available to requesting client applications. Although you can change the services startup order so that the SQL Server Browser service starts before SQL Server 2000 or MSDE, we recommend that you update all earlier versions of SQL Server to the latest service pack.

When an instance of SQL Server 2000 that is not updated to at least service pack 3a is installed on the computer, if the SQL Server Browser is not running, the SQL Server 2000 listener service starts. If SQL Server Browser starts after the listener service, it waits 5 seconds for SQL Server 2000 to give up port 1434. If that does not occur, SQL Server Browser fails to start. To resolve this problem with versions of SQL Server 2000 earlier than Service Pack 3a, stop SQL Server 2000, start SQL Server Browser, then restart SQL Server 2000. The SQL Server 2000 listener service continues to attempt to start on port 1434; therefore, the instance of SQL Server 2000 should be upgraded to Service Pack 3a as soon as possible.

SQL Server 7.0 has no similar capabilities and has no conflicts with SQL Server Browser.

Clustering

SQL Server Browser is not a clustered resource and does not support failover from one cluster node to the other. Therefore, in the case of a cluster, SQL Server Browser should be installed and turned on for each node of the cluster. On clusters, SQL Server Browser listens on IP_ANY.

Installing, Uninstalling, and Running from the Command Line

By default, the SQL Server Browser program is installed at C:\Program Files\Microsoft SQL Server\100\Shared\sqlbrowser.exe. If SQL Server 2000 is installed on the computer but not upgraded to Service Pack 3, and if the SQL Server 2000 instance is not being upgraded, SQL Server 2000 should be stopped during SQL Server installation. If SQL Server 2000 is running, SQL Server Browser cannot start and claim port 1434, and the installation may fail due to lack of connectivity.

The SQL Server Browser service is uninstalled when the last instance of SQL Server is removed. Instances of SQL Server 2000 resume their original behavior.

SQL Server Browser can be started from the command prompt for troubleshooting, by using the -c switch:

Security

Account Privileges

SQL Server Browser listens on a UDP port and accepts unauthenticated requests by using SQL Server Resolution Protocol (SSRP). SQL Server Browser should be run in the security context of a low privileged user to minimize exposure to a malicious attack. The logon account can be changed by using the SQL Server Configuration Manager. The minimum user rights for SQL Server Browser are the following:

Deny access to this computer from the network
Deny logon locally
Deny Log on as a batch job
Deny Log On Through Terminal Services
Log on as a service
Read and write the SQL Server registry keys related to network communication (ports and pipes)

Default Account

Setup configures SQL Server Browser to use the account selected for services during setup. Other possible accounts include the following:

Any domain\local account
The local service account (not available on W2K platforms)
The local system account (not recommended as has unnecessary privileges)

Hiding SQL Server

Hidden instances are instances of SQL Server that support only shared memory connections. For SQL Server, set the HideInstance flag to indicate that SQL Server Browser should not respond with information about this server instance. SQL Server Browser also supports the SQL Server 2000 method of hiding instances enabled by setting the HideServer option through the server network utility. For information about configuring the HideServer option, see «How to: Hide an Instance of SQL Server Database Engine» in SQL Server Books Online.

The ultimate guide to SQL Server Browser for Dev

Hello Dev, welcome to the ultimate guide on SQL Server Browser. This article is aimed to help you understand what SQL Server Browser is, how it works, and most importantly, how you can utilize it to improve your SQL Server performance. Let’s get started!

What is SQL Server Browser?

SQL Server Browser is a Windows service that enables clients to connect to named instances of SQL Server. It listens on a specific network port (UDP port 1434) and provides information about all running instances of SQL Server on the computer. When an instance of SQL Server is started, SQL Server Browser provides the TCP/IP port number that corresponds to that instance.

SQL Server Browser is typically deployed in scenarios where SQL Server instances are running on non-standard port numbers or are using dynamic ports. By default, SQL Server instances use port 1433 for TCP/IP connections, but this can be changed during installation or configuration.

SQL Server Browser is installed and enabled by default when you install SQL Server. However, in some cases, it may be disabled for security reasons or to reduce network traffic. In those cases, you can enable it through the SQL Server Configuration Manager.

How does SQL Server Browser work?

When a client attempts to connect to a named instance of SQL Server, it sends a request to the SQL Server Browser service on UDP port 1434. The request includes the instance name and the protocol used for the connection (TCP/IP, named pipes, or shared memory).

The SQL Server Browser service checks its running instance list to find the requested instance name and its corresponding port number. It then returns that information to the client, which can then connect to the instance using the provided port number.

When a client connects to a named instance of SQL Server, it uses the TCP/IP protocol by default. However, other protocols can be used as well, depending on the client configuration and the network environment.

How can SQL Server Browser improve SQL Server performance?

SQL Server Browser can improve SQL Server performance by enabling clients to connect to named instances of SQL Server more easily. By providing information about running instances and their port numbers, SQL Server Browser eliminates the need for clients to remember or look up the correct port numbers for each instance.

Additionally, SQL Server Browser can simplify network configuration by allowing instances to run on non-standard or dynamic port numbers. This can reduce conflicts between instances and make it easier to deploy and manage SQL Server in complex environments.

FAQ about SQL Server Browser

Conclusion

SQL Server Browser is an essential Windows service for enabling clients to connect to named instances of SQL Server. By providing information about running instances and their port numbers, it simplifies network configuration and eliminates the need for clients to remember or look up the correct port numbers for each instance.

If you’re using named instances or non-standard port numbers with SQL Server, it’s highly recommended to enable SQL Server Browser for improved performance and ease of use. And if you have any questions or concerns about SQL Server Browser, remember to consult the documentation or seek help from the SQL Server community.

The SQL Server Browser Service and UDP Port 1434

The SQL Server Browser Service listens on incoming connections for SQL Server services on SQL Server machines and provides information about SQL Server instances installed on the machine. This article discusses about the SQL Server Browser Service and UDP Port 1434, as well as what its use means throughout a simple example.

What Does the SQL Server Browser Service Do?

As per Microsoft’s documentation, the SQL Server Browser service contributes to the following actions:

Browsing a list of available servers
Connecting to the correct server instance
Connecting to dedicated administrator connection (DAC) endpoints
When upgrading an installation.
When installing on a cluster.
When installing a named instance of the Database Engine including all instances of SQL Server Express.
When installing a named instance of Analysis Services.

Due to all the above offerings, as security best practice, the SQL Server Browser service is disabled by default, as reduces the attack surface area by not having a specific UDP port open. This UDP port is port 1434.

There are however cases, where the SQL Server Browser service must be up and running in order to properly use SQL Server services on a machine. Below we will discuss such an example.

Strengthen your SQL Server Administration Skills – Enroll to our Online Course!

Check our online course on Udemy titled “ Essential SQL Server Administration Tips ”
(special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

( Lifetime Access / Live Demos / Downloadable Resources and more!)

Enroll from $12.99

Example Related to the Use (or not) of the SQL Server Browser Service

Consider the following example: You are setting up a new SQL Server named instance (i.e. instance named “SQLInstance1”) on a new server on a corporate environment. That means that there is a firewall and, oh well, firewall rules everywhere!

So for added security, you change the default port and you set a new one for your new SQL Server instance. You then send a request to your network administrator and include in your request the following:

The source IP (i.e. the client(s) that will be connecting to the SQL Server instance),
The destination IP (that is, the IP of the SQL Server instance), and
The destination port (in this case you provide the port on which you set the new SQL Server instance to be listening to).

After the relevant firewall rule(s) is implemented, you then try to access the SQL Server instance (i.e. try to connect to machineName\SQLInstance1) and guess what; you get the sweet error message: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1) (learn more about this error message).

You then think to yourself: “But I have requested the relevant firewall rule(s) and I can ping the server. What’s wrong?“.

Discussion

So, what do you do? Well, the way I see it, you have two options:

Option 1: You explicitly specify the named SQL instance’s port in every client connection and in every connection string (which, in some cases can be acceptable and in some other can be annoying), or
Option 2: You make sure that the SQL Server Browser service is running on the SQL Server machine, and that you request another firewall rule to be implemented, that will be allowing traffic from the client(s) (source) to the SQL Server machine on UDP port 1434, that is the port the SQL Server Browser service listens to.

So, if you go for Option 2, for connecting to SQL Server, you will just need to specify the instance name without any port number, that is have an “easier” connection string, and the SQL Server Browser service will take care of the rest (it will actually let the client know in the background, the actual port number of the SQL Server instance, so that the client connection to be able to “auto-specify” in the background the correct port number.

As a last note, regarding resource utilization, the SQL Server Browser service is quite lightweight, that is it does not make heavy use of any resources on the SQL Server machine.

Featured Online Courses:

SQL Server Fundamentals – SQL Database for Beginners
Essential SQL Server Administration Tips
Essential SQL Server Development Tips for SQL Developers
SQL Server 2019: What’s New – New and Enhanced Features
How to Import and Export Data in SQL Server Databases
Introduction to Azure SQL Database for Beginners
Introduction to Azure Database for MySQL
Working with Python on Windows and SQL Server Databases
Boost SQL Server Database Performance with In-Memory OLTP
Introduction to Computer Programming for Beginners
.NET Programming for Beginners – Windows Forms with C#
Introduction to SQL Server Machine Learning Services
Entity Framework: Getting Started – Complete Beginners Guide
Learn How to Install and Start Using SQL Server in 30 Mins
A Guide on How to Start and Monetize a Successful Blog