Accessing SQL Server Analysis Services via HTTP

I have set up HTTP access to SQL Server Analysis Server a couple of times. It is well documented here: http://technet.microsoft.com/en-us/library/gg492140.aspx

But each time, I seem to encounter the following error when I try to add the script map: “The specified module required by this handler is not in the modules list. If you are adding a script map handler mapping, the IsapiModule or the CgiModule must be in the modules list.”

image

Here is how to solve this: In Windows Server 2012, add the ISAPI Extension feature using the Add Roles and Features Wizard (which is accessible from Server Manager):

image

Here is a PowerShell script that automates the steps 1-2 outlined in the Technet article above. (I ran into problems adding the script map using PowerShell.)

# Prerequisites: Windows PowerShell Snap-In for IIS 7.0

$filepath="C:\inetpub\wwwroot\OLAP"
$olapisapipath="C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\bin\isapi"

New-Item -Path $filepath -ItemType directory
Copy-Item -Path "$olapisapipath\*" -Destination $filepath -Recurse
Import-Module WebAdministration
New-Item IIS:\AppPools\OLAP
Set-ItemProperty IIS:\AppPools\OLAP -Name managedPipelineMode -value Classic
Set-ItemProperty IIS:\AppPools\OLAP -Name managedRuntimeVersion -value v2.0
New-Item "IIS:\Sites\Default Web Site\OLAP" -type Application -physicalPath $filepath
Set-ItemProperty "IIS:\Sites\Default Web Site\OLAP" -Name applicationPool -Value OLAP

Access Permissions to SSAS

IIS must be granted access to query the SSAS database. If you’re using authentication, it is the IIS AppPool account that should be granted access. If you’re using anonymous, you can check the account by selecting Anonymous Authentication in IIS Manager and clicking Edit. If your IIS server is separate from your SSAS server, you must change this to a domain account.

I have put an XMLA script to create an appropriate role in SSAS on OneDrive: https://onedrive.live.com/redir?resid=DD9BE5E7B16098D7%2114485

Advertisements

One thought on “Accessing SQL Server Analysis Services via HTTP”

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