Usage Scenarios for SQL Server 2005 Native Web Services

Microsoft SQL Server 9.0 Technical Articles

Srik Raghavan
Microsoft Corporation

Applies to:
SQL Server 2005
Web services

May 2005

Summary: Get a detailed look at how to set up SQL Server 2005 for Web service access in a heterogeneous environment, and learn more about key scenarios for Web services in SQL Server 2005. (11 printed pages)

Download the associated WebServicePerlScript.exe [ http://download.microsoft.com/download/c/2/1/c21426cd-9d6e-4051-833c-c19b3017033d/webserviceperlscript.exe ] code sample.

Download the associated WebServicePerlScript.exe code sample.

Introduction
Heterogeneous Access
Perl Scripts for Administration and Monitoring
Additional Scenarios
Conclusion

Introduction

In SQL Server 2005, we added support for native XML Web services to the database engine. This feature is designed around well-known standards such as SOAP 1.2, WSDL 1.1, and HTTP. Basing the solution on standards enables interoperability and outreach in the heterogeneous environments common to most enterprises.

The new infrastructure added to SQL Server 2005 greatly facilitates exposing Web services directly out of the server, in that having the native SOAP stack built into the database engine obviates the need for a middle-tier process (such as IIS) for this purpose. It also enables SQL Server to participate as a component in service-oriented architectures, as services provide the glue in these new architectures. Native XML Web services allow you to both expose stored procedures as Web services and execute ad hoc T-SQL statements against the database server. In essence we have created a new access mechanism to SQL Server based on SOAP that provides much the same functionality currently available via the Tabular Data Stream (TDS) proprietary binary protocol.

We begin with a detailed look at how to set up SQL Server 2005 for Web service access in a heterogeneous environment. We will see how to use Perl scripts for database administration, and look briefly at additional scenarios where native Web services can be used.

Heterogeneous Access

Consider an environment where applications running on non-Microsoft operating systems need to connect to SQL Server. Our recommendation for such applications is to use SQL Server authorization (SQL-Auth) to connect to a SQL Server 2005 Web service. Let's take a look at how that works.

The first thing the user would need to do to expose a Web service is to create an endpoint. Look at the Data Definition Language (DDL) statement for creating an endpoint, shown below. It exposes a stored procedure named "GetCustomerInfo" as a Web service.

Note The term WEBMETHOD, though conceptually the same as [WebMethod] in ASP.NET, is otherwise unrelated to ASP.NET.

CREATE  ENDPOINT sql_auth_endpoint
STATE = STARTED
AS HTTP (
   SITE = '*', 
   PATH = '/sql/sql_auth', 
   AUTHENTICATION = (BASIC),
   PORTS=(SSL)   
)    
FOR SOAP (
   WEBMETHOD 'GetCustomerInfo' 
(
            name='AdventureWorks.dbo.GetCustomerInfo', 
            schema=STANDARD
) ,
   LOGIN_TYPE = MIXED,
   WSDL = DEFAULT,
   DATABASE = 'AdventureWorks',
   BATCHES=ENABLED,
   NAMESPACE = 'http://Adventure-Works/Customers/'
 )

In keeping with the "Secure By Design" theme in SQL Server 2005, we do not allow ANONYMOUS access to SQL Server under any circumstances. This means that all connections need to authenticate at the HTTP transport level using one of the supported authentication schemes. BASIC is one of the most common and widely used authentication models, as it is supported by most clients. However, it is also the least secure option, as it requires the passwords to be sent over in clear text. To avoid this problem, we require that the endpoint be enabled for SSL whenever BASIC is chosen as the authentication type. To enable SSL, one would have to execute the following command,

httpcfg set ssl /i IP:Port /h Hash /g Guid

where the Hash is the certificate hash and Guid is a globally unique identifier (GUID) string identifying the entity registering the certificate. The user can obtain the hash value for the certificate by looking up the Thumbprint value in the Certificate. As a best practice, create a single GUID for each instance of SQL Server and use that same GUID for all certificate registrations made by that instance. You can use any tool to come up with this GUID value. Httpcfg.exe ships with Windows support tools.

So in this case it would become:

httpcfg set ssl /i 1.1.1.1:443 /h 4463b7899c499a38812a7bbe7d73f4d31d026b2f /g "{2bb50d9c-7f6a-4d6f-873d-5aee7fb43290}"

where 1.1.1.1 would be replaced with the IP address of the machine hosting SQL Server.

So how does one enable SQL-Auth on an endpoint? This is done by specifying "LOGIN_TYPE=MIXED" in the payload section of the endpoint syntax. By specifying "MIXED", one can authenticate with the SQL Server 2005 Instance using either integrated or SQL authentication. We now enable the SQL credentials to flow as part of the payload (message). In doing so, we have taken care to ensure that the SOAP header that transports the credentials matches the WS-Security Username token. Adhering to the WS-Security standard inherently promotes interoperability; for example, the username token SOAP header can be generated using the Web Services Enhancements 2.0 for Microsoft .NET (WSE) using very few lines of code.

As you can see in the discussion above, there are two levels of authentication:

Transport level
Message level

Let us now drill down into how the two levels of authentication work.

All requests are always authenticated at the transport level. So if a user submits invalid BASIC authentication credentials, the connection fails with a HTTP 401 access denied error. If the user authenticates successfully at the transport level, then we have two options. We can use either the transport credentials or the credentials that came as part of the SOAP message to login to SQL Server. The credentials selected are determined by the presence of SQL-Auth credentials in the SOAP message. If credentials are present in the SOAP message, we will attempt to login to the SQL Server database using the SQL-Auth credentials. If this fails, we return a failure to the user, and we do not fall back to using the BASIC authentication credentials. If there are no credentials in the SOAP message, we will attempt to login to the SQL Server using the transport credentials.

The SOAP message containing the SQL credentials appears as follows:

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Header>
    <Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-
     200401-wss-wssecurity-secext-1.0.xsd" xmlns="http://docs.oasis-
     open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
      <wsse:UsernameToken>
        <wsse:Username>user</wsse:Username>
        <wsse:Password Type="http://docs.oasis-
         open.org/wss/2004/01/oasis-200401-wss-username-token-profile-
         1.0#PasswordText">
              password
        </wsse:Password>
      </wsse:UsernameToken>
    </Security>
  </soap:Header>
  <soap:Body>
    <GetCustomerInfo xmlns="http://Adventure-Works/Customers/">
      <CustomerID>1</CustomerID>
      <OutputParam>Hello World</OutputParam>
    </GetCustomerInfo>
  </soap:Body>
</soap:Envelope>

Specifying an invalid credential in the SOAP message results in the following SOAP fault being returned to the user:

<?xml version="1.0" encoding="utf-8"?>
<SOAP-ENV:Envelope xml:space="preserve" xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:SOAP-
ENV="http://schemas.xmlsoap.org/soap/envelope/" 
xmlns:sql="http://schemas.microsoft.com/sqlserver/2004/SOAP" 
xmlns:sqlsoaptypes="http://schemas.microsoft.com/sqlserver/2004/SOAP/types" 
xmlns:sqlrowcount="http://schemas.microsoft.com/sqlserver/2004/SOAP/types/SqlRowCount" 
xmlns:sqlmessage="http://schemas.microsoft.com/sqlserver/2004/SOAP/types/SqlMessage" 
xmlns:sqlresultstream="http://schemas.microsoft.com/sqlserver/2004/SOAP/types/SqlResultStream" 
xmlns:sqltransaction="http://schemas.microsoft.com/sqlserver/2004/SOAP/types/SqlTransaction" 
xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes">
  <SOAP-ENV:Body>
    <SOAP-ENV:Fault xmlns:sqlsoapfaultcode="http://schemas.microsoft.com/sqlserver/2004/SOAP/SqlSoapFaultCode">
      <faultcode>SOAP-ENV:Client</faultcode>
      <faultstring>
         There was an error in the incoming SOAP request packet:  
         Client, LoginFailure, AccessDenied
      </faultstring>
      <faultactor>http://schemas.microsoft.com/sqlserver/2004/SOAP</faultactor>
      <detail xmlns:SOAP-1_2-ENV="http://www.w3.org/2003/05/soap-envelope">
        <SOAP-1_2-ENV:Code>
          <SOAP-1_2-ENV:Value>SOAP-1_2-ENV:Sender</SOAP-1_2-ENV:Value>
          <SOAP-1_2-ENV:Subcode>
            <SOAP-1_2-ENV:Value>sqlsoapfaultcode:LoginFailure</SOAP-1_2-ENV:Value>
            <SOAP-1_2-ENV:Subcode>
              <SOAP-1_2-ENV:Value>sqlsoapfaultcode:AccessDenied</SOAP-1_2-ENV:Value>
            </SOAP-1_2-ENV:Subcode>
          </SOAP-1_2-ENV:Subcode>
        </SOAP-1_2-ENV:Code>
        <SOAP-1_2-ENV:Reason>
          <SOAP-1_2-ENV:Text xml:lang="en-US">
            There was an error in the incoming SOAP request packet:  
            Sender, LoginFailure, AccessDenied
          </SOAP-1_2-ENV:Text>
        </SOAP-1_2-ENV:Reason>
        <SOAP-1_2-ENV:Node>https://srikr-800.redmond.corp.microsoft.com:443/sql/sql_auth</SOAP-1_2-ENV:Node>
        <SOAP-1_2-ENV:Role>
            http://schemas.microsoft.com/sqlserver/2004/SOAP
        </SOAP-1_2-ENV:Role>
        <SOAP-1_2-ENV:Detail />
      </detail>
    </SOAP-ENV:Fault>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

This solution leverages only HTTP, SOAP, BASIC authentication and SSL, which make it ideal for heterogeneous environments. In the section below we see how one can leverage this in creating Perl scripts to connect directly to SQL Server.

Perl Scripts for Administration and Monitoring

With Web services in SQL Server 2005, one can connect to SQL Server from any platform that has Web service support. To illustrate this interoperability, we will create Perl scripts to connect to SQL Server. Perl is quite commonly used to create scripts to assist in administration and monitoring of the database server.

The example below illustrates how one can create a Perl script to monitor the state of the database. SQL Server 2005 has introduced support for dynamic management views that provide dynamic state information about the running server. In this example, we create a Perl script to monitor the number of active connections to the database by querying a dynamic view named dm_exec_connections.

It is assumed that the machine running this code already has Perl properly installed and configured.

The example here uses the ActiveState 5.8.x Perl package. The script takes advantage of the following packages:

install http://theoryx5.uwinnipeg.ca/ppms/Crypt-SSLeay.ppd
install http://theoryx5.uwinnipeg.ca/ppms/XML-Parser.ppd
install http://theoryx5.uwinnipeg.ca/ppms/libxml-perl.ppd
install http://theoryx5.uwinnipeg.ca/ppms/XML-RegExp.ppd
install http://theoryx5.uwinnipeg.ca/ppms/XML-DOM.ppd

We need SSL in this case because the Perl application is going to use both BASIC auth and SQL-Auth, and the XML packages are required to parse the response and display the results.

Note For the complete script, see the associated download.

The following block of code instantiates the SOAP Lite package. We need to explicitly request for the output to be formatted as XML so that we can parse the response.

my $soap = SOAP::Lite
  -> uri('http://Adventure-Works/Customers/')
  -> proxy('https://srikr-800/sql/sql_auth')
  -> outputxml(1);

Next we need to set up the credentials for this connection. Since we intend to use SQL-Auth, we need to initialize the UsernameToken header as shown below.

# sample Yukon security SOAP header
# <wsse:Security  xmlns:wsse="http://docs.oasis-
open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
#   <wsse:UsernameToken>
#          <wsse:Username>sql_user</wsse:Username>
#       <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01
      /oasis-200401-wss-username-token-profile-1.0#PasswordText">
         foo-bar1
      </wsse:Password>
#   </wsse:UsernameToken>
# </wsse:Security>
my $Username = SOAP::Data->name('Username' => 'AdminUser');
my $Password = SOAP::Data->name('Password' => 'password')
            ->attr({Type => 'http://docs.oasis-open.org/wss/2004/01
            /oasis-200401-wss-username-token-profile-1.0#PasswordText'});
my $UsernameToken = SOAP::Data->name('UsernameToken')
            ->value(\SOAP::Data->value($Username, $Password));
my $security = SOAP::Header->name(Security)
            ->attr({'xmlns' => 'http://docs.oasis-open.org/wss
            /2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd'})
            ->value(\$UsernameToken);

The BASIC authentication credentials are passed in by implementing the following stub:

sub SOAP::Transport::HTTP::Client::get_basic_credentials {
  return 'User' => 'Password';
}

Note It is not a recommended practice to store/reference passwords in the script files. The user should follow standard security guidelines when dealing with passwords.

Next we invoke the Web method. Since we are going to execute a T-SQL batch statement, the code looks like this:

#
# Invoking a sqlbatch to retrieve the number of connections 

$soap -> on_action (sub { return '""';});

$method = SOAP::Data->name('sqlbatch')->attr({xmlns => 
'http://schemas.microsoft.com/sqlserver/2004/SOAP'});
@param = ( SOAP::Data->name(BatchCommands => 'select session_id, 
net_transport, protocol_type from sys.dm_exec_connections'));

Finally we parse the XML response to retrieve the data:

for my $node($doc->getElementsByTagName("row"))
{
  
  print "\n";
  for my $kid ($node->getChildNodes)
  {
    print $kid->getNodeName(); print ":: ";
    for my $gkid ($kid->getChildNodes)
      {
         print $gkid->getNodeValue();  #print the actual values for the columns
      }
    
    print "\t";
  }
 
  print "\n";
}

Running this Perl script generates the following output:

Testing SOAP::Lite client against AdventureWorks Contacts sample web service.
Calling sqlbatch
Server response...

Server response...
session_id:: 54 net_transport:: HTTP    protocol_type:: SOAP    
connection_id::5EC2B4E2-39A6-4FA7-BBDB-144DAED59A41

session_id:: 53 net_transport:: Shared memory   protocol_type:: TSQL    
connection_id:: 5AE50B7D-D919-4FBC-BA42-6069A12F4D30

session_id:: 53 net_transport:: Session protocol_type:: TSQL    
connection_id::05830BE9-F12F-429D-BBAC-E4EEB2C528EF    
parent_connection_id:: 5AE50B7D-D919-4FBC-BA42-6069A12F4D30

The above output indicates that there are two connections to SQL Server: one using the binary protocol, TDS, shows up as protocol_type:: TSQL, and the other connection corresponds to the SOAP/HTTP connection made when running this Perl script.

I would like to draw your attention to the session_id column. This session id matches the spid (Session Process Identifier) in the database engine that is associated with this request. There are two entries with spid equals 53 because one corresponds to the physical connection (net_transport is shared memory), and the other corresponds to the logical session that is layered over the same physical connection. (For more information on multiple active result sets, see Multiple Active Result Sets (MARS) in SQL Server 2005 [ http://msdn2.microsoft.com/en-us/library/ms345109.aspx ] .) This is evidenced by the fact that the parent_connection_id for the logical session matches the physical connection. In the case of TDS, connections and sessions are tied together; in other words, one cannot join an existing session from a different physical connection. SOAP access enables a user to join an existing session by specifying the appropriate sessions header in the request. Using multiple sessions in SOAP is a topic for a different article. Interested readers can read SQL Server 2005 Books Online for more information on how to enable and use sessions.

Additional Scenarios

Let us now look at a few additional scenarios. Most database applications have a lot of data-centric logic built into stored procedures. Native XML Web services take advantage of this investment in stored procedures by making it easy to expose them as Web services. Additionally, native Web services can improve performance because the data access is happening in-proc instead of being shipped to the mid-tier process.

Lookup Services

When SQL Server is hosting data for reference/lookup, a Web service is an ideal mechanism to expose this data. In this scenario, the database acts as a repository for potentially a large amount of data. The Web services leverage the database engines query processing capabilities to obtain the results. The result set in such queries is well defined and in the order of a few kilobytes. Examples of such scenarios include:

product catalogs
location-aware Web services that return area-specific information (weather, traffic) to your users
employee directories for intranets

Report Generation Services

There are many scenarios where the database server hosts data that serves as the basis for reporting. Within an intranet, it is convenient to expose these reports as Web services. One can easily create a T-SQL stored procedure to generate a report and expose it using Web services in SQL Server 2005. You can also easily embed the results of Web services into Office applications such as Excel and InfoPath. Not only does it make it easier for client applications to retrieve data, it also frees the database administrator from the burden of supporting additional infrastructure in order to expose Web services. Users also have the ability to run ad hoc queries and generate reports using the batch access capabilities of native Web services.

Cross Platform Access to User Defined Types

SQL Server 2005 introduces support for user-defined types. With user-defined types (UDTs), you can extend the scalar type system of the database (beyond just defining your own alias for a system type, which has been available in previous releases of SQL Server). One could, for example, define a UDT type called Point that captures the x and y coordinates of a point. Native Web services leverage the serialization framework available in the common language runtime and enable transport of such types as XML. The client platform can then deserialize this XML into objects defined on their platform. This enables a Java client to send and receive UDT instances.

Mobile Scenarios

Any device that can parse XML and submit HTTP requests can now access SQL Server. This, combined with the ability to rejoin an existing session when the connection is dropped, makes it ideal to develop applications for mobile or sporadically connected devices, which in turn, enables anytime, anywhere access to SQL Server.

Asynchronous Services

Native Web services combined with SQL Service Broker (also available with SQL Server 2005) can be used to construct a solution that provides asynchronous services. Consider an order processing workflow. One can expose a SQL Server 2005 Web service that receives an order and responds with immediate acknowledgment that it has received the order. We could then enter the order in a service broker queue for processing. Order fulfillment might require invoking other Web services. When the order is fulfilled we could notify the client using any notification mechanism the client has subscribed to.

Conclusion

Native XML Web services leverage your investment in the database server and enable your database to participate as a service provider. I've shown in detail how you can use this feature to provide access to data hosted in SQL Server in heterogeneous environments and described additional scenarios that are appropriate for native Web services. Native XML Web services promote interoperability and facilitate outreach by enabling a broader range of clients to connect to SQL Server.

Contents