There are particular cases in which an organization will want users not currently on the same network as the SQL Services Analysis Services database to be able to connect to the OLAP cubes to refresh pivot tables in Excel. Some examples of this include remote employees or employees in satellite offices that are not constantly attached to the corporate network. Enabling a VPN connection will typically resolve this but in some cases this is not desirable. This article will describe the general process of enabling a secure connection to the OLAP database over IIS. This article discusses how to configure the connection when IIS and Analysis Services reside on the same machine.
Note: This article discusses a single-server scenario where IIS and SQL Server Analysis Services are on the same machine. If IIS is located on a different machine then Kerberos delegation may be required. Microsoft has a MSDN article discussing this here: Configure Analysis Services for Kerberos constrained delegation
Create New DNS Entry for Machine
In order for the URL that will be accessed by the users to be recognized on the network a new DNS entry should be added on the domain controller. The specifics of this are outside of the scope of this article but will typically involve adding a new host on the domain controller where the name (ie: reporting) is provided as well as the IP address (ie: 172.26.15.2). This will update the Fully Qualified Domain Name (FQDN) to resemble "reporting.mycompany.com" and provide the IP address for this location.
There are a number of steps to configure IIS to support the transfer of data between the end-user and the OLAP database. The following steps will discuss, in general terms, the configuration of IIS to use the MSMDPUMP.DLL provided with SQL Server to facilitate the transfer of OLAP data over IIS.
Create a New IIS Folder on the Machine
First, an IIS folder needs to be created in the file structure of the machine. This folder should be created in the default IIS folder location which is typically C:\inetpub. In this example we will create the folder and name it OLAP but this can be any name that is desired.
Copy Necessary SQL Files to IIS Folder
There are a few files that come with SQL that must be copied (not moved) to the folder that was created in the step above. These will be located in the SQL Server Analysis Services root under "OLAP -> bin -> isapi" (note the full path in the address bar below):
These files and the supporting folder(s) should be copied into the website folder (ie: C:\inetpub\OLAP) that was created in the previous step:
Once the files have been copied, open Notepad as an administrator (right-click on it and select "Run as administrator) and then navigate to the "msmdpump.ini" file (the bottom file in the screenshot above). Change the <ServerName> setting from "localhost" to the name of the server (and SQL instance if applicable) that the desired Analysis Services database is on (this is JTRCONMOBILE1 in our example):
Note: It may be necessary to add the following three ConfigurationSettings parameters to ensure connection stability.
Create a New Website in IIS
Next, a new website will be created by opening the Internet Information Services (IIS) Manager , right-clicking on Application Pools , and clicking Add Application Pool... :
In the Add Application Pool dialogue window, the Name should correspond to the name of the IIS folder created in a prior step and it is important to set the Managed Pipeline Mode to be Classic (do not use 'Integrated'). Click OK
The next step is to edit the ApplicationPoolIdentity to use a custom account. To do this, right-click on the OLAP application pool that was just created and select Advanced Settings...
In the Advanced Settings dialogue window, click the ellipsis (...) next to ApplicationPoolIdentity
A service account is recommended for the custom account and can typically be the same service account used to start Analysis Services as this should have all necessary permissions. If it is desired to use a different service account then this account should have full permissions to the OLAP database or be an administrator on Analysis Services.
The application pool is now in place.
Next, create a new website by right-clicking on Sites and selecting Add Website...
In the Add Website dialogue window, the Site Name should be the same that has been used in previous steps (OLAP in our example) and for the Physical Path it should point to the IIS folder created in the beginning (C:\inetput\OLAP in our example):
When clicking the Connect As... button, it should be using the Application User (pass-through authentication) setting that was configured in the previous step. This will pass the user's credentials through when they connect to ensure that the OLAP security is enforcing only what they are allowed to see:
Clicking the Test Settings button should show that both tests passed:
In the Binding section, the type should typically be set to HTTPS to use a properly signed SSL certificate for the domain. Obtaining the SSL certificate from a valid SSL issuer is outside of the scope of this document. The Host Name should refer to the fully-qualified domain name specified in a previous step and the proper SSL certificate should be chosen from the SSL Certificate dropdown list:
Click OK to save everything.
Configure IIS Authentication
It is important that proper IIS authentication is configured to ensure that the data is secure. Click on the site name that was created under the Sites node and double-click the Authentication icon under IIS:
Right-click on Windows Authentication and select Enabled to allow Windows login credentials to allow the automatic use of credentials from machines where the user is logged in using a domain account. Basic Authentication may also be set to Enabled to allow a user to type in a user name and password for users accessing the cubes from a machine where they are not logged in using domain credentials. It is important to right-click the Anonymous Authentication option and select Disable to not allow anonymous authentication.
Adding a Script Map for the MSMDPUMP.DLL
This will handle the proper mapping to the necessary DLL and is accomplished by clicking the OLAP name that was created under the Sites node and double-clicking Handler Mappings under the IIS section to the right:
Click the Add Script Map... button under the Actions window on the right:
The Request Path should be set to *.dll and for the executable the MDMDPUMP.DLL should be selected from the IIS site folder that the SQL files were copied to in a previous step. The Name field should contain the same name that has been used in previous steps for the site name for consistency (ie: in our example it is OLAP). Click OK :
It will then prompt to add the ISAPI extension. Click Yes .
Modify the Default Document for the Site
To enable the users to not have to include the name of the DLL when creating the connection to the data source the Default Document for the site can be changed to automatically refer to the DLL. Click on the OLAP site under the Sites node and then double-click the Default Document icon under IIS to the right:
Right-click somewhere in the Default Document window and click Add... :
Type msmdpump.dll in the Add Default Document window to specify the proper file to reference. Click OK .