Analysis Services via HTTP

UPDATED: Added more information on permissions.

It is pretty common policy to seperate data servers from web servers.  Microsoft may be making headway with making IIS more secure, but IT departments commonly forbid having the two on the same production server.

Microsoft has provided us with a very simple tool to use in this situation.  They call it the Analysis Services Data Pump.  This ISAPI extension has existed (I think) since SQL 2000, but I recall it had a different name.  It is a dll hosted by IIS that takes server requests on HTTP and forwards the requests to the Analysis Server.  The server responds to the dll with results and the results are forwarded back to the requestor.  Here is the link to the article that explains how to set it all up: http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx.

The only thing I would add is that the article has a lot of different directories they reference:  D:\tmp\isapi and C:\InetPut\wwwroot and C:\wwwroot.  All of these should be the same place.  I don’t see any reason that you couldn’t just create a virtual directory in IIS that points directly to %InstallPath%\OLAP.2\OLAP\bin\isapi so that you don’t have to make another copy of the dll.  The only reason I can think to make a copy is that it would isolate you from being affected by an update to SQL server if you didn’t want the file to be updated without you testing it.  I still don’t know if this is enough of a reason to make a copy.  Don’t forget that if you don’t copy into the wwwroot directory, you’ll need to make sure your access permissions are correct.  Anything that goes in the wwwroot folder, IUSR automatically has access to it.  If you don’t put it in there, (and you use anonymous access) you’ll have to give IUSR permission to the folder where it is.

I just set it up and it works great and was very easy to do.  If you work in an environment where port access requests are difficult and a web server already has HTTP ports open, this might be a good solution.

Advertisements

4 Responses to Analysis Services via HTTP

  1. Kevin Dente says:

    Although the tiering ability of MSMDPUMP is handy, I find it very odd that SSAS doesn’t support HTTP access natively. After all it’s already based on SOAP. And the Database Engine does support direct access over HTTP. Very strange.

  2. Neil Wood says:

    Because it’s more complicated than one would suspect?

    I believe it’s XMLA over HTTP that the data pump is implementing. The only other way to connect would be via TCP/IP (with ADO.NET, etc.).

    See here for an in depth discussion:
    http://www.sqljunkies.com/WebLog/mosha/archive/2005/12/02/as2005_protocol.aspx

  3. Ofer Gal says:

    Did you have success accessing a cube like this from excel?

    I tried it and it did not work even though I opened port 2725 (Office Web Components)

  4. Awilda says:

    At least you open port 2725, but in my case we have everything configured. I can access the cube from intranet,but over internet doesnt work. Everybody says to open port 2725 but that port is not listening, i dont know how to activate it. i can’t see it under netstat -ano. any clues?

    thanks in advance

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: