Ahmed Medhat's Blog: An Extra Mile: Configure HTTP Access to Analysis Services on Internet Information Services (IIS) 7.0

Through this post I'll try to explain how to connect MS Office Excel to Analysis Services Cube but through HTTP access

The idea of configuring such approach is that normally, you need to be connected to the domain containing your SQL servers to be able to connect your excel file with the external OLAP source (Cube). while on the other hand what if I need users to be able to do ad-hoc reporting from home or mainly outside the domain ? ... So the solution for this is to Configure HTTP Access to Analysis Services ;)

These instructions I'm going to explain are valid for any supported version or edition of:

Analysis Services instance that interfaces with IIS 7.0,
SQL Server 2005 or later versions (SQL Server 2012, SQL Server 2008 R2, SQL Server 2008)
HTTP access is supported for both tabular mode and multidimensional mode servers.

Important Note (Study this table from Microsoft MSDN before applying the below steps):

Scenario	Configuration
IIS and Analysis Services on the same computer	This is the simplest configuration because it allows you to use the default configuration (where the server name is localhost), the local Analysis Services OLE DB provider, and Windows integrated security with NTLM. Assuming that the client is also in the same domain, user authentication is transparent to the user, with no additional work on your part.
IIS and Analysis Services on different computers	For this topology, you must install the Analysis Services OLE DB provider on the web server. Provider (.msi) file is located in [Deployment Folder]\Release\Release_1.1\Prerequisites\SQL2008R2_SSAS_OLEDB_Provider You must also edit the msmdpump.ini file to specify the location of Analysis Services instance on the remote computer. This topology adds a double-hop authentication step, where credentials must flow from the client to the web server, and on to the backend Analysis Services server. If you are using Windows credentials and NTLM, you will get an error because NTLM does not allow delegation of client credentials to a second server. The most common solution is to use Basic authentication with Secure Sockets Layer (SSL), but this will require users to provide a user name and password when accessing the MSMDPUMP virtual directory. A more straightforward approach might be to enable Kerberos and configure Analysis Services constrained delegation so that users can access Analysis Services in a transparent manner. Consider which ports to unblock in Windows Firewall. You will need to unblock ports on both servers to allow access to the web application on IIS, and to Analysis Services on a remote server.

What I'm going to apply/explain in this article is the "IIS and Analysis Services on different computers" approach.

"Enough with the boring readings already :D ........ NOW Let's Go"

The following steps explain how to set up HTTP access to an Analysis Services default instance using IIS 7.0:

Copy the MSMDPUMP.dll to a folder on the Web server:

On each WFE server, create the an OLAP folder under: C:\inetpub\wwwroot\wss\VirtualDirectories\OLAP
Copy the contents of the ISAPI folder on the Analysis Services computer to the C:\inetpub\wwwroot\wss\VirtualDirectories\OLAP folder you just created.(Note: The ISAPI folder can be found at <drive>:\Program Files\Microsoft SQL Server\<instance>\OLAP\bin\isapi. It contains the following files and folder: MSMDPUMP.DLL, MSMDPUMP.INI, and a Resources folder that contain language resource files.)
Verify that the “\inetpub\wwwroot\wss\VirtualDirectories\OLAP\” folder on your web server contains the following:

MSMDPUMP.DLL
MSMDPUMP.INI
Resources folder

Create an application pool and virtual directory in IIS:

Start IIS Manager > open Sites
Under "Default Web Site" > Navigate to Find your OLAP Folder
(Note: You can create a SharePoint Application and add the OLAP application beneath it as well)
On the OLAP Folder:

Right-click the OLAP virtual directory, and then click Convert to Application
In Alias, type OLAP
In Physical Path, click the browse button and navigate to C:\inetpub\wwwroot\wss\VirtualDirectories\OLAP. Click OK
Click OK twice to accept the changes, and to convert the application

Configure IIS authentication and add the extension:

Select the OLAP virtual directory.
Double-click Authentication in the IIS section of the main page
Disable all the Authentication methods, and enable only Basic Authentication
Click the OLAP virtual directory to open the main page. Double-click Handler Mappings.
Right-click anywhere on the page and then select Add Script Map.
In the Add Script Map dialog box, specify

*.dll as the request path
C:\inetpub\wwwroot\VirtualDirectories\OLAP\msmdpump.dll as the executable
Type OLAP as the name

Click Request Restrictions
On the Verbs tab, verify that “all verbs” is selected. Click OK, and then click OK again to finish adding the script mapping
When prompted to allow the ISAPI extension, click yes

Edit the MSMDPUMP.INI file to set the target server:

Open the msmdpump.ini file located in folder C:\inetpub\wwwroot\VirtualDirectories\OLAP and take a look at the contents of this file. It should look like the following:
If the Analysis Services instance for which you are configuring HTTP access is a default instance, you will either leave the (*.ini) file as it is or specify the server name (for example, <ServerName>MyServer</ServerName>).
But if the analysis services Instance is a named instance, then be sure to append the instance name (In my Case Its named: <ServerName>lms-sql-vs01</ServerName>)

Give Access to users will be logging on to the OLAP application:

On each WFE, Navigate to "C:\inetpub\wwwroot\wss\VirtualDirectories\OLAP"
On the OLAP folder > Right Click > Properties > Security Tab

Edit and Add the groups/users that you need them to access the targeted OLAP Cube with default permissions.

Configure SSL Certificate:

If you are to publish your website/SharePoint application with HTTPS, then:

Install/Request a Valid SSL Certificate to publish your website using HTTPs.
Make Sure that your default web site/your SharePoint application is published and successfully accessed externally.

Test your Configuration:

[Approach 1: Internal Testing]:

Inside your SQL Server, on the desktop> right click > create new document
Rename the document to "TestHTTPAccess" and replace the extension from ".TXT" to ".UDL"
Open the UDL file, on the "Provider" tab, select the provider "Microsoft OLEDB Provider for Analysis Services 10.0"
On the "Connection" tab, write the following:

Data Source: http://<your website name>/OLAP/msmdpump.dll (Replace <your website name> with your internal SharePoint/WebSite URL)
User Name: <domain-name>\<user-name>
(Write the username of the user(s) you previously added to the OLAP folder permissions)
Password: <Your Password>

Click on Test Configuration, if it Succeeds, then continue with approach 2

[Approach 2: External Testing]:

Open a new MS Office Excel sheet (2007, 2010 or 2013)
From the Data tab > Choose from Other Sources > from Analysis Services
In the Data connection wizard, Write your SharePoint application/WebSite URL (http(s)://<your website name>/OLAP/msmdpump.dll) and the user name and password of the user with access permissions on your cube
If connection is successful, you should be able to see your Cube, and start working with your pivot table(To know how to integrate these Excel Reports with SharePoint, kindly follow my post on using Kerberos authentication with SP 2010 excel services > http://ahmedmedhat141084.blogspot.com/2013/06/using-sharepoint-2010-with-excel.html)