Network Protocols and TDS Endpoints
When the SQL Server Database Engine communicates with an application, it formats the communication in a Microsoft communication format called a tabular data stream (TDS) packet. The network SQL Server Network Interface (SNI) protocol layer, which replaces the Net-Libraries for SQL Server 2000 and Microsoft Data Access Components (MDAC), encapsulates the TDS packet inside a standard communication protocol, such as TCP/IP or named pipes. The SNI protocol layer is common to both the Database Engine and SQL Server Native Client. The SNI protocol layer is not directly configured. Instead, the server and SQL Server Native Client are configured to use a network protocol. Then, the Database Engine and SQL Server Native Client automatically use the appropriate protocol settings. The server creates a SQL Server object called a TDS endpoint for each network protocol. On the server, the TDS endpoints are installed by SQL Server during SQL Server installation.
This topic describes how the server creates and uses TDS endpoints. For more information about how to configure the server, see Configuring Server Network Protocols and Net-Libraries.
On the client computer, SQL Server Native Client must be installed and configured to use a network protocol enabled on the server. For more information about clients, see Configuring Client Network Protocols.
Enabling Server Protocols After Setup
The underlying operating system network protocols (such as TCP/IP) should already be installed on the client and server. Network protocols are typically installed during Windows setup; they are not part of SQL Server setup. If the required network protocol is not available and configured on server, Database Engine will not start. If the required network protocol is not available and configured on the client, the Network Library will not work.
Note
In the rest of this topic, "enabling a protocol" means enabling the protocol for SQL Server, not for the operating system.
The network protocols necessary to communicate with SQL Server from another computer are often not enabled for SQL Server during installation. To connect from a client computer, therefore, you may have to enable the TCP/IP, named pipes, or VIA protocol. (The shared memory protocol is enabled by default on all installations, but can only be used to connect to Database Engine from a client application on the same computer.) For information about which network protocols are enabled for your type of installation, see Default SQL Server Network Configuration.
To enable the network protocols, use the SQL Server Configuration Manager. Alternatively, protocols can be enabled during installation by using options at the command prompt. For more information, see How to: Install SQL Server 2008 from the Command Prompt.
After the network connections are installed and configured, SQL Server can listen on any combination of the server network protocols simultaneously.
TDS Endpoints Defined
A TDS endpoint is the SQL Server object that represents the communication point between SQL Server and a client. SQL Server automatically creates an endpoint for each of the four protocols supported by SQL Server. By default, all users have access to them when the protocols are enabled. If a network protocol is not enabled, the endpoint still exists, but cannot be used. An additional endpoint is created for the dedicated administrator connection (DAC), which can only be used by members of the sysadmin fixed server role.
SQL Server generates a unique name for each TDS endpoint. The endpoints that are automatically created are shown in the following table.
Purpose |
Endpoint name |
|---|---|
Shared memory |
TSQL LocalMachine |
Named pipes |
TSQL Named Pipes |
TCP/IP |
TSQL Default TCP |
VIA |
TSQL Default VIA |
DAC |
Dedicated Admin Connection |
HTTP |
HyperText Transport Protocol |
For the named pipes and shared memory protocols, there can only be one endpoint per instance. There are no configurable endpoints for these protocol types. For TCP/IP and VIA, there is a default endpoint, but additional endpoints can be created. HTTP endpoints are also user-created and do not appear in the SQL Server Configuration Manager.
Only the owner and the state can be changed on the system endpoints (by using ALTER ENDPOINT). You cannot disable the default endpoints, but you can stop and start them. A stopped endpoint still listens, but it rejects and closes new connections.
Note
By default, clients are configured to try all protocols until one of them works. If the TCP/IP protocol is disabled, clients continue with the next protocol. If TCP/IP is enabled but the endpoint is stopped, the connection attempt is not rejected, so the client does not try other protocols, but the stopped connection cannot be used. In this case, you must explicitly connect to a active endpoint.
Dynamic TCP ports usually connect to the default TCP endpoint.
Protocol Settings in the Registry
The settings for the TDS endpoints are recorded in the registry. Users should use Transact-SQL statements to create or alter endpoints, and use SQL Server Configuration Manager to enable or disable protocols, which starts and stops the endpoints.
When the user changes protocol settings in the registry, nothing happens. The registry is separate from the metadata.
Associating a User Connection with an Endpoint
When connecting to Database Engine, SQL Server associates the connection with a specific endpoint and evaluates whether the connecting login has been granted permission to use the endpoint. Connections are associated as follows:
Shared memory connections use the TSQL LocalMachine endpoint.
Named pipes connections use the TSQL Named Pipes endpoint.
Dedicated administrator connections use the Dedicated Admin Connection endpoint.
By default, TCP connections use the TSQL Default TCP endpoint. If a new (user-defined) TCP endpoint is created for a specific TCP port, connections to that TCP port will connect to that new endpoint. If a new TCP/TSQL endpoint is created using IP_ANY as the port, then TCP connections will connect to that new endpoint.
VIA connections are treated the same way as TCP connections.
User-defined endpoints behave the same way as default endpoints. When an endpoint is created for an IP address (or all IP addresses, by using IP_ANY) and a specific TCP port, permission to connect to the endpoint is granted to users in a process called provisioning. They retain the provisioning regardless of whether or not the server is actually listening on the IP address/TCP port combination. A connection to an IP address and TCP port is matched to an endpoint in the following order:
If the IP address and TCP port exactly match the IP address and TCP port of an endpoint, the endpoint is used.
If there is not an exact match, the TCP port is checked against all IP_ANY endpoints, and if the TCP port is listed, that endpoint is used.
If there is no exact port match, the default TCP endpoint is used.
The connection association process always results in the selection of at most a single endpoint. Permission to connect is checked for that endpoint. If the user does not have permission to that endpoint, the process does not search for the next endpoint.
Examples: Associating User Connections and Endpoints
The following example shows how the IP address and TCP port are used to select an endpoint. Assume that the server is configured to listen on the following IP addresses and TCP ports:
127.0.0.1:1533
251.40.20.151:1533
251.40.20.151:1433
Further assume that the following TCP endpoints are established:
Endpoint name |
Configured values |
|---|---|
Loopback |
LISTENER_IP= 127.0.0.1 and LISTENER_PORT = 1533 |
Remote |
LISTENER_IP = ALL and LISTENER_PORT = 1533 |
TSQL Default TCP |
Not linked to any IP address or port |
Three possible connection possibilities exist:
If a client makes a TCP connection to 127.0.0.1:1533, the endpoint associated with the session would be the Loopback endpoint, because there is an exact match of IP address and TCP port to the Loopback endpoint.
If a client makes a TCP connection to 251.40.20.151:1533, there is no exact match of the IP address and TCP port to an endpoint, but Remote is available for the connection, because Remote is listening on any IP address and port 1533. If the connecting login does not have permission to the Remote endpoint, the process fails. It does not try to connect to other possible endpoints, such as TSQL Default TCP, to which the login may have permission.
If a client makes a TCP connection to 251.40.20.151:1433, there is no exact match of the IP address and TCP port to an endpoint, and there is no match for TCP port 1533 with any IP address, but TSQL Default TCP is available for the connection, because TSQL Default TCP is listening on any IP address and any port.
Upgrade and/or Install
By default, all users have access to the TDS endpoints (except the dedicated administrator connection endpoint). Because these endpoints are created internally by the server, they have no owner and you cannot associate them with a specific account.
Managing Endpoints with Transact-SQL
Endpoints are created and managed by using Transact-SQL. They are created and dropped with CREATE ENDPOINT and DROP ENDPOINT statements. There are also statements to control, alter, and take ownership of endpoints.
To connect to an instance of SQL Server using Transact-SQL endpoints, users must have CONNECT permission to an endpoint and global permission on SQL Server to log in. Permission to connect to the default endpoints is implicitly granted to users when logins are created. Access to endpoints is managed with GRANT | DENY | REVOKE CONNECT ON ENDPOINT.
When a new TCP endpoint is created, SQL Server automatically revokes all existing permissions on the TSQL Default TCP endpoint. For an example of how to create a new TCP endpoint, see How to: Configure the Database Engine to Listen on Multiple TCP Ports.
To restrict access to an endpoint, the administrator can deny permission to the EVERYONE group (using the DENY CONNECT statement) and then grant permission to specific individuals or roles (using the GRANT CONNECT statement).
To return permissions to the original state, GRANT CONNECT permission to the PUBLIC group.
To provide an endpoint for a specific application, DENY CONNECT permissions to all users except the users for that application.
.gif "Security note") Security Note |
|---|
Authorization to use an endpoint is associated with the name of the endpoint. If an endpoint name changes, the security restrictions (for example, DENY CONNECT statements) will no longer be properly applied. The name of an endpoint is changed when the port changes. If SQL Server is listening on dynamic ports, the port can change, changing the endpoint name, and dropping the associated endpoint permissions. To avoid this security risk, do not set custom permissions on endpoints associated with dynamic ports, and do not change the order in which a TCP/IP endpoint occurs in the registry. |
For more information about how to set security for endpoints, see GRANT Endpoint Permissions (Transact-SQL).