HTTP Access to Analysis Services

In retrospect this was a simple task: the steps set out in the invaluable video Configuring HTTP for SSAS  all work.  However, in the middle of a long process trying to get it to work the first time it seems anything but simple.  So here are some observations to help reassure you that it really is straightforward, even after a full day battling without success.

Environment

These observations are from configuring HTTP SSAS access in this environment:

  • Windows Server 2016 Datacentre (EC2 instance)
  • SQL Server Analysis Services 2017 Evaluation (developer) edition
  • IIS 10

both SSAS and IIS running on the same server.

Install Cumulative Update 9

The most important lesson is to install SQL Server CU9 or later.   The fault in msmdpump.dll is indicated in this MS article: FIX: Access to SSAS by using HTTP fails in SQL Server The article is dated 18 July 2018.

Here are some of the responses you will see if you are using the pre-CU9 version of the file – assuming the configuration is otherwise correct.

SSMS

The response is immediate.

Cannot connect to http://...url/.

File system error: The following error occurred while writing to the file 'MSOLAP ISAPI 
Extension: \\?\C:\inetpub\OLAP\msmdpump.dll': Access is denied. .
Internal error: An unexpected exception occurred. (Unknown)

Excel

Response is immediate.

XML parsing failed at line 1, column 1: Incorrect document syntax.
.
File system error: The following error occurred while reading from the file 'Compressed stream'.

Browser

The response to a browser http request is the same as from the correct DLL, so this cannot be used to determine whether the msmdpump.dll version is correct.

Successful Installation

Connecting to the HTTP address on the same server as IIS returns this:

 

 

 

 

This is the right response.  It’s a 501 error code, so this won’t be seen from browsers on remote hosts.

Other Errors

Excel will report an error message from the host quickly although some are replaced by a generic “check typing of username” friendly message from Excel.  SSMS might allow server-level nodes and some cube structure to be viewed in the explorer.  If a cube is visible, attempting to browse it can provide more error information (although hidden by default).

  • Either the user, ‘NT AUTHORITY\IUSR’, does not have access to the ‘<Cube Name>’ database, or the database does not exist.    –  The solution is to make IUSR a member of the Server Administrators of the SSAS instance.  IUSR does not (and should not, for security) need to be a member of any Role within the cube.

Other Observations

SSL Offloading

The HTTP endpoint handles SSL encoding on load balancer without problem.  No additional configuration is required: IIS is configured as an unsecured http site.

 

Resources