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.
To create and manage endpoints, use the following Transact-SQL statements:
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.
Use to add, drop, or modify methods or change endpoint options.
Use to delete unwanted or obsolete endpoints.
To create, modify, or drop HTTP endpoints you must have permissions. To access endpoints requires authentication. These requirements are described in the following topics:
To expose stored procedures or user-defined functions, see the following topic:
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.