• Article

Configure HTTP Access to SQL Server Analysis Services on IIS 7.0

You can enable HTTP access to Analysis Services by configuring MSMDPUMP.dll, an ISAPI extension that runs in Internet Information Services (IIS) and pumps data to and from client applications and an Analysis Services server. This approach provides an alternative means for connecting to Analysis Services when your OLAP solution calls for the following capabilities:

  • Client access is over Internet or extranet connections, with restrictions on which ports can be enabled. Or, client connections are from non-trusted domains in the same network.

  • Client application runs in a network environment that allows HTTP but not TCP/IP connections.

  • Authentication methods other than Windows integrated security are required. IIS supports Anonymous connections and Basic authentication. Configuring Analysis Services for HTTP access lets you use these alternative authentication methods with Analysis Services.

  • Client applications cannot use the Analysis Services client libraries (for example, a Java application running on a UNIX server). If you cannot use the Analysis Services client libraries for data access, you can use SOAP and XML/A over a direct HTTP connection to an Analysis Services instance.

This topic explains how to set up HTTP access to an Analysis Services instance using IIS 7.0. These instructions are valid for any supported version or edition of an Analysis Services OLAP instance that interfaces with IIS 7.0, including SQL Server 2008 R2, SQL Server 2008, and SQL Server 2005.

This topic includes the following sections:

Overview

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

Create an application pool and virtual directory in IIS

Configure IIS authentication and add the extension

Edit the MSMDPUMP.INI file to set the target server

Test your configuration

Overview

MSMDPUMP is an ISAPI extension that loads into IIS and provides redirection to an Analysis Services instance that is on the same computer or a remote computer within the same domain. You must create and configure one virtual directory, each with its own set of MSMDPUMP files, for each Analysis Services instance you want to connect to. The configuration file in each set of MSMDPUMP files specifies the name of the Analysis Services server instance used for each HTTP endpoint.

On IIS, MSMDPUMP connects to Analysis Services using the Analysis Services OLE DB provider over TCP/IP. Both Analysis Services and IIS must be in the same domain or in trusted domains in order for the native connection to succeed.

When MSMDPUMP connects to Analysis Services, it does so under a Windows user identity. This account will either be the Anonymous account if you configured the virtual directory for anonymous connections, or a Windows user account. The account must have the appropriate data access rights on the Analysis Services server and database.

Note

Remember to unblock the ports in Windows Firewall to allow client connections to a remote Analysis Services server. For more information, see Configure Windows Firewall for Analysis Services Access.

SSAS_HttpAccess_Pump

The following table lists additional considerations when you enable HTTP access for different scenarios.

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. 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.

Client connections are from a non-trusted domain or an extranet connection

Client connections from a non-trusted domain introduce further restrictions on authentication. By default, Analysis Services uses Windows integrated authentication, which requires users to be on the same domain as the server. If you have Extranet users who connect to IIS from outside the domain, those users will get a connection error if the server is configured to use the default settings.

Workarounds include having Extranet users connect through a VPN using domain credentials. However, a better approach might be to enable Basic authentication and SSL on your IIS web site.

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

In this section, you copy the MSMDPUMP executable, configuration file, and resource files from the Analysis Services program folders to an OLAP virtual directory folder that you create on the file system.

The drive must be formatted for the NTFS file system. The path to the folder that you create must not contain any spaces.

  1. On the web server, create the following folder: <drive>:\inetpub\wwwroot\olap

  2. Copy the contents of the ISAPI folder on the Analysis Services computer to the \inetpub\wwwroot\olap folder you just created.

    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.

    Note

    Note that the format of the instance name has changed in recent releases of SQL Server. If you are using SQL Server 2008 R2, the default instance name is MSAS10_50.MSSQLSERVER. If you are using earlier versions of SQL Server, the instance names are created sequentially by installation order, where MSSQL.1 is the first service installed, MSSQL.2 is the second service, and so on. You might need to open the instance folders to determine which one has the Analysis Services program files.

  3. Verify that the \inetpub\wwwroot\olap\isapi folder on your web server contains the following: MSMDPUMP.DLL, MSMDPUMP.INI, and a Resources folder.

Create an application pool and virtual directory in IIS

Configuring HTTP access starts with creating an application pool and virtual directory that provides an endpoint to the pump.

Create an application pool

  1. Start IIS Manager. Click Start, point to Run, and then type Inetmgr.

    SSAS_HttpAccess_Inetmgr

  2. Right-click Application Pools and then click Add Application Pool. Create an application pool named OLAP, using .NET Framework v2.0.50727, with Managed pipeline mode set to Classic.

    SSAS_HttpAccess_AddAP

  3. By default, IIS creates application pools using Network Service as the security identity. To change the identity of the application pool you just created, right-click OLAP, and then select Advanced Settings.

    SSAS_HttpAccess_AdvSettings

  4. In Identity, click the built-in account that IIS specified. Depending on the version of Windows that you are using, this is either Network Service (shown in the screenshot) or ApplicationPoolIdentity. Click the Change button for this property to replace the built-in account with the custom account you want to use.

  5. By default, on a 64-bit operating system, IIS sets the Enable 32-bit Applications property to false. If you copied msmdpump.dll from a 64-bit installation of Analysis Services, this is the correct setting for the MSMDPUMP extension on a 64-bit IIS server. If you copied the MSMDPUMP binaries from a 32-bit installation, set it to true. Check this property now to ensure it is set correctly.

Create a virtual directory

  1. In IIS Manager, open Sites, right-click Default Web Site (or whatever web site you are using to access the pump), and then click Add Virtual Directory.

  2. In Alias, type OLAP.

  3. In Physical Path, click the browse button and navigate to C:\inetpub\wwwroot\olap. Click OK.

    SSAS_HttpAccess_AddVdir

  4. Right-click the OLAP virtual directory you just created, and then click Convert to Application.

  5. In the Add Application dialog box, next to Application Pool, click Select and then choose the OLAP application pool that you created in the previous section.

  6. Click OK twice to accept the changes, and to convert the application.

Configure IIS authentication and add the extension

In this section, you further configure the OLAP virtual directory you just created. You will specify an authentication method and then add a script map. For more information about authentication methods, see IIS Authentication Methods.

  1. In IIS Manager, open Sites, open Default Web Site, and then select the OLAP virtual directory.

  2. Double-click Authentication in the IIS section of the main page.

    SSAS_HttpAccess_IIS

  3. Enable Windows Authentication if you are using Windows integrated security.

    This is the most secure and recommended mode, but it requires that IIS be able to access user domain credentials, using Microsoft Active Directory® or another mechanism. If Analysis Services and IIS are on different computers, you will need to enable Kerberos and configure Analysis Services for constrained delegation. It is beyond the scope of this topic to discuss all the possible configurations.

    SSAS_HttpAccess_IISAuth

  4. Alternatively, enable Basic Authentication if your client and server applications are in different domains. This mode requires the user to enter a user name and password. The user name and password are transmitted over the HTTP connection to IIS. IIS will try to impersonate the user using the provided credentials. Please note that it is imperative for anyone building a system where the password is transmitted to have ways of securing the communication channel. IIS provides a set of tools that help you secure the channel. For more information, see How to Set Up SSL on IIS 7.

    Basic authentication is useful when you want to collect user credentials from the client, giving users a way to access the Web site using credentials that are different from their personal Windows login account. When you select Basic authentication, IIS reads the HTTP header of each request for a username and password. If it doesn't find the proper credentials, IIS sends a response to the client asking for a username and password. The client prompts the user for credentials and then uses Base64 encoding to send the username and password to the server. Although the credentials are encoded, they are not encrypted and are therefore not secure. This is why it’s strongly recommended that Web sites use a combination of Basic authentication and SSL to encrypt the credentials that the client sends to the server.

  5. Disable Anonymous Authentication unless you are supporting connections through IUSR_<computername>. If you enable Anonymous Authentication, IIS will always use it first, even if you enable other authentication methods.

    If you do enable Anonymous Authentication, be sure that the IUSR_<computername> account on your web server has the appropriate data access permissions on the Analysis Services database.

    When this mode is selected, the pump (msmdpump.dll) runs using the credentials of IUSR_<computername>. Therefore, every connection to Analysis Services is opened as IUSR_<computername> user. When this mode is selected, there is no distinction between which user is connecting to IIS and which to Analysis Services. This mode is most likely found in an extremely controlled environment, where users are given or denied access by way of access control lists on the virtual directory.

  6. Click the OLAP virtual directory to open the main page. Double-click Handler Mappings.

    SSAS_HttpAccess_IIS

  7. 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, specify c:\inetpub\wwwroot\olap\msmdpump.dll as the executable, and type OLAP as the name.

    SSAS_HttpAccess_AddScript

  8. Click Request Restrictions.

  9. On the Verbs tab, verify that All verbs is selected. Click OK, and then click OK again to finish adding the script mapping.

    SSAS_HttpAccess_RequestRestrictions

  10. When prompted to allow the ISAPI extension, click Yes.

    SSAS_HttpAccess_ISAPIPrompt

Edit the MSMDPUMP.INI file to set the target server

Open the msmdpump.ini file located in folder C:\inetpub\wwwroot\OLAPand 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>
</ConfigurationSettings>

If the Analysis Services instance for which you are configuring HTTP access is located on the local computer and installed as a default instance, there is no reason to change this setting. Otherwise, you must specify the server name (for example, <ServerName>ADWRKS-SRV01</ServerName>). For a server that is installed as a named instance, be sure to append the instance name (for example, <ServerName>ADWRKS-SRV01\Tabular</ServerName>).

By default, Analysis Services listens on TCP/IP port 2383. If you installed Analysis Services as the default instance, you do not need to specify any port in <ServerName> because Analysis Services knows how to listen on port 2383 automatically. However, you do need to allow inbound connections to that port in Windows Firewall. For more information, see Configure Windows Firewall for Analysis Services Access.

If you configured a named or default instance of Analysis Services to listen on a fixed port, you must add the port number to the server name (for example, <ServerName>AW-SRV01:55555</ServerName>) and you must allow inbound connections in Windows Firewall to that port.

Test your configuration

The connection string syntax for MSMDPUMP is the URL to the MSMDPUMP.dll file.

If the web application is listening on a fixed port, append the port number to the server name or IP address (for example, http://my-web-srv01:8080/olap/msmdpump.dll or http://123.456.789.012:8080/olap/msmdpump.dll.

To quickly test the connection, you can open a connection using Microsoft Excel or SQL Server Management Studio.

  • In Excel, in Data, click Connections. In Get External Data, click From Other Sources, and then choose From Analysis Services. In Server name, enter the HTTP address of the msmdpump extension: http://my-web-srv01/OLAP/msmdpump.dll.

  • In Management Studio, in the Connect to Server dialog box, select Analysis Services as the server type. In Server name, enter the HTTP address of the msmdpump extension: http://my-web-srv01/OLAP/msmdpump.dll.

Be sure to follow-up with more rigorous testing by using an actual client computer that runs in the network environment from which the connections will originate. Remember to grant the appropriate permissions on the Analysis Services server (either IUSR_<computername> if you enabled Anonymous connections or the Windows identities of the users who are connecting to MSMDPUMP by way of IIS).