In-Depth

Create Interoperable Native Web Services

Yukon lets you take advantage of the new kernel-mode Http.sys listener and eliminates the need for IIS to process ASMX files or SQLXML 3.0 templates that implement Web services.

SQL Server 2005 Developer Edition and higher add native XML Web services as a new feature that enables DBAs and .NET database developers to expose stored procedures, user-defined functions, and ad-hoc batch queries as SOAP 1.x Web methods. Native Web services substitute SOAP over HTTP endpoints to Windows' Http.sys kernel-mode HTTP listener for Internet Information Services' (IIS) virtual directories. SQL Server 2005 Express Edition doesn't support HTTP endpoints, so you must license the Workgroup Edition or better to enable these features for production. Only Windows XP Service Pack 2 (SP2) and Windows 2003 Server install Http.sys; thus, you can't deploy native Web services to Windows 2000 servers. On a more positive note, native Web services improve data security by eliminating the need for access to TCP port 1433, preventing anonymous connections to Web services and requiring Secure Sockets Layer (SSL) to encrypt clear-text login names and passwords.

The watchwords of SQL Server 2005's native Web services are interoperability and simplicity. The SQL Server team's design goal was twofold: Provide cross-platform data access that delivers parity with the Windows-only Tabular Data Stream (TDS) protocol, and move the SOAP stack from the IIS middle tier to the database engine's process. Replacing TDS with SOAP messages eliminates the requirement for JDBC, ODBC, or MDAC stacks on the client. Integrating the SOAP stack in the database engine's process creates a single data-access component for heterogeneous clients. You also can take advantage of C# or VB CLR stored procedures to customize the service's WSDL document for use by non-Microsoft SOAP toolkits, such as Borland's JBuilder 9.0 or webMethods' Glue 5.0.1. In this article, I'll show you how to create SOAP over HTTP endpoints with T-SQL batches, consume native Web services with a Windows client, alter the contents of auto-generated WSDL documents, and compare Web service performance with alternative SQL Server data-access methods. The VS 2005 NativeWebServices.sln VB.NET demonstration project automates most of these operations for the Northwind sample database's stored procedures (download the project here).

Native Web services have a feature set that's similar to the soap-typed virtual directory you create with SQLXML 3.0's graphical IIS Virtual Directory Management utility or its object model. SQLXML 3.0 lets you execute SQL Server 2000, MSDE, or any SQL Server 2005 edition's parameterized stored procedures to retrieve and update data, or submit parameter values to T-SQL template queries. SQLXML and IIS 5+ manage default database, security, and virtual directory assignment, and they support anonymous, integrated Windows, basic, or digest authentication. You can designate a single Windows user account or a login ID and password for all SQLXML 3.0 clients' database access. Typing http://ServerName/DatabaseVDir/SoapVName?wsdl in VS 200x's Add Web Reference dialog's URL box generates the Web service proxy class from the dynamic WSDL document. Most other vendors' Web service client toolkits can't create proxy classes directly from SQLXML 3.0 WSDL documents.

SQL Server 2005's native Web services enable declarative Web service deployment and offer greater implementation flexibility than SQLXML 3.0. You add an HTTP endpoint for SOAP data access with a two-part T-SQL declaration. The first CREATE ENDPOINT statement part supplies HTTP transport parameters:

CREATE ENDPOINT NorthwindDS 
[AUTHORIZATION sa ]
STATE = STARTED 
AS HTTP (PATH = '/wsnwindds', 
   AUTHENTICATION = (INTEGRATED), 
   PORTS = (CLEAR) [, 
   SITE = 'localhost'][, 
   CLEAR_PORT = 80][,
   SSL_PORT = 443][,
   COMPRESSION = ENABLED]) 

The optional AUTHORIZATION LoginID argument specifies the ENDPOINT object's owner; STATE = STARTED overrides the default STATE = STOPPED instruction. PATH = '/wsnwindds' extends the optional SITE parameter value to provide the Web service address URL: http://localhost/wsnwindds, for this example. AUTHENTICATION = ({INTEGRATED | BASIC | DIGEST | NTLM | KERBEROS}) lets you specify the authentication method. You must specify PORTS = (SSL) to encrypt transport of clear-text BASIC authentication credentials and set up SSL encryption with a server authentication certificate. Endpoints support WS-Security authentication headers, but not digital signatures or message-level encryption. Specifying CLEAR_PORT and SSL_PORT port numbers is optional. COMPRESSION = ENABLED forces gzip SOAP response message compression if the client's HTTP header includes gzip as an accept-encoding argument value.

The FOR SOAP clause specifies a WEBMETHOD expression for each T-SQL or CLR stored procedure or user-defined function (UDF) that's exposed by the service, followed by a set of optional parameters that are specific to SOAP operations:

FOR SOAP (
   [WEBMETHOD ['namespace'.]
      'TenMostExpensiveProducts' 
      (NAME = 'Northwind.dbo.[Ten Most 
         Expensive Products]'[, 
      FORMAT = ROWSETS_ONLY][, 
      SCHEMA = STANDARD]), 
      ...]
   [BATCHES = ENABLED][, 
   WSDL = DEFAULT][, 
   SESSIONS = DISABLED][, 
   SESSION_TIMEOUT = 300][, 
   LOGIN_TYPE = WINDOWS][, 
   DATABASE = 'Northwind'][, 
   NAMESPACE = 
      'http://oakleaf.ws/webservices/northwindds'][, 
   SCHEMA = STANDARD][, 
   CHARACTER_SET = XML][, 
   HEADER_LIMIT = 4096]); 

WEBMETHOD expressions require a valid SOAP operation name; a quoted namespace prefix is optional. The required NAME argument value is the three-part, fully qualified object name of the method's associated stored procedure or UDF. Combining the optional FORMAT = ROWSETS_ONLY and SCHEMA = STANDARD arguments returns a diffgram-formatted DataSet with an inline XML schema for VS 2005 Web service proxies. This VB.NET procedure fills a DataGridView control with rows delivered from a Web method that's created from the Northwind sample database's Ten Most Expensive Products stored procedure:

Private Sub SampleDataSetProxyCode()
   'Assumes a proxy class named WSNwindDS
   'and a SOAP endpoint named NorthwindDS 
   'that delivers a DataSet
   Dim wsNwindDS As New 
      WSNwindDS.NorthwindDS
   Dim dsNwind As DataSet = 
      wsNwindDS.TenMostExpensiveProducts
   'Display the DataSet in a DataGridView
   With dgvNwind
      .DataSource = dsNwind
      .DataMember = 
         dsNwind.Tables(0).TableName
   End With
End Sub

Setting FORMAT = ALL_RESULTS (the default value) returns an SqlResultStream XML element, which can contain SqlRowSet, SqlRowCount, SqlMessage, and SqlTransaction child elements. Specifying SCHEMA = DEFAULT substitutes the value specified by the endpoint's (second) SCHEMA parameter, which also applies to ad-hoc T-SQL queries. Setting SCHEMA = NONE removes the inline XML schema; most non-Microsoft SOAP toolkits won't process SOAP response messages with inline schemas. Here's a similar example of a VB.NET procedure that displays in a textbox a well-formed XML document of SqlRowsSet nodes:

Private Sub SampleXmlNodeProxyCode()
   'Assumes a proxy class named WSNwindEP
   'and a SOAP endpoint named NorthwindEP that
   'delivers an array of XmlNodes
   Dim wsNwindEP As New 
      WSNwindEP.NorthwindEP
   Dim nodEPAny() As XmlNode = 
      wsNwindEP.TenMostExpensiveProducts.Any
   'Display SqlRowSet1 nodes in a text box
   txtSoapMsg.Text = 
      nodEPAny(0).ChildNodes(0).InnerXml
End Sub

The Any property returns an array of XmlNodes; the first array member represents a <diffgr:diffgram ... > parent group that has a SqlRowSet1 child group. Dropping one level in the hierarchy by applying the ChildNodes(0) property displays <SqlRowSet1> as the root element.

Setting BATCHES = ENABLED lets clients execute ad-hoc T-SQL queries with the built-in SqlBatch Web method, which has required BatchCommands and optional SqlParameters arguments. Unlike SQLXML 3.0 SOAP template Web methods, which require predetermined (constant) T-SQL query text as the content of a <sql:query> element, SqlBatch Web methods send the string value of the <BatchCommands> element as query text to the SQL Server 2005 query processor. Thus, enabling built-in SqlBatch Web methods exposes the database server to potential SQL injection attacks.

WSDL = DEFAULT generates a dynamic WSDL document with complex or simple (xsd:any) data typing. Data typing depends on the service URL that you use to generate the Web service proxy, which I'll discuss shortly. The SESSIONS = {DISABLED | ENABLED} and SESSION_TIMEOUT = intSeconds parameters set up a session for multiple SOAP request/response messages. LOGIN TYPE = {WINDOWS | MIXED} restricts the authentication mode; specifying MIXED requires an SSL port. DATABASE = {'DatabaseName'' | DEFAULT} sets the target database. NAMESPACE = {'Uri' | DEFAULT} substitutes the ?Uri' value for the default http://tempuri.org namespace. CHARACTER_SET = {XML | SQL} determines the handling of XML-invalid characters in resultsets; Books Online states that specifying SQL encodes illegal characters as character references. HEADER_LIMIT = intChars specifies the maximum number of characters in the SOAP request header; the default value is 4,096.

Simplify Service Deployment
You can omit all optional parameters except BATCHES = ENABLED, WSDL = DEFAULT, and DATABASE = 'DatabaseName' to generate an ad-hoc query Web service with a SqlBatch Web method and its WSDL document. If the query executes against the user's assigned default database, you can drop the DATABASE = 'DatabaseName' parameter. This brief CREATE ENDPOINT declaration is the simplest T-SQL construct that can return a SOAP response message as a DataSet (see Figure 1):

CREATE ENDPOINT NorthwindDS 
STATE = STARTED
AS HTTP(
   PATH = '/wsnwindds', 
   AUTHENTICATION = (INTEGRATED), 
   PORTS = (CLEAR))
FOR SOAP(
   BATCHES = ENABLED,
   WSDL = DEFAULT,
   DATABASE = 'Northwind',
   SCHEMA = STANDARD);

This simple endpoint has no WEBMETHODs specified, so the Web service is limited to processing built-in SqlBatch queries. It's unlikely that any DBA would permit such an endpoint in a production database because of potential security breaches.

Setting SCHEMA = STANDARD for Web methods based on SELECT ... FOR XML stored procedures or SqlBatch ad-hoc queries doesn't return inline XML schemas. To include an inline schema, add the XMLSCHEMA directive as in SELECT * FROM Products FOR XML AUTO, ELEMENTS, XMLSCHEMA. However, inline schemas you add to FOR XML AUTO query resultsets don't enable casting the SOAP response message to the DataSet type for .NET Web or Windows clients. If your goal is to create interoperable Web services to be consumed by clients running under other operating systems or written in languages other than VB or C#, the best advice is to avoid schemas in SOAP response messages and to generate simple WSDL documents. The demonstration project's SOAP Messages tab page displays formatted SOAP request and response messages for the most recent Web service request (see Figure 2).

You can modify existing native Web services endpoints by executing ALTER ENDPOINT commands, which can include {ADD | ALTER | DROP} WEBMETHOD clauses. In most cases, you'll find it simpler to delete and re-create the endpoint with a DROP ENDPOINT EndpointName command that's followed by an updated CREATE ENDPOINT ... AS HTTP ... FOR SOAP instruction. The demonstration project's Edit SOAP Endpoint tab page uses this approach for updating the NorthwindDS and NorthwindEP Native Web services that return DataSets and XmlNodes, respectively (see Figure 3). Three system tables—sys.http_endpoints, sys.soap_endpoints, and sys.endpoint_webmethods—let you access most property values of the SOAP endpoints and WEBMETHODS you define. The most useful value is endpoint_id, which you must supply as a parameter value to system stored procedures that generate WSDL documents.

Native Web services offer the capability to customize WSDL documents to accommodate Web service client toolkits other than VS 2005. For example, typing http://ServerName/EndpointPath?wsdl in IE's Address textbox returns the default (complex) WSDL document that assigns SQL Server simple types—such as sqltypes:datetime—to Web service parameters and a complex SQL Server type—sqlresultstream:SqlResultStream—to the response message. The complex (48.5K) WSDL document for the NorthwindEP endpoint accessed from http://localhost/wsnwindep?wsdl includes this schema fragment for the EmployeeSalesByCountry Web method's request parameters and response document:

<xsd:element 
   name="EmployeeSalesByCountry">
   <xsd:complexType>
      <xsd:sequence>
         <xsd:element minOccurs="1" 
maxOccurs="1" name="Beginning_Date" 
type="sqltypes:datetime" nillable="true">
         </xsd:element>
         <xsd:element minOccurs="1" 
maxOccurs="1" name="Ending_Date" 
type="sqltypes:datetime" nillable="true">
         </xsd:element>
      </xsd:sequence>
   </xsd:complexType>
</xsd:element>
<xsd:element 
   name="EmployeeSalesByCountryResponse">
   <xsd:complexType>
      <xsd:sequence>
         <xsd:element minOccurs="1" 
maxOccurs="1" nillable="false" 
name="EmployeeSalesByCountryResult" 
type="sqlresultstream:SqlResultStream">
         </xsd:element>
      </xsd:sequence>
   </xsd:complexType>
</xsd:element>

The http://localhost/sqlxml30/nwindws?wsdl URL generates a sample SQLXML 3.0 WSDL document that contains an almost identical schema fragment for the EmployeeSalesByCountry Web method. The only difference is a pair of erroneous minOccurs="0" attributes for the two datetime parameters. Non-Microsoft SOAP toolkits don't support the highlighted SQL Server–specific type attribute values in native Web services' complex WSDL or SQLXML 3.0 WSDL schemas.

Customize WSDL Schemas for Interoperability
Substituting ?wsdlsimple for ?wsdl in the WSDL document's URL query string replaces proprietary SQL Server–specific types with XML schema simple datatypes and sqlresultstream:SqlResultStream with the XML schema xsd:any datatype wildcard. For example, http://localhost/wsnwindep?wsdlsimple returns this potentially interoperable schema snippet for the EmployeeSalesByCountry Web method from the 43.2K WSDL document:

<xsd:element 
   name="EmployeeSalesByCountry">
   <xsd:complexType>
      <xsd:sequence>
         <xsd:element minOccurs="1" 
maxOccurs="1" name="Beginning_Date" 
type="xsd:string" nillable="true">
         </xsd:element>
         <xsd:element minOccurs="1" 
maxOccurs="1" name="Ending_Date" 
type="xsd:string" nillable="true">
         </xsd:element>
      </xsd:sequence>
   </xsd:complexType>
</xsd:element>
<xsd:element 
   name="EmployeeSalesByCountryResponse">
   <xsd:complexType>
      <xsd:sequence>
         <xsd:element minOccurs="1" 
maxOccurs="1" nillable="false" 
name="EmployeeSalesByCountryResult">
            <xsd:complexType mixed="true">
               <xsd:sequence>
                  <xsd:any 
                   minOccurs="0" maxOccurs="unbounded" 
                   processContents="skip">
                  </xsd:any>
               </xsd:sequence>
            </xsd:complexType>
         </xsd:element>
      </xsd:sequence>
   </xsd:complexType>
</xsd:element>

The highlighted attributes and elements illustrate the differences between complex, native Web service or SQLXML 3.0 WSDL syntax and simple XML schema definitions for a sample Web method. You can identify all syntactical differences between files created from complex (NorthwindEpComplex.wsdl) and simple (NorthwindEpSimple.wsdl) by opening these files from the ...\WebReferences\WSNwindEP folder in the Microsoft XML Diff and Patch 1.0 tool at http://apps.gotdotnet.com/xmltools/xmldiff/. Generating a VS 2005 Web service proxy from the simple WSDL version adds a WSNwindEP data source with members for each Web method, including the built-in SqlBatch method. A member's Any method returns an XmlNode containing the SOAP response message for the parent Web method (see Figure 4).

Two system stored procedures—sp_http_generate_wsdl_defaultcomplexorsimple and sp_http_generate_wsdl_defaultsimpleorcomplex—return complex or simple WSDL documents from native Web services. The simplified syntax to return the WSDL document as a String is:

EXEC 
sp_http_generate_wsdl_defaultcomplexorsimple 
@EndpointID, @IsSSL, @Host, @QueryString, 
@UserAgent

All parameters are nvarchar(256) except @EndpointID (int) and @IsSSL (bit). This SqlCommand.CommandText value returns a simple WSDL document by invoking the SqlCommand.ExecuteScalar method:

EXEC 
sp_http_generate_wsdl_defaultcomplexorsimple 
65586, FALSE, N'localhost', N'wsdlsimple', 
N'NativeWebServices';

Replace N'wsdlsimple' with N'wsdl' to return the default complex WSDL document. The sp_http_generate_wsdl_defaultcomplexorsimple and sp_http_generate_wsdl_defaultsimpleorcomplex procedures return identical WSDL documents for the same set of parameter values.

You can further customize native Web services to accommodate specific SOAP toolkits by adding an SQLCLR stored procedure to modify specific WSDL document elements. Books Online's "Implementing Custom WSDL Support" topic links to three subtopics that describe how to build and use the CustomWSDL class, which contains the GenerateWSDL C# function that maps to the myWSDL SQLCLR stored procedure. Be sure to remove the erroneous // characters that comment the [SqlProcedure] attribute of the June CTP version's GenerateWSDL function before building and deploying the assembly. (The demo project code includes a corrected version of CustomWSDL.cs.)

GenerateWSDL has the same parameter values as the two sp_http_generate_wsdl* stored procedures and calls one of three private functions to make toolkit-specific modifications: UpdateWsdlForVS2003 (Everett), UpdateWsdlForJBuilder, or UpdateWsdlForGLUE. These functions change this complexType definition for SqlRowSet from:

<xsd:complexType name="SqlRowSet">
   <xsd:sequence maxOccurs="unbounded">
      <xsd:element ref="xsd:schema" /> 
      <xsd:any /> 
   </xsd:sequence>
</xsd:complexType>

to (for VS 2003, JBuilder, and GLUE):

<xsd:complexType name="SqlRowSet">
   <xsd:sequence maxOccurs="unbounded">
      <xsd:any maxOccurs="unbounded" minOccurs="0" 
         processContents="lax" /> 
   </xsd:sequence>
</xsd:complexType>

For GLUE only, change this definition, if present:

<xsd:simpleType name="sqlCompareOptionsList">
   <xsd:list 
      itemType="sqltypes:sqlCompareOptionsEnum">
</xsd:simpleType>

to this definition:

<xsd:simpleType name="sqlCompareOptionsList">
   <xsd:restriction base="xsd:string" />
</xsd:simpleType>

Substitute myWSDL for the default WSDL generator procedure by replacing the CREATE ENDPOINT statement's WSDL = DEFAULT attribute with WSDL = 'DatabaseName.dbo.myWSDL' or specify WSDL = 'DatabaseName.dbo.GenerateWSDL', if you use VS 2005's automated SQLCLR deployment feature. To return the appropriate customized WSDL document from the demo project with PATH = '/wsnwindep', use http://localhost/wsnwindep?wsdleverett, http://localhost/wsnwindep?wsdljbuilder, or http://localhost/wsnwindep?wsdlglue as the simple WSDL URL. To return the complex WSDL document, append "extended" to the query string, as in http://localhost/wsnwindep?wsdleverettextended.

Tests with the demo project show that SQL Server 2005's native Web services exhibit performance similar to that of corresponding SQLXML 3.0 Web services and, by inference, VS 2005 ASMX services with IIS 6 running on the database server. You're likely to see a small to moderate performance gain compared with services that have IIS installed on a remote server. The obvious drawback when moving the SOAP stack close to the data on the database server is costly resource consumption. As a general rule, scaling up database servers is more expensive than scaling out IIS 6, especially with the reduced licensing cost of Windows Server 2003 Web Edition. In addition, a clear migration path from native Web services to the Indigo message bus was missing when this article went to press. But if client-side data interoperability and Web service deployment simplicity are on your "must have" list, download the NativeWebServices.sln project and give it a test drive on your VS 2005 or VB Express development system. You might discover that SQL Server 2005's native Web services are right for you.

More Information

- "Overview of Native XML Web Services for Microsoft SQL Server 2005" by Brad Sarsfield and Srik Raghavan (updated June 2005)

- "Usage Scenarios for SQL Server 2005 Native Web Services" by Srik Raghavan (May 2005)

- "Web Services Q and A"

- "XML Support in Microsoft SQL Server 2005" by Shankar Pal, Mark Fussell, and Irwin Dolobowsky (May 2005)

comments powered by Disqus
Most   Popular
Upcoming Events

.NET Insight

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.