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