Endpoint in SQL Server

In this blog you will learn about Endpoint in SQL Server with example.

A SQL Server endpoint is the point of entry into SQL Server, or a way to connect to SQL Serverinstance.

Endpoints in SQL Server are similar to Web Services that expose database access over HTTP.

There are 5 system endpoints that are automatically created and that can’t be dropped (you can only start or stop them), one for each protocol:

• TCP/IP
• Shared Memory
• Named Pipe
• VIA
• One endpoint for the dedicated administrator connection (DAC).

Query to list all end points in SQL Server

select * from sys.endpoints

Output



Endpoints which have ID less then 65536 are system endpoints.

Creating endpoint

Before creating an endpoint, we need to reserve an HTTP Namespace to expose the endpoint.

EXEC sp_reserve_http_namespace N'http://localhost:80/sql'

Reserved HTTP Namespace can be deleting using below command

sp_reserve_http_namespace N'https://localhost:80/sql'

Let's First create a function which we would expose using the endpoint.

Create Function fn_EndPoint()
Returns Varchar(100)
AS
   Begin
   Return 'My End Point'
End

Now let’s create an endpoint that would expose the function fn_EndPoint that we created above.

Create Endpoint MyEndpoint
STATE = STARTED
AS HTTP
(
    path='/sql/',
    AUTHENTICATION=(INTEGRATED),
    PORTS=(CLEAR),
    SITE ='localhost'
)
FOR SOAP
(
    WEBMETHOD 'http://localhost/'.'GetEndPoint'
   (
        name='DatabaseName.dbo.fn_EndPoint',
        SCHEMA = STANDARD
    ),
    WSDL = DEFAULT,
    BATCHES=DISABLED,
    DATABASE='DatabaseName'
)End