HTTP Access to Analysis Services (multidimensional)
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
- Configure HTTP Access to Analysis Services on IIS 8.0 – the Microsoft article for an earlier IIS.
- Configuring HTTP for SSAS Bob Kerr’s video differs from MS in: creating a separate site in IIS, placing msmdpump.dll and related items in the root virtual folder and setting a default document allowing a simple URL to be used.