SQL server 2005 cluster failover considerations for HTTP SOAP services

When installing SQL server 2005, it is always a good practice to use a domain account as the SQL service account and to ensure that such 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.

 

To give some background, SQL server 2005 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 request 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. In SQL server 2005, these calls are made by using a special store procedure called “sp_reserve_http_namespaces” (see “Reserving an HTTP Namespace" topic in books online for details). 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 store 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 clusters failover with SQL server 2005 and HTTP-SOAP is when using encryption certificates for requests over the SSL channel (see “Configuring Certificate for Use by SSL” topic in books online for details). In a cluster environment, this configuration of 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. And lastly, to ensure that Kerberos authentication over HTTP is configured, please refer to information in the “Registering Kerberos Service Principal Names by Using Http.sys” in the books online topics, which also applies to clusters.

 

Humberto Acevedo, SQL Server Protocols Test.
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights