Native XML Web Services in SQL Server 2005

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.

The SCHEMA setting determines whether SQL Server returns the XSD schema for the method with the resultset (SCHEMA=STANDARD) or not (SCHEMA=NONE). You don't have to request schema for Web methods mapped to scalar UDFs and stored procedures that use FOR XML. You have to request schema when executing a stored procedure or a T-SQL batch that returns a tabular resultset. If you want schema with the FOR XML type of procedure, you need to specify XMLSCHEMA in the SELECT statement. If you don't request schema when calling an endpoint Web method mapped to a FOR XML stored procedure, you'll still be able to load the resultset into an XmlElement object.

This process is a little different if you want to work with a DataSet object. If you specify SCHEMA=NONE, the Web service returns a dataset, and without a schema, you'll get an error in your .NET code when you try to access a DataTable object in the returned dataset.

The last Web method option I'll mention is FORMAT. The default setting is ALL_RESULTS, for which SQL Server returns an array of objects containing a result set (either a dataset or an XML element) plus a few additional objects such as row count, SQL Server errors and warnings, stored procedure output parameters, and results of PRINT statements. If you don't want all that extra stuff, you can optimize the Web method by specifying FORMAT = ROWSETS_ONLY and you will get back just the result set.

Now, let's create a stored procedure and expose it as a Web method. For my example, I wanted to create something that could be used in the real world. One area in which I expect Native XML Web Services to be used a lot is remote database monitoring. Monitoring SQL Server installations behind firewalls isn't easy. But if you expose some monitoring and management stored procedures as Web services, you should be able to connect much more easily through HTTP, as long as your firewall routes the HTTP traffic to your SQL Server.

Listing 2 shows the code that creates a stored procedure that returns a list of indexes in which fragmentation is greater than the specified parameter. The stored procedure uses the new dynamic management function sys.dm_db_index_physical_stats. (Note that this function is the SQL Server 2005 replacement for the DBCC SHOWCONTIG command.) The next step is to run the code in Listing 3 to create an endpoint. When you run the script, SQL Server creates an endpoint called DBAdministration. This endpoint will have the GetFragmentedIndexes method mapped to a stored procedure of the same name in the AdventureWorks sample database.

Once you create the endpoint, you can start creating a client application in Visual Studio. The first step is to right-click the project and select Add Web Reference. Next, type the URL for WSDL as specified when you created the endpoint. On my computer, the URL is http://rehakr/sql/dba?wsdl, as you can see in Figure 1.

Click Add Reference, and you're ready to start coding. First, you create an instance of the Web service and set credentials. In this case, I use the credentials of the logged-on user:

DBAdministration ws = new     DBAdministration();ws.Credentials = System.    Net.CredentialCache.   DefaultCredentials;

Next, call the Web service. The return object is an array of objects:

object[] results = ws.Get   FragmentedIndexes(int.   Parse(txtFragPercent.   Text));

Because the return object contains many types of objects, you need to loop through the array and find the type of object you're looking for. To get the resultset, we need to search for a DataSet object:

DataSet ds = null;for (int i = 0; i <    results. Length; i++){  object result =      results[i];  if (result.ToString() ==      "System.Data.DataSet")     {      ds = (System.Data.DataSet)     results[i];      break;      }  }

As you can see, you need to do some extra work to get the data from the return object. Because this is the same code you'll have to use in every client application, I recommend that you create a helper object that implements methods such as GetDataSet() or GetXmlElement() to minimize coding the same plumbing over and over. Please consult SQL Server 2005 Books Online (BOL) to get the complete list of possible objects returned by an endpoint. Figure 2 shows a grid displaying the results of calling GetFragmentedIndexes(80).

Security Recommendations and Best Practices

The most basic security advice I can give you is to stick to the defaults as much as you can because the design of Native XML Web Services is heavily security focused. Use the default Windows-only authentication, keep T-SQL batches disabled, use SSL, and utilize Kerberos for HTTP authentication.

Although Native XML Web Services offers new options for data access, you need to be careful about when you consider using them. The most important thing you should know is that the overhead of making a SOAP call is much higher than using ADO.NET. In my own benchmark, I could see that stored procedures with minimum duration time (1ms to 2ms) executed about 15–20 times slower. As the length of execution time increases, the overhead becomes less significant, but my results clearly show that SOAP access is not the best choice on systems that have heavy loads in which you need to process high volumes of short OLTP transactions. SOAP is also not the best choice for returning very large result sets or retrieving binary objects.

Another big concern is scalability. Because an endpoint essentially replaces your middle tier, it can become a bottleneck. Unlike when you use a Web farm with .NET Web services as a middle tier, you can't easily scale out a SOAP endpoint because it's tied to a single instance of SQL Server.

However, it can make sense to use this feature in heterogeneous environments in which you need to expose your data easily to non-Microsoft clients, as long as your SQL Server is expected to handle only a slow to medium load. Native XML Web Services can also be useful if you need Web-service functionality without having to use IIS, if you require zero-footprint for data access on your clients, and if your applications don't require the use of a middle-tier layer. I also already showed another useful scenario, remote database monitoring over HTTP without using IIS. You shouldn't blindly use this feature, but when you take into consideration all the performance, security, and scalability implications, it can be a useful part of your enterprise architecture.