SQL Server Endpoints: Soup to Nuts
by William Brewer
A SQL Server endpoint is the pointof entry into SQL Server. It is implemented as a database objectthat defines the ways and means in which SQL Server maycommunicate over the network. SQL Server 2005 routes all interactions with thenetwork via endpoints and each endpoint supports a specific type ofcommunication.
A SQL Server endpoint is a usefulpoint 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 fromafar then you'll need to set up remote access to a Server via the internet thatonly 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 serverand 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 mustbe authorised before it even reaches SQL Server. If you are implementingSOAP-based services, mirroring or Service Broker, then you are probably alreadyup 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 startor stop these endpoints using the Surface Configuration tool. If an endpoint isstopped, it listens for, but rejects and closes new connections. These systemendpoints provide a system that works in a manner similar to previous versionsof SQL Server. You will not see the advantages of endpoints until you createyour own. If you wish to set up an HTTP service such as SOAP, then you willneed to set up an additional User endpoint to do it. The same is true when youare 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 asif it doesn't exist.
An endpoint has a transport, whichwill 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 othersmust use TCP. The endpoints, other than TSQL, have a number of special-purposeproperties that define the way that the service using them communicates, andoperates.
A SQL Server login must havepermission to use an endpoint. (a CONNECT permission). By default, all PUBLICgroups have permission to use the default TCP connection. There is a 'DedicatedAdmin Connection' endpoint that can only be used by members of the SysAdminrole. In order to tie down access security as much as possible, the DBA will beinterested in replacing the implicit permission to access the other TDSendpoints 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, SharedMemory, Named Pipe, VIA) that accept TDS connections. The public group is givenconnection rights to all these, which allows all logins defined on the serverto use these endpoints.. An additional system endpoint is created for theDedicated Administrator Connection (DAC), which can only be used by members ofthe 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 viathe TSQL 'ALTER ENDPOINT' DDL. When looking at endpoints via DMVs, one candistinguish system endpoints since they have an ID less than 65536. Becausethese endpoints are created internally by the server, they have no owner andyou cannot associate them with a specific account.
The SQL Server ConfigurationManager is the easiest way to alter the properties of the system endpoints. Thesettings for the TDS endpoints are recorded in the registry. However, oneshould only use Transact-SQL statements to create endpoints, and use SQL ServerConfiguration Manager to enable or disable protocols, which, in turn, startsand stops the endpoints.
Creating User Endpoints
Endpoints can be created andmanaged and dropped with CREATEENDPOINT, ALTERENDPOINT and DROP ENDPOINTstatements.(Not, unfortunately in SQL Server Express). There are other statements such as GRANT CONNECTthat areused to control or and take ownership of endpoints. Once you have created anendpoint, you will need to give CONNECT permission to the logins that are beingused by the client to access SQL Server, and you may need to restore PUBLICaccess to the default endpoint for the payload if appropriate.
TCP Endpoints
These are configured to listen onspecific port numbers and server IP addresses. The system endpoint for TCP isconfigured to use port 1433 for backward-compatibility. Other ports can beused. The TCP endpoint can also be forced to listen for requests from just oneIP address rather than all. Once you create a new endpoint, the publicpermission for connection to the TCP system endpoint is dropped. To create aTCP TDS endpoint called MyFirstUserConnection on port 1680 for all theavailable TCP addresses on the server.
CREATE ENDPOINT [MyFirstUserConnection]
STATE = STARTED
AS TCP
(LISTENER_PORT = 1680, LISTENER_IP =ALL)
FOR TSQL() ;
GO
Togrant access to this MyFirstUserConnection endpoint to the Support group in theMyFirm domain.
GRANT CONNECT ON ENDPOINT::[ MyFirstUserConnection] TO [MyFirm\Support] ;
If youwant a system endpoint to listen on an additional TCP port, you can useSQL Server Configuration Manager to do so.
Firstexpand '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 andService Broker Endpoints
SQL Server does not contain aService Broker or Database Mirroring endpoint until you create one. You cancreate only one Service Broker, or Database mirroring endpoint on an instance.They use Transmission Control Protocol (TCP) to send and receive messages. Eachendpoint listens on a unique TCP port number. The endpoint of a server instancecontrols the port on which that instance listens for messages from other serverinstances.
You can specify the authenticationand encryption methods. Within a domain, or between trusted domains, Windowsauthentication is best; otherwise certificate-based authentication should beused. Strong encryption techniques will inevitably affect performance, so thedefault choice of RC4 is usually better than the stronger AES algorithm, unlessyou are operating in a relatively insecure network.
A Service Broker endpointconfigures SQL Server to send and receive Service Broker messages over thenetwork. Service Broker endpoints provide additional options for messageforwarding.
The database mirroring endpoint ofa server instance controls the port on which that instance listens for databasemirroring messages from other server instances. Database Mirroring endpointsmust also specify whether the endpoint should be a PARTNER, WITNESS or ALL. SQLServer Express can only be a witness.
The easiest way to set up DatabaseMirroring endpoints is to use the 'Configure Database Mirroring Security'Wizard, from the 'Configure Security' button on the Mirroring page of theDatabase Properties dialog in SSMS. But you can also execute the CREATEENDPOINT command using Transact-SQL.
Here is an example of code tocreate 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 upa web service on SQL Server 2005. No default HTTP endpoint exists, but must beexplicitly created and specified. These are more complex than the other typesof 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 aunique URL that they use to listen for incoming HTTP requests. SOAP requeststhat are sent to this URL will be routed by HTTP.SYS to the SQL Server instancethat hosts the endpoint associated with the URL. From there, they are sent tothe SOAP processing layer within SQL Server.
A SQL Server instance can haveseveral endpoints, each of which can expose any number of stored procedures, asWebMethods on the endpoint. These WebMethods can be invoked via SOAP remoteprocedure calls. A WebMethod can have a different name than the actual storedprocedure that is being exposed. The WebMethod name is what is shown to theuser in WSDL as the operation name.
Users can be given permission toexecute ad-hoc Transact-SQL statements against the endpoints by enablingbatches on the endpoint. This results in a WebMethod named "sqlbatch"being exposed to the user.
All requests, including requestsfor WSDL, are authenticated. Clients must authenticate against SQL Serverprincipals in order to submit any request. When setting up an HTTP endpoint,you will need to decide between Basic, Digest, Integrated (NTLM, Kerberos), andSQL Authentication. Any client can connect to a SQL Server Web Service by usingeither BASIC or SQL Auth. However, as BASIC requires the passwords to be sent overin clear text, users can connect only on secure ports that also have SSLenabled. (using the command httpcfg which ships with the support tools)
A connection firstly authenticatesat the HTTP transport level. If successful, the user's SID is used to authenticatewith SQL. The exception is SQL Auth. The SQL Auth credentials are sent as partof the SOAP packet using WsSecurity Username token headers. One can alsorestrict access to only specified IPs or ranges of IPs. Even if a storedprocedure is mapped, it can only be executed if the user has CONNECTpermissions on the endpoint as well as EXECUTE permissions on the storedprocedure.
When an endpoint is created, onlymembers of the sysadmin role and the owner of the endpoint can connect to theendpoint. 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 SQLServer using Transact-SQL endpoints, users must have CONNECT permission to anendpoint and global permission on SQL Server to log in. When SQL Server is setup this will not be apparent because permission to connect to the defaultSystem endpoints is implicitly granted to users when logins are created.
When a new TCP endpoint iscreated, SQL Server automatically revokes all existing permissions on the TSQLDefault TCP endpoint.
To restrict access to an endpoint,the administrator can deny permission to the EVERYONE group, using the DENYCONNECT statement. Then, he can grant permission to specific individuals orroles, using the GRANT CONNECT statement.
If one must return permissions totheir original state, then GRANT CONNECT permission to the PUBLIC group.
To provide an endpoint exclusivelyfor a specific application, DENY CONNECT permissions to all users, except theusers for that application.
Altering a User Endpoint
The best and easiest way ofinspecting or altering a simple system TDS endpoint is with the SQLServer Configuration Manager. You can use the ALTERENDPOINT Statementin TSQL to alter the properties of any endpoint. You need specify only thoseparameters that you want to update, and all other properties of an existingendpoint stay the same. The ENDPOINT DDL statements cannot be executed inside auser transaction.
Looking at endpoints
Endpoints can be inspected in oneof 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
HTTPEndpoints
So whatis an HTTP Endpoint? You may have heard that it is SQL Server 2005's means forcreating Web services, but it actually is much more. An HTTP Endpoint also is ameans of creating interfaces via HTTP or TCP for SOAP, T?SQL, Service Broker,and even database mirroring. Although these other functions are veryintriguing, this discussion concerns only the ability to create Web services inSQL Server—Web services that can return rowset data, scalar values,messages, and even errors, all of which are serialized into XML automatically.And, an HTTP Endpoint does all of this without requiring you to install IIS (ituses the Windows 2003 kernel module http.sys).
BecauseI like to learn from examples, I am going to teach by example. I demonstratehow to use HTTP Endpoints by walking through an example from start to finish.Before starting, here are a few notes to consider:
This demonstration uses the new demo database called AdventureWorks.
Native HTTP SOAP in SQL Server 2005 is not supported on Windows XP. If you want to try the examples, you need Windows Server 2003.
SQL Server 2005 Express Edition (the new MSDE) does not support HTTP Endpoints, so be sure to install the Developer Edition.
I do not explain how to use SQL Server Management Studio to execute SQL scripts. I trust you know how to do this.
Security
BecauseHTTP Endpoints are a server-level feature, security of endpoints is also on theserver level. Of course, the serveradmin system role can create, alter, anddrop endpoints from the SQL Server instance, but how do you allow developers tomanage these endpoints without giving them the excessive permissions of thissystem role?
Theanswer lies in SQL Server 2005's new ability to assign server-level permissionsto logins, as shown here:
GRANT ALTER ANY HTTP ENDPOINT TO peter
Withthis command, you can allow the peter loginto manage HTTP Endpoints without giving other unnecessary permissions. Thefollowing is a list of permissions that you can modify via GRANT, DENY, orREVOKE:
· {GRANT|DENY|REVOKE} ALTER ANY ENDPOINT TO Login
Controls the ability to alter any HTTP Endpoint; also allowspermission to transfer ownership and connect to any endpoint
· {GRANT|DENY|REVOKE} ALTER ON ENDPOINT:: EndPointName TO Login
Controls whether a login can alter a specific HTTP Endpoint; alsoallows permission to transfer ownership and connect to the specified endpoint
· {GRANT|DENY|REVOKE} CONTROL ON ENDPOINT:: EndPointName TO Login
Controls whether a login can alter or drop a specific HTTPEndpoint; also allows permission to transfer ownership and connect to thespecified endpoint
· {GRANT|DENY|REVOKE} CONNECT ON ENDPOINT:: EndPointName TO Login
Controls whether or not a login can connect to (execute requestsagainst) an HTTP Endpoint
· {GRANT|DENY|REVOKE} TAKE OWNERSHIP ON ENDPOINT:: EndPointName
· TO Login
Controls whether a login can take ownership of the HTTP Endpoint
· {GRANT|DENY|REVOKE} VIEW DEFINITION ON ENDPOINT:: EndPointName
· TO Login
Controls the ability for a login to see the metadata (definition) of the HTTP Endpoint
Considerone last example. If I want to deny the peter loginthe ability to use an HTTP Endpoint named SQLEP_Financial, Iwould execute the following statement:
DENY CONNECT ON ENDPOINT::SQLEP_Financial TO peter
Providing aSource for Data
Thefirst order of business is to create the code components that will be thesource of information for the endpoint. I create two stored procedures and oneuser-defined function (UDF), as shown in Listing 1:
Listing 1
USE AdventureWorksGOCREATE PROCEDURE prProductListASSELECT Product.ProductID, Product.ProductNumber, Product.NameFROM Production.Product AS Product INNER JOIN Production.ProductInventory AS Inventory ON Product.ProductID = Inventory.ProductID INNER JOIN Production.Location AS Location ON Inventory.LocationID = Location.LocationIDWHERE Product.ListPrice > 0 AND Location.LocationID = 6 --Products from Misc Storage onlyORDER BY Product.NameGOCREATE PROCEDURE prProductStockInfo @ProductID intASIF (SELECT ListPrice FROM Production.Product WHERE ProductID = @ProductID) = 0 BEGIN RAISERROR ('Product not available for retail sale', 11, 1) ENDELSE BEGIN SELECT Product.ProductID, Product.ProductNumber, Product.Name, Product.Weight, Product.WeightUnitMeasureCode, Product.ListPrice, Inventory.Quantity FROM Production.Product AS Product INNER JOIN Production.ProductInventory AS Inventory ON Product.ProductID = Inventory.ProductID INNER JOIN Production.Location AS Location ON Inventory.LocationID = Location.LocationID WHERE Product.ProductID = @ProductID AND Location.LocationID = 6 --Misc Storage ENDGOCREATE FUNCTION dbo.fnProductPhoto (@ProductID int)RETURNS varbinary(MAX)AS BEGIN DECLARE @largePhoto varbinary(max) SELECT @largePhoto = ProdPhoto.LargePhoto FROM Production.ProductPhoto AS ProdPhoto INNER JOIN Production.ProductProductPhoto ProdProdPhoto ON ProdPhoto.ProductPhotoID = ProdProdPhoto.ProductPhotoID WHERE ProdProdPhoto.ProductID = @ProductID RETURN (@largePhoto) END
Thefirst procedure returns a list of products (product ID, name, and number) fromthe AdventureWorks database. The second procedure returns more details about aproduct, including inventory levels. The UDF returns a single varbinary valuecontaining the product photo.
Creating theEndpoint
Onceyou have your procedures and functions set up, you can create the endpoint (Webservice). The following code creates the endpoint on my server:
CREATE ENDPOINT SQLEP_AWProducts STATE = STARTEDAS HTTP( PATH = '/AWproducts', AUTHENTICATION = (INTEGRATED), PORTS = (CLEAR), SITE = 'win2k301')FOR SOAP( WEBMETHOD 'ProductList' (NAME='AdventureWorks.dbo.prProductList'), WEBMETHOD 'ProductStockInfo' (NAME='AdventureWorks.dbo.prProductStockInfo'), WEBMETHOD 'ProductPhoto' (NAME='AdventureWorks.dbo.fnProductPhoto'), BATCHES = DISABLED, WSDL = DEFAULT, DATABASE = 'AdventureWorks', NAMESPACE = 'http://Adventure-Works/Products')
Believeit or not, you are now ready to consume this Web service. Before doing that,though, examine the CREATE ENDPOINT statement. Starting from the top, the firstthing you will notice is the name of the endpoint, SQLEP_AWProducts, which ishow you refer to the Web service within your client code (see Figure 1). PATHis the virtual path on the server for the Web service. So, for this example,you would access the Web service at http://win2k301/AWproducts, but it wouldbe called SQLEP_AWProducts in your .NET application code. Table 1 providesexplanations for this and other arguments used in the AS HTTP clause.
Click here for a larger image.
Figure 1: ObjectBrowsing the Web Service in Visual Studio.NET 2005
Table 1: ArgumentsUsed in the AS HTTP Clause
Argument
Description
PATH
The virtual URL path on the server where the Web service will reside
AUTHENTICATION
The method of authentication; can be BASIC, DIGEST, or INTEGRATED
You should try to use INTEGRATED when possible, since it is the most secure. It will try to use Kerberos-based authentication if possible (otherwise, NTLM).
DIGEST is not as secure as INTEGRATED. You should use it only if INTEGRATED authentication is not possible.
BASIC authentication is the least secure. You should use it only if you can't implement either INTEGRATED or DIGEST authentication methods. BASIC requires SSL (see PORTS below).
PORTS
CLEAR (HTTP - port 80 by default) SSL (HTTPS - port 443 by default)
SITE
The name of the server on which the Web service is running
TheSTATE argument can have one of three values:
STARTED—listening and responding
DISABLED—neither listening nor responding
STOPPED—listening, but returns errors to client requests
Youcan change the state of an existing Web service using the ALTER ENDPOINTstatement. For example, to disable the endpoint, you can execute:
ALTER ENDPOINT SQLEP_AWProducts STATE = DISABLED
TheFOR SOAP clause determines which methods the Web service will expose, whichother features are exposed (dynamic SQL, for example), and othercharacteristics of the Web service. In this example, three methods are exposed:the two stored procedures and the UDF shown in Listing 1. WEBMETHOD requiresthe name of the exposed method as its first argument. As you can see in thecode, it does not have to match the name of the actual stored procedure or UDFthat it is exposing. The next argument of WEBMETHOD is the fully qualified NAMEof the object you are exposing.
TheBATCHES argument specifies whether or not the Web service supports ad hocqueries. WSDL indicates how the WSDL document generation will occur; DEFAULTindicates that the WSDL document will be created automatically. SQL Server 2005allows you to implement custom WSDL generators using the CLR-based abilities ofSQL Server 2005 (see Books Online for more details). DATABASE is the name ofthe database that will be accessed from the endpoint (endpoints are defined atthe server level). And finally, NAMESPACE is, well, the namespace for theendpoint.
Youhave now officially created a Web service using SQL Server 2005.Congratulations.
The Client toConsume Your Web Service
Consuminga Web service created in SQL Server 2005 as an HTTP Endpoint is similar toconsuming a Web service created in C#. That's the beauty of Web services andSOAP. Begin a new Windows application in Visual Studio.NET and add thefollowing controls to the form (ControlType: name):
Button: btnExecSP
DataGridView: dgvProduct
ListBox: lstProducts
PictureBox: picProduct
Youwill need to add the Web reference before writing any code. Again, you canbrowse tohttp://server/awproducts?wsdl to create the Web reference. Keep inmind that your Web reference object will be named server_AWProducts (my machineis named win2k301 so I browse tohttp://win2k301/awproducts?wsdl, and my Web reference object iswin2k301_AWProducts). You will have to tweak the code to the Web reference namethat you create.
Oncethe Web reference is in place, you can double-click the Button control and addthe code in Listing 2.
Listing 2
private void BtnExecSP_Click(System.Object sender, System.EventArgs e){ win2k301_AWProducts.SQLEP_AWProducts proxy = new win2k301_AWProducts.SQLEP_AWProducts(); proxy.Credentials = System.Net.CredentialCache.DefaultCredentials; object[] products = proxy.ProductList(); System.Data.DataSet resultDS; if (products[0].ToString() == "System.Data.DataSet") { resultDS = (System.Data.DataSet)products[0]; DataTable dt = resultDS.Tables[0]; lstProducts.DataSource = dt; lstProducts.DisplayMember = "Name"; lstProducts.ValueMember = "ProductID"; }}
Thefirst thing you will notice is that the Web method returns an object array. BecauseSQL Server can actually send back not only result sets, but also messages,errors, and other types (see Table 2 for more details), you should validatethat you received what you expected. Listing 2 does a check by using theToString method on the first element in the returned object array and comparingit to "System.Data.DataSet". If true, you know you can safely castthe object to a DataSet type and work with it from there.
Table 2: ObjectReturn Types
Item from SQL Server
Corresponding .NET Object
Results of SELECT statement
System.Data.DataSet
Results of a SELECT statement with FOR XML
System.Xml.XmlElement
Raised error
SqlMessage (from WSDL)
Message
SqlMessage (from WSDL)
Output parameter
SqlParameter (from WSDL)
Rows affected
SqlRowCount (from WSDL)
RETURN statement value
System.Int32
Nowdouble-click the ListBox control and add the following code:
private void lstProducts_SelectedIndexChanged(object sender, System.EventArgs e){ Int32 i = 0; try {i = System.Convert.ToInt32(lstProducts.SelectedValue.ToString());} catch {return;} //prevents issue when loading listbox object e1; win2k301_AWProducts.SqlMessage errorMessage; System.Data.DataSet resultDS; win2k301_AWProducts.SQLEP_AWProducts proxy = new win2k301_AWProducts.SQLEP_AWProducts(); proxy.Credentials = System.Net.CredentialCache.DefaultCredentials; object[] products = proxy.ProductStockInfo(i); e1 = products[0]; switch (e1.ToString()) { case "System.Data.DataSet": resultDS = (System.Data.DataSet)products[0]; DataTable dt = resultDS.Tables[0]; dgvProduct.DataSource = dt; dgvProduct.Refresh(); break; case "Yukon_Http_v1.win2k301_AWProducts.SqlMessage": errorMessage = (win2k301_AWProducts.SqlMessage)products[0]; MessageBox.Show("Error fetching product", "'" + errorMessage.Message + " occurred at '" + errorMessage.Source + "'"); return; } Byte[] photo = proxy.ProductPhoto(i); picProduct.Image = new Bitmap(new MemoryStream(photo));}
Youshould pay attention to a couple of details here. First, this code snippet checksfor another type of returned object: SqlMessage. Notice the type name (from theToString method) is Yukon_Http_v1.server_virtualDirectory.SqlMessage.This type is defined in the WSDL document and is unique to each Web service. Thefollowing listing shows a snippet from the WSDL of the SqlMessage complexType:
Thelast tidbit for this discussion is the Byte array that is returned from theUDF. Because this UDF returned varbinary data, its XSD type isxsd:base64Binary. Thus, it is a Byte array in .NET. SQL Server's int data typeis the XSD type xsd:int and Int32 in .NET. Books Online has more informationabout the mapping of types.
Easy WebService Creation
HTTPEndpoints provide a way to create various interfaces into SQL Server, includingfeatures such as Service Broker, database mirroring, and of course, Webservices. It makes the creation of a Web service (that exposes SQL Server data)a snap for experienced programmers and database administrators alike. Enjoy!
