Click here to monitor SSC
  • Av rating:
  • Total votes: 135
  • Total comments: 9
William Brewer

SQL Server Endpoints: Soup to Nuts

06 July 2007

A SQL Server endpoint is the point of entry into SQL Server. It is implemented as a database object that defines the ways and means  in which SQL Server may communicate over the network. SQL Server 2005 routes all interactions with the network via endpoints and each endpoint supports a specific type of communication.

A SQL Server endpoint is a useful point where one can enhance the security of a SQL Server 2005 installation. If, for example, you want to allow your DBAs to monitor a production database from afar then you'll need to set up remote access to a Server via the internet that only your support team can use. This is where endpoints come in. Endpoints, which are a general term for the point of connection between a client or server and the network, can be used in SQL Server in much the same way as a firewall, in order to limit the type of traffic to just what you, as administrator, expect and want. The advantage of a user-defined endpoint is that traffic must be authorised before it even reaches SQL Server. If you are implementing SOAP-based services, mirroring or Service Broker, then you are probably already up to your elbows in endpoints. They are essential for security.

When SQL Server 2005 is installed, a number of 'system endpoints' are set up in the MASTER database. You can start or stop these endpoints using the Surface Configuration tool. If an endpoint is stopped, it listens for, but rejects and closes new connections. These system endpoints provide a system that works in a manner similar to previous versions of SQL Server. You will not see the advantages of endpoints until you create your own. If you wish to set up an HTTP service such as SOAP, then you will need to set up an additional User endpoint to do it. The same is true when you are setting up Database Mirroring or Service Broker. You can define, alter, delete or reconfigure any number of user endpoints by using TSQL statements: You can also disable user endpoints. If a user endpoint is disabled, it acts as if it doesn't exist.

An endpoint has a transport, which will either be HTTP or TCP. You also specify a 'payload', which is one of TSQL, Service_Broker, Database_Mirroring, or SOAP. SOAP must use HTTP, and the others must use TCP. The endpoints, other than TSQL, have a number of special-purpose properties that define the way that the service using them communicates, and operates.

A SQL Server login must have permission to use an endpoint. (a CONNECT permission). By default, all PUBLIC groups have permission to use the default TCP connection. There is a 'Dedicated Admin Connection' endpoint that can only be used by members of the SysAdmin role. In order to tie down access security as much as possible, the DBA will be interested in replacing the implicit permission to access the other TDS endpoints to all users, with something more precise..

System Endpoints

When SQL Server is installed, a 'system endpoint' is created for each of the four protocols (TCP/IP, Shared Memory, Named Pipe, VIA) that accept TDS connections. The public group is given connection rights to all these, which allows all logins defined on the server to use these endpoints.. An additional system endpoint is created for the Dedicated Administrator Connection (DAC), which can only be used by members of the sysadmin fixed server role. These endpoints cannot be dropped or disabled, but you can can stop and start them. Additionally, the state can be changed via the TSQL 'ALTER ENDPOINT' DDL. When looking at endpoints via DMVs, one can distinguish system endpoints since they have an ID less than 65536. Because these endpoints are created internally by the server, they have no owner and you cannot associate them with a specific account.

The SQL Server Configuration Manager is the easiest way to alter the properties of the system endpoints. The settings for the TDS endpoints are recorded in the registry. However, one should only use Transact-SQL statements to create endpoints, and use SQL Server Configuration Manager to enable or disable protocols, which, in turn, starts and stops the endpoints.

Creating User Endpoints

Endpoints can be created and managed and dropped with CREATE ENDPOINT, ALTER ENDPOINT and DROP ENDPOINT statements. (Not, unfortunately in SQL Server Express). There are other statements such as GRANT CONNECT that are used to control or and take ownership of endpoints. Once you have created an endpoint, you will need to give CONNECT permission to the logins that are being used by the client to access SQL Server, and you may need to restore PUBLIC access to the default endpoint for the payload if appropriate.

TCP Endpoints

These are configured to listen on specific port numbers and server IP addresses. The system endpoint for TCP is configured to use port 1433 for backward-compatibility. Other ports can be used. The TCP endpoint can also be forced to listen for requests from just one IP address rather than all. Once you create a new endpoint, the public permission for connection to the TCP system endpoint is dropped. To create a TCP TDS endpoint called MyFirstUserConnection on port 1680 for all the available TCP addresses on the server.

CREATE ENDPOINT [MyFirstUserConnection]
STATE 
STARTED
AS TCP
   
(LISTENER_PORT 1680LISTENER_IP =ALL)
FOR TSQL() ;
GO

To grant access to this MyFirstUserConnection endpoint to the Support group in the MyFirm domain.
GRANT CONNECT ON ENDPOINT::[ MyFirstUserConnection] TO [MyFirm\Support] ;

If you want a system endpoint to listen on an additional TCP port, you can use SQL Server Configuration Manager to do so.

First expand 'SQL Server 2005 Network Configuration' in the left-side tree

  • Click 'Protocols for '.
  • Expand 'Protocols for ', and right-click TCP/IP.Select 'Properties'
  • In the 'IP Addresses' tab of the properties dialog box, click each disabled IP address that you want to enable, and then click Enable.
  • select the IPAll entry in the list,
  • Type in a comma-separated list of all the ports that you want the Database Engine to listen on, in the TCP Port box. If you want to specify particular IP addresses, rather than use all of them, right-click TCP/IP in the console pane, click Properties, select the 'protocol tab, and, select No in the 'Listen All' box
  • In the left pane, click 'SQL Server 2005 Services'.
  • In the right pane, right-click 'SQL Server < MyInstance>', and then click 'Restart'.
  • When the Database Engine restarts, the Error log will list the ports on which SQL Server is now listening.

For altering User TDS Endpoints, you will need to use TSQL as they do not show up in the Configuration Manager. However, once these are in place, they require little or no maintenance.

Database Mirroring and Service Broker Endpoints

SQL Server does not contain a Service Broker or Database Mirroring endpoint until you create one. You can create only one Service Broker, or Database mirroring endpoint on an instance. They use Transmission Control Protocol (TCP) to send and receive messages. Each endpoint listens on a unique TCP port number. The endpoint of a server instance controls the port on which that instance listens for messages from other server instances.

You can specify the authentication and encryption methods. Within a domain, or between trusted domains, Windows authentication is best; otherwise certificate-based authentication should be used. Strong encryption techniques will inevitably affect performance, so the default choice of RC4 is usually better than the stronger AES algorithm, unless you are operating in a relatively insecure network.

A Service Broker endpoint configures SQL Server to send and receive Service Broker messages over the network. Service Broker endpoints provide additional options for message forwarding.

The database mirroring endpoint of a server instance controls the port on which that instance listens for database mirroring messages from other server instances. Database Mirroring endpoints must also specify whether the endpoint should be a PARTNER, WITNESS or ALL. SQL Server Express can only be a witness.

The easiest way to set up Database Mirroring endpoints is to use the 'Configure Database Mirroring Security' Wizard, from the 'Configure Security' button on the Mirroring page of the Database Properties dialog in SSMS. But you can also execute the CREATE ENDPOINT command using Transact-SQL.

Here is an example of code to create a Database Mirroring endpoint

CREATE ENDPOINT endpoint_mirroring 
      STATE 
STARTED AS TCP LISTENER_PORT 7022 
   
FOR DATABASE_MIRRORING 
     
AUTHENTICATION WINDOWS KERBEROS
       
ENCRYPTION SUPPORTED
       
ROLE=ALL); 
GO

HTTP Endpoints

These are required for setting up a web service on SQL Server 2005. No default HTTP endpoint exists, but must be explicitly created and specified. These are more complex than the other types of endpoint because there are parameters for setting up Authentication method, Encryption, Login Type, Web Method, WSDL support and SOAP payload.

HTTP endpoints are created with a unique URL that they use to listen for incoming HTTP requests. SOAP requests that are sent to this URL will be routed by HTTP.SYS to the SQL Server instance that hosts the endpoint associated with the URL. From there, they are sent to the SOAP processing layer within SQL Server.

A SQL Server instance can have several endpoints, each of which can expose any number of stored procedures, as WebMethods on the endpoint. These WebMethods can be invoked via SOAP remote procedure calls. A WebMethod can have a different name than the actual stored procedure that is being exposed. The WebMethod name is what is shown to the user in WSDL as the operation name.

Users can be given permission to execute ad-hoc Transact-SQL statements against the endpoints by enabling batches on the endpoint. This results in a WebMethod named "sqlbatch" being exposed to the user.

All requests, including requests for WSDL, are authenticated. Clients must authenticate against SQL Server principals in order to submit any request. When setting up an HTTP endpoint, you will need to decide between Basic, Digest, Integrated (NTLM, Kerberos), and SQL Authentication. Any client can connect to a SQL Server Web Service by using either BASIC or SQL Auth. However, as BASIC requires the passwords to be sent over in clear text, users can connect only on secure ports that also have SSL enabled. (using the command httpcfg which ships with the support tools)

A connection firstly authenticates at the HTTP transport level. If successful, the user's SID is used to authenticate with SQL. The exception is SQL Auth. The SQL Auth credentials are sent as part of the SOAP packet using WsSecurity Username token headers. One can also restrict access to only specified IPs or ranges of IPs. Even if a stored procedure is mapped, it can only be executed if the user has CONNECT permissions on the endpoint as well as EXECUTE permissions on the stored procedure.

When an endpoint is created, only members of the sysadmin role and the owner of the endpoint can connect to the endpoint. You must grant connect permission for users to access your endpoint; this is accomplished by executing the following statement:

GRANT CONNECT ON HTTP ENDPOINT::MyLittleEndpoint TO [DOMAIN\USER] 

Securing a User Endpoint

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. When SQL Server is set up this will not be apparent because permission to connect to the default System endpoints is implicitly granted to users when logins are created.

When a new TCP endpoint is created, SQL Server automatically revokes all existing permissions on the TSQL Default TCP endpoint.

To restrict access to an endpoint, the administrator can deny permission to the EVERYONE group, using the DENY CONNECT statement. Then, he can grant permission to specific individuals or roles, using the GRANT CONNECT statement.

If one must return permissions to their original state, then GRANT CONNECT permission to the PUBLIC group.

To provide an endpoint exclusively for a specific application, DENY CONNECT permissions to all users, except the users for that application.

Altering a User Endpoint

The best and easiest way of inspecting or altering a simple system  TDS endpoint is with the SQL Server Configuration Manager. You can use the ALTER ENDPOINT Statement in TSQL to alter the properties of any endpoint. You need specify only those parameters that you want to update, and all other properties of an existing endpoint stay the same. The ENDPOINT DDL statements cannot be executed inside a user transaction.

Looking at endpoints

Endpoints can be inspected in one of the catalog views (see Endpoints Catalog Views (Transact-SQL))

e.g.

SELECT FROM sys.endpoints

These catalog views are:

sys.endpoints All endpoints and all generic properties
sys.database_mirroring_endpoints The Database Mirroring endpoints
sys.service_broker_endpoints The Service Broker endpoints
sys.soap_endpoints HTTP endpoints that carry a SOAP-type payload
sys.endpoint_webmethods SOAP methods defined on endpoints
sys.tcp_endpoints All TCP endpoints and properties
sys.http_endpoints All http endpoints and HTTP properties
sys.via_endpoints All VIA endpoints and properties

Further reading

Books on Line have plenty of examples of TSQL configuration of endpoints and are good on the syntax of the CREATE ENDPOINT statement.

William Brewer

Author profile:

William Brewer is a SQL Server developer who has worked as a Database consultant and Business Analyst for several Financial Services organisations in the City of London. True to his name, he is also an expert on real ale.

Search for other articles by William Brewer

Rate this article:   Avg rating: from a total of 135 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Mirroring
Posted by: Anonymous (not signed in)
Posted on: Monday, July 23, 2007 at 1:30 AM
Message: Dear Willam
i found many things in ur article really it was outstanding
Great!

Regards
Syed Naveed
Naveed_shah15@hotmail.com

Subject: Re: Mirroring
Posted by: WBrewer (view profile)
Posted on: Tuesday, July 24, 2007 at 3:01 PM
Message: Bless you Syed, and thank you. I needed that!

Subject: yes, but how do you make end-points public ?
Posted by: Anonymous (not signed in)
Posted on: Wednesday, July 25, 2007 at 8:11 AM
Message: yes, but how do you make end-points public ?

Subject: Mirroring
Posted by: Anonymous (not signed in)
Posted on: Friday, August 3, 2007 at 4:02 AM
Message: Thank you William for a simple non-confusing overview of endpoints. At last someone realises that this is the type of article that is needed when new technology is released.

Jane Howell

Subject: public endpoints
Posted by: Anonymous (not signed in)
Posted on: Monday, December 24, 2007 at 4:58 PM
Message: yes, but how do you make end-points public ? i want to access it publically..

mail me : jatin.purba@gmail.com

Subject: And then...?
Posted by: Anonymous (not signed in)
Posted on: Wednesday, April 2, 2008 at 2:43 PM
Message: What next?> Can you go one step further and show someone connecting to an endpoint in a simple manner? like, with IE or something?

Subject: Mirroring
Posted by: James BlueBlood (view profile)
Posted on: Tuesday, August 4, 2009 at 1:54 AM
Message: Hi,

can anyone give the complete material on Mirroring.. any videos.. any URL to download the Mirroring material in sql server..

thank you
BlueBlood

Subject: Great Article!!!
Posted by: Devashish (view profile)
Posted on: Saturday, June 26, 2010 at 6:56 AM
Message:
SQL Server endpoints made really easy!!

Subject: .
Posted by: Cody (view profile)
Posted on: Thursday, June 18, 2015 at 12:34 AM
Message: The article states "once you create a new endpoint, the public permission for connection to the TCP system endpoint is dropped".

It wasn't clear to me what exactly constituted a "user" endpoint, for example if the service broker endpoint is created then will the public permissions on the default TCP TSQL endpoint be dropped?

The answer is no, I just tested it out. The connect permission only gets dropped if you create another FOR TSQL() endpoint.

 
Simple-Talk Database Delivery

DLM
Patterns & Practices Library

Visit our patterns and practices library to learn more about database lifecycle management.

Find out how to automate the process of building, testing and deploying your database changes to reduce risk and make rapid releases possible.

Get started

Phil Factor
Microsoft and Database Lifecycle Management (DLM): The DacPac

The Data-Tier Application Package (DacPac), together with the Data-Tier Application Framework (DacFx), provides an... Read more...

 View the blog

Top Rated

Working with SQL Server data in Power BI Desktop
 What's the best way of providing self-service business intelligence (BI) to data that is held in... Read more...

Microsoft and Database Lifecycle Management (DLM): The DacPac
 The Data-Tier Application Package (DacPac), together with the Data-Tier Application Framework (DacFx),... Read more...

A Start with Automating Database Configuration Management
 For a number of reasons, it pays to have the up-to-date source of all the databases and servers that... Read more...

Archiving Hierarchical, Deleted Transactions Using XML
 When you delete a business transaction from the database, there are times when you might want to keep a... Read more...

Rollback and Recovery Troubleshooting; Challenges and Strategies
 What happens if your database deployment goes awry? Do you restore from a backup or snapshot and lose... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

Temporary Tables in SQL Server
 Temporary tables are used by every DB developer, but they're not likely to be too adventurous with... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.