Understanding SQL Server Endpoint Types

A SQL Server endpoint is essentially a communication port that allows different systems or applications to interact with the SQL Server database engine. It acts as a gateway, facilitating communication between the SQL Server instance and external entities,

SQL Server supports various endpoint types, each designed to accommodate specific communication protocols and requirements.
These can be broadly categorized into two types: system-defined endpoints and user-defined endpoints.

System-Defined Endpoints
System-defined endpoints are preconfigured by SQL Server and are integral to its functionality. They include:
SELECT * FROM sys.endpoints;    

User-Defined Endpoints
User-defined endpoints are created and configured by database administrators or developers to serve specific requirements. They include:

TSQL Endpoint: Allows remote connections for executing Transact-SQL commands against the SQL Server instance.
HTTP Endpoint: Facilitates communication using HTTP/HTTPS protocols, often utilized for web services integration.

Creating TSQL Endpoints:
Enable TCP/IP Protocol: Ensure TCP/IP protocol is enabled for SQL Server.
EXEC sp_configure 'remote access', 1;
RECONFIGURE;
 

Use the CREATE ENDPOINT statement to create a TSQL endpoint.
CREATE ENDPOINT [MyTsqlEndpoint]
STATE = STARTED
AS TCP (LISTENER_PORT = 1433)
FOR TSQL();
 

Grant necessary permissions to the endpoint.

GRANT CONNECT ON ENDPOINT::[MyTsqlEndpoint] TO [YourUser];

Creating an HTTP Endpoint:
Enable HTTP Protocol: Enable the HTTP protocol for SQL Server.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

Use the CREATE ENDPOINT statement to create an HTTP endpoint.
CREATE ENDPOINT [MyHttpEndpoint]
STATE = STARTED
AS HTTP (
   PATH = '/MyService',
   AUTHENTICATION = (BASIC),
   PORTS = (CLEAR),
   SITE = 'localhost'
);

 

Grant necessary permissions to the endpoint.
 GRANT CONNECT ON ENDPOINT::[MyHttpEndpoint] TO [YourUser];

Managing Endpoint State
Endpoints can be in one of three states: STARTED, STOPPED, or DISABLED. Here's how you can manage endpoint states:

    STARTED: Endpoint is active and accepting connections.
    STOPPED: Endpoint is inactive but can be started.
    DISABLED: Endpoint is inactive and cannot be started.

Change the state of an endpoint: use the ALTER ENDPOINT statement:
ALTER ENDPOINT [MyEndpoint] STATE = STOPPED;