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;