With each new SQL Server release, it becomes increasingly clear that the product is
no longer just a database. SQL Server is a powerful application platform, loaded with capabilities to enhance and complement its traditional database features. Among
other benefits, this ongoing development of the product greatly decreases the need
to rely on external or third-party applications to create robust, feature-rich enterprise
database solutions. The new Native XML Web Services feature in SQL Server 2005
removes dependency on IIS to expose standards-based, secure Web services.
The ability to serve Web services from SQL Server isn't entirely new. The feature is
provided in SQL Server 2000 through the Microsoft SQL Server 2000 Web Services
Toolkit and SQLXML. However, the XML Web services implementation in SQL Server
2005 greatly enhances the feature while making it more efficient, more secure, and easier
to manage. And the feature is no longer dependent on Microsoft IIS. The Native XML
Web Services feature supports many Web services standards such as SOAP 1.1 and 1.2
and standard Web Services Definition Language (WSDL). In this article, I explain how
the Native XML Web Services feature works, how to implement it, and how to code
client applications. I also cover security, best practices, and recommendations for using the
feature. You can download the project files for the examples in this article by clicking the Download the Code link.
Native XML Web Services Implementation
Web service requests communicate with SQL Server 2005
by using the new protocol SOAP over HTTP. In this protocol, HTTP calls are
intercepted by a listener driver called http.sys. This kernel-mode component processes HTTP requests and routes them to applications that have registered a
particular URL path with http.sys. If the URL you're requesting has been registered, the HTTP handler sends the request to SQL Server through the http .sys API. Thus, SQL Server become another
consumer for HTTP requests, removing
the need for using IIS with Web services. A
restriction exists, though: http.sys is currently
implemented only on Windows Server 2003
and Windows XP Service Pack 2 (SP2), so if
you consider using Native XML Web Services, your SQL Server has to be installed on
one of these OSs.
You create Web services by establishing
one or more SOAP endpoints on the server.
(SQL Server 2005 has several types of endpoints, such as the Database Mirroring or
Service Broker endpoints.) You could view a
SOAP endpoint as an equivalent of the WebService class in .NET; it's a point of contact
for client applications. In each endpoint, you
expose one or more stored procedures or
scalar user-defined functions (UDFs) as Web
methods. Although it's not recommended,
you can configure the endpoint to allow
ad hoc execution of T-SQL batches; but
because of its security risks, this feature is off
by default and use of it is highly discouraged.
Surprisingly, no UI tool exists for creating
and managing endpoints. You have to use
your T-SQL skills and master the art of using
the CREATE ENDPOINT, DROP ENDPOINT and ALTER ENDPOINT commands. Listing 1 shows the most common
options for the CREATE ENDPOINT
command when creating a SOAP endpoint.
I'll discuss only these most common options;
I won't discuss options for endpoints other
than the SOAP type.
You control the state of the endpoint
by using the STARTED, STOPPED, or
DISABLED setting for the STATE option,
as Listing 1 shows. When the endpoint is stopped, it still responds to requests but
returns an error. The stopped state can be
useful for minimizing the timeframe of
exposure. For example, you can run a couple
of scheduled jobs and start an endpoint
before scheduled data transfers, then stop
it when the transfers are finished. Disabling
the endpoint turns off all functionality.
The next few options in Listing 1 are HTTP related. The SITE and PATH
options determine the Web service URL.
The SITE setting configures the first part
of the URL after "http." You can define a
specific Web address (using computer name,
localhost, or an IP address), or you can use
one of the wildcards. The asterisk (*) wildcard lets you use all possible host names that
haven't been explicitly registered with http.
sys by other applications. The plus sign (+)
wildcard enables the use of any host name.
The best option is to use the * because it
prevents potential conflicts with other registered URLs while giving you the flexibility
of using different address formats. Here's
what a sample Web service URL looks like
after setting a site to myserver and path to
/sql/mysqlendpoint:
http://myserver/sql/mysqlendpoint
The two other important HTTP endpoint options are PORTS and AUTHENTICATION. You use the PORTS option to
configure whether the Web service will work
with HTTP, HTTPS, or both. For security
reasons, Microsoft highly recommends that
you require SSL if your Web service will be
called from outside of your network. The
AUTHENTICATION option determines
the type of HTTP authentication, which can be one
several types: Basic, Digest,
NTLM, Kerberos, or Integrated. These options are
similar to authentication
that IIS uses, with one
major difference: SQL
Server doesn't allow the
anonymous option because
of the sensitive nature of
accessing data over a Web
service. Another difference
is that you'll need Basic authentication if your server is running
mixed authentication and you want to pass
SQL Server credentials. You have to use valid
Windows credentials to be authenticated,
either in a domain or locally. Also, because
Basic authentication sends credentials in clear
text, this option is supported only when the
endpoint is configured to require SSL. If you
try to use the combination of AUTHENTICATION = (BASIC, …) and PORTS =
(CLEAR, …), you'll get the following error:
The ‘CLEAR' and ‘BASIC' options are not
allowed on the same statement. You'll have to set
up the port to allow connections only over
HTTPS by specifying PORTS = (SSL).
Now, let's look at a few SOAP-specific
options for creating an endpoint. You use
these options to configure what database
objects you want to expose as Web services,
what type of .NET objects will be used
as return types, what type of SQL Server
authentication you want to use, the WSDL
generation type, and whether the endpoint
should support T-SQL batches.
The most important part of the SOAP
section in Listing 1 is one or more WEBMETHOD settings. This is the place where
you expose your stored procedures and
scalar UDFs as public Web methods. The
WEBMETHOD='method_alias' statement
defines the public name for your Web
method. This is the name that client applications will use. The NAME='database.owner.name' setting maps that public function to
the database object you want to expose. The
public alias doesn't have to match the internal
object name. In fact, SQL Server experts
recommend that you don't use the same
name so that you hide inner database details
as much as possible from potential hackers.
After defining one or more WEBMETHOD sections, you have a few more
options to set. Native XML Web Services
support both Mixed and Windows-only
authentication. You configure this option by
setting LOGIN_TYPE to WINDOWS or
MIXED (WINDOWS is the default). Note
that if you decide to use Mixed authentication, you'll have to set up the port to allow
only HTTPS access, as I mentioned earlier
when I explained using Basic HTTP authentication. Again, the same reason applies—we
don't want unencrypted network packets transporting usernames and passwords in
clear text. Also note that you'll need to use
Basic HTTP authentication with Mixed
SQL authentication because the other security types won't be translatable against the
SQL Server authentication engine. If you set
BATCHES=ENABLED (the default is disabled), SQL Server will add a method called
sqlbatch() to the endpoint. This method
takes two parameters: a string containing
your T-SQL batch and an optional array of
SqlParameter objects. As I already mentioned,
you should enable this option only when
absolutely necessary, and you need to be
aware of security implications.
You have three options for WSDL generation. You can specify DEFAULT to generate standard WSDL, you can use NONE
if you don't want to expose your WSDL, or
you can set the option to point to a stored
procedure that uses the StoredProcedureName setting, then generate your own
custom WSDL in that stored procedure.
Using this option gives you more flexibility
for supporting non-Microsoft Web services,
development environments, and toolkits
because you can tweak the WSDL to support whatever format they require.
Once you've created an endpoint, you
can retrieve the WSDL from the endpoint by
appending ?wsdl to the Web service URL:
http://servername/endpointname?wsdl
The WSDL you get back is considered
"default WSDL," and it supports the new
SQL Server 2005 data types. If you're using
Visual Studio 2003 or another development environment, you can request "simple
WSDL" by using this URL format:
http://servername/endpointname?wsdlsimple
The simple WSDL uses primitive XSD
data types, thus providing better backward
compatibility.