Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft Windows Server 2008

Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft Windows Server 2008

Author Karang (OLAP Team)

Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft Windows Server 2008

Microsoft® SQL Server™ 2005 Analysis Services uses the same architecture for providing HTTP access as did SQL Server 2000 Analysis Services.

The pump component is loaded into IIS (Internet Information Services) and serves as an ISAPI extension, pumping data from the client to an Analysis Services server and back.

This white paper walks you through the process of setting up HTTP access to Analysis Services when using Microsoft Windows Server™ 2008 IIS7

Getting Binaries

Copy the contents of the %Installation folder%\OLAP\bin\isapi directory into the folder you would like to become the base for the virtual directory in IIS.

In this example, we are going to copy all the files from the C:\Program Files\Microsoft SQL Server\MSSQL.1\OLAP\bin\isapi folder into the C:\inetpub\wwwroot\olap directory.

Creating an Application Pool

  1. To Open IIS Got to Start ->Run -> Type Inetmgr

  1. Go to Application Pool and Create a New Application Pool with below mentioned values: Name: OLAP

.Net Framework Version: v2.0.50727

Managed Pipeline mode: Classic

  1. By default Application Pool select network service for changing Identity of Application Pool Right Click OLAP (Newly Created Application Pool) Select Advanced Setting and Change Identity under Process Model

Creating a virtual directory

  1. To Open IIS Go to Start ->Run -> Type Inetmgr

  1. Right Click Website -> Click on Add Virtual Directory

  1. Change Virtual Directory to Application: Right Click Newly Created Virtual Directory (OLAP) and select Convert to Application and on Convert to Application window Click OK

Setting Up Virtual Directory Properties

  1. To setup Security, Select OLAP Virtual Directory on the Right Pane Select Feature View

  1. Double Click on Authentication under IIS, Enable Anonymous Access & Integrated Windows Authentication
  1. For adding msmdpump.dll Executable as a Extension, Go to OLAP Virtual Directory Feature View and double click Handler Mappings
  1. Right Click on Page and Select Add Script Map

Request Path: *.dll

Executable: Path of msmdpump.dll (C:\Inetpub\wwwroot\olap\msmdpump.dll)

Name: OLAP

  1. Click on Request Restriction and on Verbs Tab Select All Verbs
  1. When click ok on Add Script Map it will ask you to allow this ISAP extension?

Click Yes

Selecting the target Analysis Services server

Open the msmdpump.ini file located in your folder and take a look at the contents of this file. It should look like the following:

<ConfigurationSettings>

<ServerName>localhost</ServerName>

<SessionTimeout>3600</SessionTimeout>

<ConnectionPoolSize>100</ConnectionPoolSize>

<MinThreadPoolSize>0</MinThreadPoolSize>

<MaxThreadPoolSize>0</MaxThreadPoolSize>

<MaxThreadsPerClient>4</MaxThreadsPerClient>

</ConfigurationSettings>

Change <ServerName> Value to your AS Server

If the Analysis Services instance that you need to provide access to is located on the local machine and installed as a default instance, there is no reason to change this setting. Otherwise, you need to specify the machine name and instance name ( mymachine\inst1).

Small Test which signifies that Data Pump is configured successfully

Open SQL Management Studio, Select Server Type as Analysis Services & Provide msmdpump.dll URL(< in Server Name, successful connection signifies that Pump is configured properly.

Top View