Configure Analysis Service HTTP connections (Double-Hop)

One of the key features in Microsoft? SQL Server? 2000 Analysis Services is the capability to access data from the Web. The PivotTable? Service (PTS) OLE DB provider uses a special Active Server Pages (ASP) Web page to send and receive data using the HTTP or HTTPS protocol. The ASP page uses a single COM object, called the data pump, which acts as a conduit for Analysis Services data. The library that contains this COM object is referred to as the data pump library.

Analysis Services originally required for Internet Information Services (IIS) to be installed on the same server, because the data pump library used shared memory to communicate directly with the Analysis server. The original structure has to be:
-------Web Server installed IIS + Analysis Server installed IIS------------

With the release of Service Pack 3 (SP3) for SQL Server 2000 Analysis Services, however, the data pump library can also use a named pipe to share information with the Analysis server. The use of named pipes allows installation of the data pump library on a separate computer running IIS, thereby increasing the security and stability of both IIS and Analysis Services. The current structure could be:
-------Web Server installed IIS + Analysis Server
-------Web Server installed IIS + Firewall/proxy Server + Analysis Server
-------Web Server installed IIS + Firewall/proxy Server + IIS + Analysis Server

When a connection is made to a computer that is running Microsoft SQL Server 2000 Analysis Services and that connection involves a double-hop authentication scenario, we must use Kerberos as the authentication protocol. For example, in a double-hop authentication scenario, a client computer may pass the logon credentials to a computer that is running Microsoft Internet Information Services (IIS). The computer that is running IIS must then pass the logon credentials to the Analysis server computer. This article describes how to configure an Analysis server computer to use the Kerberos authentication protocol in windows 2000.

How to configure an Analysis server computer to use Kerberos authentication
http://support.microsoft.com/default.aspx?scid=kb;EN-US;828280

However, in windows 2003, the scenario is changed. The com objects is not used again as the connections from client on IIS will exist in W3Wp process but not dllhost process. Therefore,in windows 2003 environment, the double-hop configration of Analyasis Serivce 2000 HTTP will be as below:

Software Requirement
--------
Database Server:
    OS    :    Win2003 with SP1
    AS    :    AS2000 with SP4
Web Server:
    OS    :    Win2003 with SP1
             :    IIS 6.0

Detailed Required Steps
----------
1. Analysis Service components requirements
1).  Make sure the Analysis Service SP4 is installed on the Analysis Server.
2). Make sure the Pivot Table Service (PTS) SP4 is installed on the machines (including IIS server), which is not is installed Analysis Service.

2. Configure msmdpump.dll on the IIS server.
1). Copy the msmdpump.dll from Analysis Service machine, which is under c:/Program Files/Microsoft Analysis Services/bin by default.
2). Manually register the msmdpump.dll using regsvr32.exe. If the error “the specified model cannot be found” occurred, we need to install Microsoft .Net Framework 1.1 on the IIS server and copy the DLL MSVCR71.dll (7.10.3052.4) to C:/windows/system32

3. Configure the msolap.asp page on the IIS server.
1). Copy the msmdpump.dll from Analysis Service machine, which is under c:/Program Files/Microsoft Analysis Services/bin by default.
2). For further steps to configure the msolap.asp page on the IIS, please see the steps as below:
How to configure Analysis Services for the Web
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/aghtconfig_4giq.asp
3). Only Windows Integrated Authentication or Basic Authentication should be enabled on the web site or virtual directory.
4). If you get the blank page when you browse the msolap.asp page, it indicates the msolap.asp page configuration is ok.

4. Configure Analysis Services to use Kerberos as an authentication protocol

1). A ServicePrincipalName (SPN) must be correctly registered in the Active Directory Service
   a. If the MSSQLServerOLAPService service is running under the LocalSystem account, the SPN should be registered automatically.
   b. If the MSSQLServerOLAPService service is running under the LocalSystem account, and the SPN is not automatically registered, use the setSPN utility that ships with the Windows 2000 Resource Kit to create the SPN
http://www.microsoft.com/downloads/details.aspx?FamilyID=5fd831fd-ab77-46a3-9cfe-ff01d29e5c46&DisplayLang=en
   c. If the MSSQLServerOLAPService service is running under a domain account, the following actions are required:
      1). The SPN must be registered manually (see Manual creation of an SPN below).
      2). Grant the domain account the following rights:
          a). Log on as a service
          b). Act as part of the Os
          c). Replace a Process Level Token
          d). Create a Token
      3). Add the account to the local Olap Administrators group
      4). Grant the OLAP Administrators group full control on the following directories:
          a). ../Program Files/Microsoft Analysis Services/BIN
          b). ../Program Files/Microsoft Analysis Services/Data
      5). If the OLAP Repository has been migrated to SQL Server, the account must have db_owner permissions on the repository database.
 
2) All Computers:
a. All machines should be Windows 2000 or later
b. All machines should be in the same domain or in domains with a two-way trust
c. The domain(s) must be using Active Directory
d. Synchronized system clocks. Kerberos depends on synchronized timing between computers. If the clocks are out of synch, Kerberos authentication may fail. If you need to do this, a simple DOS command to run from each machine is:
            net time //DOMAIN:DOMAIN_NAME /SET
e. DNS must be properly configured.
f. Reverse zone lookup must be configured. Analysis Services performs a reverse lookup of the client's IP address to resolve to a netbios machine name for logging. If the Reverse lookup zone is not configured, create one then execute the following command from the machine(s) being used:
   ipconfig /registerdns
g. The Preferred DNS Server on all machines in the domain should point to the configured DNS server.
 
3). Active Directory Settings:
a. Mark user accounts that will be delegated as "Not Sensitive". If an account is marked as sensitive, it cannot be delegated.
b. User accounts that will be delegated do not require being marked as "Trusted for Delegation".
c. If the MSSQLServerOLAPService will be running under a domain account, mark that account as "Trusted for Delegation"
d. Mark the IIS Server as "Trusted for Delegation".
e. Create a Service Principal Name for the OLAP Server. If it's running under a the LocalSystem account, the SPN is created automatically. If it's running under a domain account, it is necessary to manually create the SPN.
f. Create a Service Principal Name for the IIS Server. The setSPN tool from the Windows 2000 resource Kit to create the SPN. The syntax for creating the IIS SPN is:
        setspn -A http/IISServerName IISServerName

4). Client Computers:
a. The browser must be Internet Explorer 5.0 or later.
b. If the browser is Internet Explorer 6.0 it may be necessary to turn on "Enable Windows Integrated Authentication" in the browsers Advanced Options.
Note that turning that setting on requires a reboot.
c. The connection string used by the OLAP Client must contain "SSPI= Kerberos" (note, this is case sensitive).
 
5). IIS Server:
a. Only Windows Integrated Authentication or Basic Authentication should be enabled on the web site or virtual directory.
b. Since the OS is Windows 2003, please create a separate application pool for the virtual directory. This will prevent a crash of the W3WP.exe process hosting the application from taking out everything being hosted on the server. We created a pool for olap on the IIS server, and set the account of pool identity as the domain account which has local admin permission on both IIS server and Analysis Server. It's better to make this account as the Analysis Service startup account.

Reference:
Improved Web Connectivity in Microsoft SQL Server 2000 Analysis Services
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_datapump.asp

Thanks and regards,
Michael