SSAS: Connecting via HTTP on Windows Vista

Microsoft has a couple of articles on how to set up HTTP connectivity for SSAS, one for Win XP http://www.microsoft.com/technet/prodtechnol/sql/2005/httpssas.mspx and another for Win2003 server http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx. But Vista Business/Ultimate includes the new version of IIS (IIS 7) which means some of the steps have changed a little. So let's walk through the process with the help of a few screen shots.

Getting binaries
Copy the contents of the %Installation folder%\OLAP\bin\isapi directory into the folder you would like to become the base for the virtual directory in IIS.

In this example, we are going to copy all the files from the C:\Program Files\Microsoft SQL Server\MSSQL.1\OLAP\bin\isapi folder into the C:\inetpub\wwwroot\olap directory.

Notes:  To take advantage of the full set of security settings, it is important to make sure that the folder to become the base for the virtual directory is located on the drive formatted for the NTFS file system.

Due to IIS limitations, the path to your directory should not contain spaces.

If you are planning to run the HTTP pump on a different server than the Analysis Services server, please make sure that you also install OLEDB for Analysis Redistributable package.

Creating an Application Pool

Once you have copied the files you need to open up IIS Manager, right click on the Application Pools node and choose the option to create an Application Pool.

 image_thumb61 

Then create an Application Pool, I don't think this is strictly necessary, I was also able to get this working with the default application pool, but it will help isolate the SSAS connectivity from any other applications.

image_thumb16

Right click on the folder that you created to hold all the binary files and convert it to an Application.

image_thumb12

This is where you get to set an alias and choose which application pool to use. Set the alias to the same name as the folder (it is less confusing this way) and set the application pool to use the one we just created.

 

image

Once the folder is tagged as an application we have a few more options that become available. Double click on the handler mappings.

image

In the top right corner of the Hander Mappings screen is the option to " Add Script Map..." - click on that.

image

Fill out the form with the details from the screen shot below.

image

When you hit OK to add the script mapping you will be asked if you want to enable the ISAPI extension, it is important to answer yes to this or nothing will work.

image

The last thing we need to do is to go back to the home screen for the "olap" application and choose an authentication scheme

image

The options are the same as described in the two articles referenced at the top of this posting, so I'm not going to repeat those details here. Once you have configured the authentication, the only thing that remains is to test the connection.

image

To test, I simple entered http://localhost/olap/msmdpump.dll as the server name when using SQL Server Management Studio.

image

 

Note: This post was "inspired" by a thread on the MSDN Analysis Services forum and it turned out on that thread that there was a problem with 64 bit IIS 7 being switched into running in 32 bit mode. If you are interested the full thread can be found here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1677855&SiteID=1

I was asked a little while ago, which software I used for my screen shots. The answer is that I am lucky enough to have received a complementary copy of Snagit which I really love. It makes it so easy to crop, markup and annotate screen shots.

Print | posted on Sunday, November 4, 2007 11:17 PM

Comments on this post

# re: SSAS: Connecting via HTTP on Windows Vista

Requesting Gravatar...
This whole configuration process is absurdly onerous - I don't know why SSAS doesn't natively support HTTP, the way SQL Server does (heck, XML/A is a SOAP based protocol!). I've created an enhancement request on connect for native support - go vote for it if you agree.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=303951
Left by Kevin Dente on Nov 06, 2007 4:40 AM

# re: SSAS: Connecting via HTTP on Windows 2008?

Requesting Gravatar...
I'm unable to get this to work in Windows 2008. It's almost similar in appearance but there must be a difference. Please help.
Left by gerry on Jun 10, 2008 6:06 PM

# re: SSAS: Connecting via HTTP on Windows Vista

Requesting Gravatar...
It works. I had to convert the 64bit msmdpump.dll to 32bit mode.
Left by gerry on Jun 20, 2008 11:34 AM

# re: SSAS: Connecting via HTTP on Windows Vista

Requesting Gravatar...
thanks a lot. with the help of your article, I have successly made SAAS2008 working in Windows Server 2008 Ent.

Vista and Windows Server 2008 both use IIS7, they are configed the same.

and, authentication is important to success, "The options are the same as described in the two articles referenced at the top of this posting, so I'm not going to repeat those details here."
so must refer to xp/2003 docs above carefully



Left by jbean on Oct 13, 2008 9:34 PM

# re: SSAS: Connecting via HTTP on Windows Vista

Requesting Gravatar...
How I can connect vi HTTP to the msmdpump.dll from another host? I only connect localy
Left by Mathias on Feb 21, 2009 7:29 AM

# re: SSAS: Connecting via HTTP on Windows Vista

Requesting Gravatar...
I am getting a prompt to save or open the msmdpump.dll file when I try to hit it in a browser. In IIS 6 you create a web service extension and allow the .dll but in the ISAPI and CGI Restrictions for IIS 7 I see that the msmdpump.dll is allowed. Does anyone know why I'm still getting the prompt to open or save the dll? I also tested the connection in Rex and it was not able to connect. Thanks in advance!
Left by Kamehameha on Mar 27, 2009 3:25 AM

# re: SSAS: Connecting via HTTP on Windows Vista

Requesting Gravatar...
@Kamehameha: It sounds like the "Handler Mapping" is not setup properly. This is what would tell IIS to execute the msmdpump.dll rather than just serving it up as a download.
Left by Darren Gosbell on Mar 27, 2009 6:39 AM

# re: SSAS: Connecting via HTTP on Windows Vista

Requesting Gravatar...
You write: «In this example, we are going to copy all the files from the C:\Program Files\Microsoft SQL Server\MSSQL.1\OLAP\bin\isapi folder into the C:\inetpub\wwwroot\olap directory.»

My SQL Server 2005 installation has an OLAP directory inside MSSQL.2 but not inside MSSQL.1.

Does this matter? What is the difference between the two MSSQL dirs?
Left by Magne on May 26, 2009 12:19 AM

# re: SSAS: Connecting via HTTP on Windows Vista

Requesting Gravatar...
@Magne - no the actual number does not matter. SQL 2005 numbers it's directories sequentially and it depends on which components you chose to install. Usually the database engine would be SQL.1 but if you did not install that on this server SSAS would become SQL.1
Left by Darren Gosbell on May 26, 2009 6:40 AM

# re: SSAS: Connecting via HTTP on Windows Vista

Requesting Gravatar...
I wanted to add in a comment on 64-bit vs. 32-bit. I am running Vista Business on a 64-bit machine. I kept getting "405.0 - Method not allowed" errors when trying the http URL to the msmdpump.dll. The error seemed as though it was not accepting the POST method, but in fact the issue was that my OLAP application pool should not be enabled for 32-bit applications. If you go to the advanced settings on the OLAP application pool, the 2nd setting from the very top is, "Enable 32-Bit Applications" and it must be set to "False" since I am running on a 64-bit machine. When I first read the info in the link at the end of this article, I did not get this. This should only affect the specific application pool, in this case "OLAP". Other app pools should be fine, I am guessing as I have not run into any other issues with other app pools *yet* on my machine.

- Andy T.
Left by Andy T. on Feb 25, 2010 2:18 AM

# re: SSAS: Connecting via HTTP on Windows Vista

Requesting Gravatar...
I have a slightly different situation but have followed the steps listed above. I am trying to connect to analysis server 2000(cant really upgrade at this point). I am using windows server 2008(32 bit). when I hit the dll in my browser, it also just gives me an option to save the file. Any ideas?
Left by Dunnomino on Jul 14, 2010 7:21 PM

# re: SSAS: Connecting via HTTP on Windows Vista

Requesting Gravatar...
Hello
i have successfully implement your article and it's pretty straigth forward. but i have one question, when i do test in SQL Server Management Studio, there is no analysis service shown. but when i do localhost in SQL Server Management Studio, there are some analysis service that i've made before. is it normal? or i need to configure something else?
thanks, and best regards
Left by elmana on Dec 27, 2010 1:13 PM

# re: SSAS: Connecting via HTTP on Windows Vista

Requesting Gravatar...
@elmana - yes this is normal. SSAS does not implement a "discovery" mechanism. The list is SSMS is just a history of servers that you have connected to in the past. If you just type in the http address it should work.
Left by Darren Gosbell on Dec 30, 2010 2:12 PM

# re: SSAS: Connecting via HTTP on Windows Vista

Requesting Gravatar...
Here you can fins another SSAS HTTP Access articles including this one.
http://ssas-wiki.com/w/Articles#HTTP_Access
Left by Sam Kane on Feb 18, 2011 7:57 AM

# re: SSAS: Connecting via HTTP on Windows Vista

Requesting Gravatar...
Here are this and some other articles on HTTP Access:

http://ssas-wiki.com/w/Articles#HTTP_Access
Left by Sam Kane on Mar 17, 2011 7:20 AM

Your comment:

 (will show your gravatar)