Configuring HTTP Access to SQL Server 2005 Analysis Server

Author: Edward Melomed

Published: May 2005

Summary:

This paper is intended for anyone is interesting in providing access to SQL Server Analysis Sever through HTTP protocol.

The paper explains the steps required to set up HTTP access. It is also will go into discussion about different performance and security settings.

Please note that all the instruction provided are specific to Microsoft Windows XP operating system.

Copyright

This is a preliminary document and may be changed substantially prior to final commercial release of the software described herein.

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred.

Microsoft, Active Directory, and Windows are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

Overview

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 Server and back.

This white paper walks you through the process of setting up HTTP access to Analysis Server. Figure 1 provides a high-level overview of the component architecture:

Figure 1

Setting Up HTTP Access to an Analysis Services Server on Microsoft Windows® XP

Getting Binaries

To get binaries:

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

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 C:\inetpub\wwwwroot\olap directory.

Notes: To take advantage of the full set of security settings, it is important to make sure that the folder to become the base for the virtual directory is located on the drive formatted for the NTFS file system.

Due to IIS limitations, the path to your directory should not contain spaces.

If you are planning to run HTTP pump on different machine from Analysis Server machine, please also make sure you install %CD Root%\Redist\Analysis Services\pts9.msi.

Creating a Virtual Directory

To create a virtual directory:

1. To open the Computer Management console, open Control Panel, then Administrative Tools, then Computer Management

(Or, you can right-click the My Computer icon and select Manage on the shortcut menu).

2. In the Computer Management console, expand the Services and Applications node and then the Internet Information Services node.

If you can’t find the Internet Information Services node, IIS is likely not installed. To install it, open Add or Remove Programs in Control Panel. Select Add/Remove Windows Components. Then add IIS to Windows.

3. Right-click on the Web site to open the shortcut menu and select New, then Virtual directory.

4. Name the virtual directory. In this example: OLAP. The Content Directory should point to the folder you’ve just created. In our example, this is C:\inetpub\wwwroot\olap.

5. Make sure that under the Access Permissions, only the second check box, Run Scripts (Such as ASP) is selected. (See Figure 2.)

Figure 2

Setting Up Virtual Directory Properties

To set properties for the virtual directory:

1. Right-click your virtual directory node and select Properties from the menu.

You should see the screen shown in Figure 3.

Figure 3

Properties that need to be changed are circled in red.

Properties that are not required to be changed but which play an important role in the security and performance of the virtual directory are circled in green.

2. Select the Configuration button and you will see the screen shown in Figure 4.

Figure 4

3. Click the Add button.

4. In the Executable option, enter the full path name to msmdpump.dll. In this example it would be: C:\inetpub\wwwroot\olap\msmdpump.dll

5. Enter .dll in the Extension text box.

Your screen should look something like the dialog box shown in Figure 4.

Figure 5

6. Click the OK button to accept the settings.

Selecting Security Settings

To select security settings;

1. Select the Directory security tab. You will see a dialog box similar to the one in Figure 6.

Figure 6

2. You are presented with three options. Choose one of them.

This section briefly describes these options, citing the advantages, disadvantages, and security concerns of each one.

Anonymous Access

When this mode is selected, Pump ( msmdpump.dll) is running with credentials; in our case, the credentials of IUSR_MACHINENAME user. Therefore, every connection to Analysis Server is opened as IUSR_MACHINENAME user. When this mode is selected, there is no distinction on which user is connecting to IIS and to Analysis Server.

This mode is to be used when the security infrastructure does not take advantage of the security functionality of Analysis Server. This is most likely an extremely controlled environment, where users are given or denied access to the virtual directory.

Basic authentication

This mode requires that the user enter a user name and password. The user name and password are transmitted over the HTTP connection to IIS where IIS will authenticate and impersonate the user using the provided credentials.

Please note that it is absolutely imperative for anyone building a system where the password is transmitted to have ways of securing the communication channel using HTTPS protocol.

Integrated Windows authentication

This is the most secure and the recommended mode.

It requires that IIS be able access user domain credentials. This could be done using Microsoft Active Directory® or other mechanisms. It is beyond the scope of this paper to discuss in depth all the possible configurations.

Securing User Access to the Directory Serving as a Base for the Virtual Directory

It is strongly recommended that you secure user access to the directory that is used as a base for the virtual directory you’ve created and set up.

It is recommended that you give “Read and Execute” rights to your file system folder to Everyone.

You might want to give full access to members of the Administrators local group.

Selecting the Target Analysis Server

As you can see from the architectural diagram in Figure 1, every pump component uses its own configuration file.

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

<ServerName>localhost</ServerName>

</ConfigurationSettings>

The interesting element is <ServerName>. If the Analysis Server 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, specify the machine name and instance name (mymachine\inst1 ).

It is also possible to specify here a pointer to the virtual directory on another IIS server that is set up for HTTP access to Analysis Server.

For example you can have <ServerName>http://secondmachine/olap/msmdpump.dll</ServerName>

Getting all together.

At this point you should have configured your HTTP Pump and should be ready to try to connect from your application.

If your application is providing you with the way to specify Server name all you need is substitute your server name with path to your virtual directory concatenated with “msmdpump.dll”. As in case of SQL Server 2000 Analysis Services the MSOLAP OLEDB provider will understand the fact that Server name includes a URL path and will automatically start using a HTTP protocol.

For example if you are to connect to “MyMachine” from MDX sample application, you should be able to connect to Analysis Server using “http://MyMachine/olap/msmdpump.dll” as a server name.