Regulate Network Connectivity

SQL Server accepts client connections over specific network protocols and (for lack of a better term) associated channels. The combination of network protocol, a channel, and the type of communication taking place over it is managed from within SQL Server as an endpoint.

SQL Server comes pre-configured with 5 endpoints intended to support traditional client-server communications.  These are identified in this table:

Endpoint	Network Protocol	Channel
Dedicated Admin Connection	TCP	This endpoint is associated with a TCP port.   With default instances of SQL Server, this is typically TCP port 1434. With named instances of SQL Server, this is typically a TCP port dynamically assigned during the start-up of the SQL Server service. With both named and default instances, the TCP port can be set to a differing (and fixed) value through the SQL Server Configuration Manager.
TSQL Local Machine	Shared Memory	This endpoint is not associated with a channel as the protocol directly transfers data between client and server through memory.
TSQL Named Pipes	Named Pipes	This endpoint is associated with a named pipe. With default instances of SQL Server, the server identifies this named pipe as \\.\pipe\sql\query and clients identify this named pipe as \\ <computername> \pipe\sql\query. With named instances of SQL Server, the server identifies this named pipe as \\.\pipe\MSSQL$ <instance name> \sql\query and clients identify this named pipe as \\ <computername> \pipe\MSSQL$ <instancename> \sql\query. The name of the pipe can be changed using the SQL Server Configuration Manager.
TSQL Default TCP	TCP	This endpoint is associated with a TCP port. With default instances of SQL Server, this is typically TCP port 1433. With named instances of SQL Server, this is typically a TCP port dynamically   assigned during the start-up of the SQL Server service. With both named and default instances, the TCP port can be set to a differing (and fixed) value through the SQL Server Configuration Manager.
TSQL Default VIA	VIA	This endpoint is associated with VIA port 0:1433 on both default and named instances of SQL Server.

As the Shared Memory protocol can only be used locally on the server, the Dedicated Admin Connection (DAC) is configured (by default) for local connections only, and VIA is a deprecated protocol, the endpoints associated with these will not be discussed further in this post. That leaves the TSQL Named Pipes and TSQL Default TCP endpoints.

NOTE In the context of this discussion, TCP and Named Pipes refer to protocols employed by SQL Server.  The use of one protocol or the other affects how SQL Server (and the client) manage and interact buffers through which data is exchanged. These protocols are employed a higher level in the networking stack than protocol employed by the operating system, typically TCP/IP. To illustrate this, consider that communications via the (SQL Server) Named Pipes protocol are handled over TCP port 445.

Returning to the concept of a client connecting to a SQL Server instance, the client must know a combination of network protocol and channel over which SQL Server is accepting connection requests. The client may “guess” these using well-known combinations, such as a default instance of SQL Server using the TCP protocol to take connections via TCP port 1433.  However, if well-known combinations are not being used by the instance, the client must either be provided the network protocol and channel information in advance or a secondary service, the SQL Server Browser Windows service, must be available to provide this information on request.

The SQL Server Browser Windows service (SQL Browser) listens on UDP port 1434 for request for information. It responds to requesters with the network protocol and channel information (as well as other metadata) for all SQL Server (Database Engine) instances on the server.  A client can then use this information to attempt a connection to a SQL Server instance.

To secure network connectivity to SQL Server consider moving instances from well-known network protocol and channel combinations.  Consider too disabling the SQL Server Browser Windows service or simply registering a sensitive instance of SQL Server as hidden so that SQL Browser does not return any information on it to requestors.  Information on changing TCP ports and pipe names is found here and here, respectively.  Information on hiding a SQL Server instance is found here.

To take this one step further, consider establishing application-specific TCP endpoints, each associated with a differing TCP port.  Doing so provides you the ability to carefully administer how differing applications connect to an instance of SQL Server. Should there be a need to disable connectivity for one application, its endpoint can be stopped while other endpoints remain active for another applications. Information on configuring multiple TCP ports for a SQL Server instance is found here.  Information on granting and revoking endpoint permissions are found here and here.

NOTE Only the TCP and VIA network protocols support the creation of multiple endpoints.

DEMONSTRATION To review the steps required to configure an application-specific TCP endpoint, please review this post.