SQL Server

Endpoint in SQL Server

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

Endpoint in SQL Server

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.

  1. Create Function fn_EndPoint()  
  2. Returns Varchar(100)  
  3. AS  
  4.    Begin  
  5.    Return 'My End Point'  
  6. End   
Now let’s create an endpoint that would expose the function fn_EndPoint that we created above.
  1. Create Endpoint MyEndpoint  
  2. STATE = STARTED  
  3. AS HTTP   
  4. (                                     
  5.     path='/sql/',  
  6.     AUTHENTICATION=(INTEGRATED),  
  7.     PORTS=(CLEAR),  
  8.     SITE ='localhost'  
  9. )  
  10. FOR SOAP  
  11. (                 
  12.     WEBMETHOD 'http://localhost/'.'GetEndPoint'             
  13.    (        
  14.         name='DatabaseName.dbo.fn_EndPoint',  
  15.         SCHEMA = STANDARD  
  16.     ),      
  17.     WSDL = DEFAULT,   
  18.     BATCHES=DISABLED,  
  19.     DATABASE='DatabaseName'  
  20. )End  
Ebook Download
View all
Learn
View all
Membership not found