SQL Server Endpoints: Soup to Nuts

SQL Server Endpoints are database objects that define the ways and means that SQL Server 2005 communicates on the network. Any DBA working with SQL Server 2005 will soon need to become familiar with them, particularly if using SOAP, Service Broker or Database Mirroring.

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 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 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 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.
To grant access to this MyFirstUserConnection endpoint to the Support group in the MyFirm domain.
GRANT CONNECT ON ENDPOINT::[ MyFirstUserConnection] TO [MyFirm\Support] ;

407-ConfigManager4.jpg

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

407-ConfigManager.jpg

First expand ‘SQL Server 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, 407-ConfigManager2.jpg
  • 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 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

HTTP Endpoints

These are required for setting up a web service on SQL Server. 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. (note, SOAP is now deprecated)

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 407-ConfigManager3.jpg

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.

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.

Tags: , , , , , ,

  • 109481 views

  • Rate
    [Total: 145    Average: 4.2/5]
  • Anonymous

    Mirroring
    Dear Willam
    i found many things in ur article really it was outstanding
    Great!

    Regards
    Syed Naveed
    Naveed_shah15@hotmail.com

  • WBrewer

    Re: Mirroring
    Bless you Syed, and thank you. I needed that!

  • Anonymous

    yes, but how do you make end-points public ?
    yes, but how do you make end-points public ?

  • Anonymous

    Mirroring
    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

  • Anonymous

    public endpoints
    yes, but how do you make end-points public ? i want to access it publically..

    mail me : jatin.purba@gmail.com

  • Anonymous

    And then…?
    What next?> Can you go one step further and show someone connecting to an endpoint in a simple manner? like, with IE or something?

  • James BlueBlood

    Mirroring
    Hi,

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

    thank you
    BlueBlood

  • Devashish

    Great Article!!!

    SQL Server endpoints made really easy!!

  • Cody

    .
    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.