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 Native Client. The SNI protocol layer is not directly configured. Instead, the server and SQL Native Client are configured to use a network protocol. Then, the Database Engine and SQL 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 on how to configure the server, see Configuring Server Network Protocols and Net-Libraries.

On the client computer, SQL Native Client must be installed and configured to use a network protocol enabled on the server. For more information on 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 Microsoft 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.

The network protocols necessary to communicate with SQL Server 2005 from another computer are often not enabled for SQL Server during installation. To connect from a client computer, therefore, you may need 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 either the Surface Area Configuration tool or 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 2005 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.

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 SQL Server Configuration Manager, although they do appear in the Surface Area Configuration tool.

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.

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 the 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:

1. If the IP address and TCP port exactly match the IP address and TCP port of an endpoint, the endpoint is used.
2. 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.
3. 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 illustrates the 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

Further assume that the following TCP endpoints are established.

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 by Using Transact-SQL

Endpoints are created and managed 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 creating 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.

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

See Also

Other Resources

CREATE ENDPOINT (Transact-SQL)
ALTER ENDPOINT (Transact-SQL)
DROP ENDPOINT (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History