Native XML Web Services Support

After SQL Server 2000 shipped, Microsoft released SQLXML web releases to update and enhance the XML support in SQL Server 2000. SQLXML release 3.0 introduced support for SOAP web services, allowing stored procedure and user-defined functions to be invoked over HTTP as web service methods. The similar XML web services support is now built into SQL Server 2005.

SQLXML provided the web services support via an Internet Services API (ISAPI) DLL running under Microsoft's IIS web server. The fact that the ability to expose stored procedures and functions as web service methods is natively built into SQL Server 2005 eliminates the dependency on IIS and also means there is no need to install SQLXML to use the web services functionality. Shortly you'll learn about the native HTTP SOAP support introduced in SQL Server 2005. But first, here is a quick introduction to SOAP, web services, and WSDL.

Note

Native HTTP SOAP support is not a complete replacement for SQLXMLonly for the web services support. SQLXML contains a lot more functionality, including support for URL queries, templates, updategrams, and bulk loading, which is available only via SQLXML.

Introduction to SOAP, Web Services, and WSDL

XML web services is a technology based on HTTP, SOAP, and XML that allows one application on one platform to invoke a method in another application, possibly running on a totally different platform, over the Internet.

The most common application of web services is application-to-application integration over the Internet. The client application posts an XML package, called a SOAP request envelope, over HTTP to a web server where the web service is located. On the server side, the web service method is invoked, and it returns another XML package, called a SOAP response envelope, which is received and parsed by the client to see the method execution results. In case of an error, the web service method returns a SOAP fault message.

Web services are platform and language independent. This means you can develop web services by using any programming language, deploy them on any platform, and then invoke a web API from any platform, using any programming language. As long as the client can generate and post SOAP request XML text, the server can parse the posted XML and reply back with the SOAP response XML text. Finally, the client can parse and process that response XML. This is all you need in order to implement and utilize XML web services.

Web Services Building Blocks

The four primary building blocks of today's XML web services are XML, SOAP, HTTP, and WSDL.

XML is the key to application interoperability and cross-platform integration. Its flexibility (XML defines the syntax, and it is a meta-language that can be used to create other languages), extensibility (XML does not have a fixed set of tags), and portability (XML is text) makes XML a perfect choice for sending and receiving messages over the Internet. Web services use XML as the marshaling format.

The W3C created the SOAP standard, which defines the format of messages sent to implement web services. SOAP was originally an acronym for Simple Object Access Protocol, but now this messaging format specification is no longer an acronym but is simply called SOAP. SOAP uses XML syntax to define the request and response payloads. In other words, the SOAP specification defines how the request and response XML payloads should look, how the web service should report errors, how to send additional information via SOAP headers, and so on. Standardizing on the web services request and response payloads enables you to write web services by using technology from one vendor and consume web services by using technology from a different vendor.

Here is what the SOAP request payload, including the SOAP-specific HTTP headers and the request envelope, looks like:

POST /StockQuote HTTP/1.1 Host: www.stockquoteserver.com Content-Type: text/xml; charset="utf-8" Content-Length: nnnn SOAPAction: "Some-URI" <SOAP-ENV:Envelope   xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"   SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">    <SOAP-ENV:Body>        <m:GetLastTradePrice xmlns:m="Some-URI">            <symbol>DIS</symbol>        </m:GetLastTradePrice>    </SOAP-ENV:Body> </SOAP-ENV:Envelope>

And here is what the SOAP response payload, including the SOAP-specific HTTP headers and the response envelope, looks like:

HTTP/1.1 200 OK Content-Type: text/xml; charset="utf-8" Content-Length: nnnn <SOAP-ENV:Envelope   xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"   SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"/>    <SOAP-ENV:Body>        <m:GetLastTradePriceResponse xmlns:m="Some-URI">            <Price>34.5</Price>        </m:GetLastTradePriceResponse>    </SOAP-ENV:Body> </SOAP-ENV:Envelope>

The SOAP request and response can be sent over any protocol (HTTP, SMTP, and so on). As an analogy, if you have a package that you want to send somewhere, you can ship it by using FedEx, UPS, USPS, or another carrier. The most commonly used transport protocol for today's web services, and the only one supported by SQL Server 2005, is HTTP. The native web services support in SQL Server 2005 is available on only two platforms: Windows Server 2003 and Windows XP SP2. This is because only on these two platforms is the HTTP stack part of the operating system kernel, which is used by SQL Server 2005 to provide HTTP SOAP support without requiring IIS. As a matter of fact, having the HTTP stack (http.sys) in the operating system kernel allows faster execution of HTTP requests. Hence, the native HTTP SOAP support in SQL Server 2005 performs better than ISAPI and IIS-based SQLXML. Prior to HTTP SOAP support in SQL Server 2005, the only network protocol that could be used to access SQL Server was Tabular Data Stream (TDS). The HTTP SOAP support now enables HTTP as an alternate protocol over which SQL Server can be accessed from any platform, without installation of any client or SQL Server network library components. For instance, you can now access SQL Server 2005 over HTTP from a Perl client running on a Unix machine or a C++ or .NET application running on a Pocket PC or smart phone, without requiring any client components such as MDAC.

The fourth and final primary building block for web services is Web Services Description Language (WSDL). WSDL, like SOAP and XML, is a specification defined by the W3C (see www.w3.org/TR/wsdl). WSDL is essentially an XML-based format that describes the complete set of interfaces exposed by a web service. WSDL defines the set of operations and messages that can be sent to and received from a given web service. A WSDL document serves as a contract between web services consumers and the server. A WSDL document describes what functionality a web service offers, how it communicates, and where to find it. In summary, WSDL provides the information necessary for a client to interact with a web service. SQL Server 2005 can dynamically auto-generate the WSDL, or you can write a stored procedure that spits out a custom WSDL document, or you can disable availability of a WSDL document for a web service created by using native HTTP SOAP support.

Note

Remember that the native HTTP SOAP support in SQL Server 2005 is available only on Windows Server 2003 and Windows XP SP2.

Creating HTTP Endpoints

To have SQL Server listen on HTTP for SOAP requests, the first step is to create an HTTP endpoint by using the CREATE ENDPOINT T-SQL statement. The CREATE ENDPOINT statement allows you to create a new web service and, optionally, to define the methods that the endpoint exposes. The ALTER ENDPOINT statement can be used to change the web service settings and to add or remove web methods.

When you create an HTTP endpoint, you have to provide a unique URL, which may include a port number, that SQL Server uses to listen for incoming SOAP requests. When requests are submitted to this URL, the http.sys kernel HTTP stack routes the posted message to the SQL Server endpoint associated with the URL. SQL Server then executes the stored procedure or function, depending on the web method referred in the posted SOAP request envelope, serializes the stored procedure or function results as XML, and replies with the SOAP response envelope.

Although not recommended for security reasons, SQL Server 2005 does allow you to execute ad hoc T-SQL batches over HTTP. You can use the BATCHES=ENABLED option with the CREATE ENDPOINT statement to enable execution of ad hoc T-SQL batches on the endpoint. When this option is enabled, the web service adds a method called sqlbatch in the sql namespace, which clients can invoke to execute ad hoc batches.

CREATE ENDPOINT is a generic statement that is used to create HTTP SOAP endpoints, or to create a TCP-based endpoint to accept T-SQL requests, or to create an endpoint for service broker or database mirroring functionality. With this statement, you provide the name of the endpoint, the authorization details, and a collection of other options, depending on the type of endpoint being created.

Note

The CREATE ENDPOINT statement cannot be executed within the scope of a user transaction. That is, if you have started a transaction by using the BEGIN TRANSACTION statement, you cannot execute CREATE ENDPOINT in that session unless you commit or roll back the transaction.

The following is an example of using a CREATE ENDPOINT statement to create an HTTP SOAP web service that exposes the following stored procedure as a web service method:

USE AdventureWorks; GO IF OBJECT_ID('dbo.GetEmployees') IS NOT NULL    DROP PROCEDURE dbo.GetEmployees; GO CREATE PROCEDURE dbo.GetEmployees AS BEGIN    SELECT c.FirstName, c.LastName, c.EmailAddress, c.Phone,           e.NationalIDNumber, e.LoginID, e.Title, e.BirthDate,           e.MaritalStatus, e.Gender, e.HireDate    FROM HumanResources.Employee e JOIN Person.Contact c    ON e.ContactID = c.ContactID; END;

Let's now use the CREATE ENDPOINT statement to expose the preceding stored procedure as a web service method that can be invoked over HTTP to get a list of employees in the AdventureWorks sample database:

IF EXISTS (SELECT endpoint_id FROM sys.endpoints WHERE name = 'AWEmployees')    DROP ENDPOINT AWEmployees; GO CREATE ENDPOINT AWEmployees    STATE = STARTED    AS HTTP       ( AUTHENTICATION = (INTEGRATED),         PATH = '/SQLWebSvcs/AW',         PORTS = (CLEAR)       )    FOR SOAP       (  WEBMETHOD 'GetEmployees'          (  NAME = 'AdventureWorks.dbo.GetEmployees'),          WSDL = DEFAULT,          DATABASE = 'AdventureWorks'       ); GO

This batch first uses the sys.endpoints catalog view to check whether the endpoint named AWEmployees already exists. If it does, the batch drops the endpoint by using the DROP ENDPOINT DDL statement. Next, the CREATE ENDPOINT statement defines an HTTP endpoint and a web method that maps to the GetEmployees stored procedure created earlier.

Caution

If you are executing the preceding T-SQL statements on Windows XP SP2 and have IIS running, you might get error 0x80070020 and have CREATE ENDPOINT fail. In this case, either you can stop IIS or use a different port to create the HTTP SOAP endpoint. If you have an HTTP SOAP endpoint on port 80 (that is, with PORTS = (CLEAR)), and if IIS is Native XML Web Services Support 327 also listening on the same port, starting IIS fails. You have to either drop the HTTP SOAP endpoint or use a different port for the endpoint. Note that this problem does not occur on Windows Server 2003 because IIS 6.0 on that platform is http.sys based, whereas IIS 5.1 on Windows XP SP2 is not http.sys based.

The web service is available at http://localhost/SQLWebSvcs/AW. After you successfully create the HTTP SOAP endpoint by using the preceding statement, you can start Internet Explorer and type http://localhost/SQLWebSvcs/AW?wsdl in the address bar. Internet Explorer should return an XML document, the WSDL for the web service, and it should contain one web method called GetEmployees. To consume this web service, you can try using Visual Studio .NET to add a web reference to http://localhost/SQLWebSvcs/AW?wsdl, which creates the proxy class for the web service, which you can use to invoke web service methods. The code download for this book includes a C# client application that uses the web service created in this section.

Administering HTTP SOAP Endpoints

Following the "secure by default" principle, SQL Server 2005 does not include any HTTP SOAP endpoint out of the box. Members of the sysadmin role and the users who have CREATE ENDPOINT permissions can create HTTP SOAP endpoints. The endpoints are stopped by default, and the STATE = STARTED options can be used with CREATE ENDPOINT or ALTER ENDPOINT to start HTTP SOAP access.

SQL Server 2005 includes Secure Sockets Layer (SSL) support to accept incoming requests and send responses on a secure (https://) channel.

To control access to the endpoint, the AUTHENTICATION option can have one or more of five values: BASIC, DIGEST, INTEGRATED, NTLM, or KERBEROS. BASIC authentication mode requires SSL and consists of an authentication header containing the Base 64encoded username and password, separated by a colon. In DIGEST authentication mode, the username and password are hashed using a one-way hashing algorithm (MD5) before the request is sent to the server. The server has access to either the raw password or a stored MD5 hash created at the time the password was set. It can then compare the stored calculated value to the one provided by the client. This way, the client can prove that it knows the password without actually giving it to the server. In INTEGRATED authentication mode, the server first tries to authenticate by using Kerberos, and if it is not supported by the client, or if negotiation is not possible, authentication falls back to NTLM. NTLM is the authentication mechanism supported by Windows 95, Windows 98, and Windows NT 4.0 (client and server). This authentication mechanism is a challenge-response protocol that offers stronger authentication than either basic or digest. NTLM is implemented in Windows 2000 and later versions by a Security Support Provider Interface (SSPI). Kerberos authentication is an Internet standard authentication mechanism. Kerberos authentication is supported in Windows 2000 and later versions by an SSPI. After a user is authorized, the user can be routed to a specific database by using the DATABASE= parameter in the CREATE ENDPOINT statement.

An endpoint is a server-level securable. The owner of an endpoint or a sysadmin role member can use GRANT, REVOKE, or DENY for the ALTER, EXECUTE, CONNECT, TAKE OWNERSHIP, and VIEW DEFINITION endpoint permissions regarding a specified principal.

Table 10.1 lists the four catalog views that you can access to view HTTP SOAP endpoint metadata.

Table 10.1. HTTP SOAP-Specific Catalog Views

Catalog View	Description
sys.endpoints	This table contains one row per endpoint. The protocol and protocol_desc columns indicate what type (TCP, VIA, HTTP, and so on) of endpoint it is, and the state column indicates whether the endpoint is started, stopped, or disabled. The principal_id column indicates the endpoint owner.
sys.http_endpoints	This catalog view lists the endpoints that use HTTP. In includes columns to show the site, URL, port, authentication method, and compression setting.
sys.soap_endpoints	This catalog view contains one row for each endpoint in the server that is defined to carry a SOAP-type payload. It shows the name of the stored procedure used to generate the WSDL document, the default namespace, the default database for the endpoint, and other settings, such as the session timeout.
sys.endpoint_webmethods	This catalog view contains a row-per-SOAP-method defined on a SOAP-enabled HTTP endpoint. It lists the web method name, the object (stored procedure or function) that the web method maps to, and the result format.

SQL Server:General Statistics (or MSSQL$InstanceName:General Statistics, for a named SQL Server 2005 instance) contains several Performance Monitor counters that you can use to monitor things such as HTTP authenticated requests, SOAP batch SQL requests, SOAP method invocations, SOAP WSDL requests, failed SOAP requests, execution of SOAP requests, and successful SOAP requests.