0

In SSMS we are giving hostname\instance name to connect to SQL Instance, can someone please explain me how it is getting connected, is it using any files or protocols or what exactly is happening in back ground so that it gets connected?

1
  • What exactly are you trying to accomplish? Are you looking for guidance on how to connect an application to a SQL Server instance much like SSMS does? Mar 18, 2014 at 14:14

2 Answers 2

1

Connection to named instances is handled by SQL Browser Service:

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.

0

Using TCP/IP Sockets, when the SQL client is given a server name SERVER\INSTANCE, for example PRODDB\Payroll, it checks two sockets to work out what port number INSTANCE is listening on.

  1. It checks port tcp1433. The default instance is listening on this port by default.
  2. It checks port udp1434. The SQL Browser service is listening on this port.

Both the default instance and SQL Browser maintain a list of instances running and their current port numbers.

If there is nothing listening on those two ports (maybe there is no default instance, maybe the default instance is using a different port, maybe SQL Browser is stopped) then the only way to connect is to manually specify a port number in the connection string using a comma. For example, PRODDB\Payroll,14550.

SQL Browser sends broadcast network traffic so a lot of administrators prefer to not run it.

TechNet: Default Client Connection Behavior.

As an aside, port tcp1434 is used by the default instance for the Dedicated Administrator Connection.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.