SSMS connection string

Question

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?

Answer 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.

Answer 2

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.