Jaa

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

  • Artikkeli

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">https://<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
    image
  • 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.

Comments

  • Anonymous
    September 05, 2011
    Hi, Is it possible to restrict the database that we use? I.e., is there a way to connect directly to a specific database and not allow the end-user to change the database. This would be very usefull when using ASLB (see sqlcat.com for more info on ASLB). Thanks, Tiago.

  • Anonymous
    September 05, 2011
    Hi, Is it possible to restrict the database that we use? I.e., is there a way to connect directly to a specific database and not allow the end-user to change the database. This would be very usefull when using ASLB (see sqlcat.com for more info on ASLB). Thanks, Tiago.

  • Anonymous
    September 05, 2011
    Hi, Is it possible to restrict the database that we use? I.e., is there a way to connect directly to a specific database and not allow the end-user to change the database. This would be very usefull when using ASLB (see sqlcat.com for more info on ASLB). Thanks, Tiago.

  • Anonymous
    November 21, 2012
    Hi there, Neither I nor anybody in my office has the directory C:Program FilesMicrosoft SQL ServerMSAS10_50.MSSQLSERVEROLAPbinisapi in our SQL Server 2008 installations. Specifically, the MSAS10_50.MSSQLSERVER folder is missing from the tree and none of the other files exist. No one and no website anywhere can explain to me why this is or how to fix it, but its ripping my hair out and makes configuring the SQL Server Analysis Services impossible. Please help.