Making SQL Server Analysis Services Accessible via HTTP (or HTTPS)

Yes this is possible. It is a scenario –at least is seems appealing to me- suitable for accessing SSAS from machines that are not in the same domain as the SSAS. SSAS understands windows users only (there is no such thing as SQL Server Authenticated users). So in order to access a SSAS instance you should either be a member of the domain SSAS belongs to or use a pass through logic via local accounts (i.e. use local SSAS machine account with the same username & password as your own).

However there is another option – also suitable for SQL Server.

You may use the msmdpump.dll as the conduit for your communication. Just create a web site the “exposes” this dll and use a connection string  (from your office client not a from a browser) like this /msmdpump.dll">HTTP://<SSASMACHINE>/msmdpump.dll .

Here it the process (IIS 7 or higher)

  • Create a new site
  • Copy the msmdpump.dll in the site. Typically located under C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\bin\isapi
  • Add a Script Map in this site with the DLL, name the map as you please –my option is typically olap. Click on the site, double click the “Handler Mapping” icon and select “Add script map…” from the actions at the top left corner
  • Click OK and Yes when prompted.
  • Now you have the option to use the anonymous access to this web site and impersonate any valid user in the SSAS or just keep the windows integrated authentication behavior. The first is a wide open hole in the security – use it only when no other option can work – it is not recommended.

It’s pretty easy to do and it can give accessibility to office clients outside the SSAS windows authentication territory. Particularly useful when business people would like to put their hands on your dev/test environment to test cube data.