• Article

Setting the Server to Listen for Native XML Web Services Requests

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Setting up an instance of SQL Server as a Web Service that can listen natively for HTTP SOAP requests requires creating an HTTP endpoint (URL namespace) and defining its properties, and the methods that the endpoint exposes.

HTTP endpoints that are created for use with SQL Server can listen and receive requests on the same TCP port (port 80) that is used by Microsoft Internet Information Services (IIS) for World Wide Web service. This can occur because each URL, regardless of whether it is for use with SQL Server or IIS, registers with the Windows Server 2003 operating system for use through the system HTTP listener process (Http.sys).

For example, assume that you created an endpoint with the URL: http://MyServer/My endpoint. This endpoint is then registered with Http.sys. When a SOAP request is sent to this endpoint, the request goes first to the server identified in the URL (MyServer). Upon receiving the request, the Http.sys layer on the server compares the rest of the URL to the registered endpoint and forwards the request directly to the instance of SQL Server and bypasses IIS. Additionally, by using the Internet Services Manager (Inetmgr.exe) console, you can create a virtual directory that is the equivalent of an HTTP endpoint for IIS with a different URL, such as: http://MyServer/MyVDir.

Creating and Managing Endpoints

To create and manage endpoints, use the following Transact-SQL statements:

  • CREATE ENDPOINT

    Use to create endpoints, to define methods for which the client applications can send HTTP SOAP requests to the endpoint, and to set authentication and specify ports.

  • ALTER ENDPOINT

    Use to add, drop, or modify methods or change endpoint options.

  • DROP ENDPOINT

    Use to delete unwanted or obsolete endpoints.

Endpoint Permissions and Authentication

To create, modify, or drop HTTP endpoints you must have permissions. To access endpoints requires authentication. These requirements are described in the following topics:

Programmability to the Web

To expose stored procedures or user-defined functions, see the following topic:

Default Execution Environment

By default, HTTP SOAP requests to Native XML Web Services in SQL Server are executed under the settings listed in the following table.

  • SET ANSI_NULL_DFLT_ON ON
    If the ANSI NULL default option for the database is FALSE, the default nullability of new columns is overridden. For more information, see SET ANSI_NULL_DFLT_ON (Transact-SQL).

  • SET QUOTED_IDENTIFIER ON
    Identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).

  • SET NUMERIC_ROUNDABORT OFF
    Losses of precision do not generate error messages and the result is rounded to the precision of the column or variable storing the result. For more information, see SET NUMERIC_ROUNDABORT (Transact-SQL).

  • SET ARITHABORT ON
    If an overflow or divide-by-zero error occurs during query execution, the query or batch will be terminated. For more information, see SET ARITHABORT (Transact-SQL).

  • SET ANSI_WARNINGS ON
    If null values appear in aggregate functions, such as SUM or AVG, a warning message is generated. For more information, see SET ANSI_WARNINGS (Transact-SQL).

  • SET ANSI_NULLS ON
    Comparisons against a null value that are performed by using equals (=) or not equal (<>) operators are evaluated to return FALSE. For more information, see SET ANSI_NULLS (Transact-SQL).

  • SET CONCAT_NULL_YIELDS_NULL ON
    Concatenating a null value with a string yields a NULL result. For more information, see SET CONCAT_NULL_YIELDS_NULL (Transact-SQL).

  • SET ANSI_PADDING ON
    For columns that store values shorter than the defined size of the column, the values are padded. For columns that have trailing blanks in char, varchar, binary, and varbinary data, trailing blanks or zeros are not trimmed. For more information, see SET ANSI_PADDING (Transact-SQL).

  • SET CURSOR_CLOSE_ON_COMMIT ON
    When a transaction is either committed or rolled back, any open cursors are closed. For more information, see SET CURSOR_CLOSE_ON_COMMIT (Transact-SQL).

  • SET IMPLICIT_TRANSACTIONS OFF
    By default, transactions in HTTP SOAP sessions operate in autocommit mode where all individual statements are committed if they complete successfully. For more information, see SET IMPLICIT_TRANSACTIONS (Transact-SQL).

Cluster Failover Considerations for HTTP SOAP services

When installing SQL Server, it is a good practice to use a domain account as the SQL service account and to ensure that the account does not have full administration rights on the local computer. There are some implications in doing this when setting up a SQL server as a virtual server on a Windows cluster. The considerations are in configuring SQL HTTP-SOAP native services.

SQL Server leverages the HTTP driver from Windows Server 2003. This HTTP listener is designed to route requests to any application that subscribes to it in the Windows platform including SQL Server and IIS services. To subscribe to this service, applications need to call into the HTTP APIs to reserve the application’s specific root URL, also known as namespace. This reservation tells the HTTP driver to route all subsequent requests from that URL to the subscribed application. The call to these HTTP APIs must be under the context of an account that has local Windows administrator rights. Beginning with SQL Server 2005, these calls are made by using a special store procedure called sp_reserve_http_namespaces (see Reserving an HTTP Namespace for more information). This store procedure creates a binding between the root URL and the SQL service account for the Windows HTTP driver routing. If the SQL service account does not have administrator rights on the local machine, the stored procedure must be called by using a Windows local administrator account instead. Once this namespace has been registered, the SQL SOAP endpoints can be created under that URL for subsequent HTTP requests to be redirected. In a cluster setting, this HTTP namespace reservation must be established by a local Windows administrator on each one of the nodes. This ensures that the HTTP service still routes the requests to SQL server if any failover occurs over any of the nodes.

Another consideration in cluster failover with SQL Server and HTTP-SOAP is when using encryption certificates for requests over the SSL channel. For more information, see Configuring Certificate for Use by SSL. In a cluster environment, configuring the certificates needs to be done on each of the nodes as well. The subject for these certificates must be set as the fully qualified name of the virtual server name. To ensure that Kerberos authentication over HTTP is configured, see Registering Kerberos Service Principal Names by Using Http.sys, which also applies to clusters.