SQL server 2005 cluster failover considerations for HTTP SOAP services

  • Article

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

Comments

  • Anonymous
    October 28, 2006
    PingBack from http://chaespot.com/mssql/2006/10/28/sqljunkies-the-1-sql-server-community-for-developers-3/

  • Anonymous
    February 15, 2007
    PingBack from http://chaespot.com/mssql/2007/02/15/a-sql-server-2005-based-or-sql-server/

  • Anonymous
    November 12, 2008
    Regarding the statement in your first paragraph -- I just got off of a trouble call with MS while we were having problems installing out SQL2k5 Entx64 on top of Win2k8 Entx64 dual node cluster (active/active).  One of the things that the lvl 3 MS tech advised us was that we needed to ensure that both our service account and our installation account needed to be local admin accounts.  How do you reconcile your statement with what the MS tech told me?  Just curious, as we started out without our users as local admins because of similar articles we had read before beginning the installation in the first place.  (Regardless, no having the admin rights for those users did indeed cause us some problems amidst all the other problems we had). thanks, Sonya DBA