How Configure HTTP Access to Analysis Services on Internet Information Services from Powershell

Introduction

“You can enable HTTP access to Analysis Services by configuring MSMDPUMP.dll, an ISAPI extension that runs in Internet Information Services (IIS) and pumps data to and from client applications and an Analysis Services server. This approach provides an alternative means for connecting to Analysis Services when your BI solution calls for the following capabilities:

• Client access is over Internet or extranet connections, with restrictions on which ports can be enabled.

• Client connections are from non-trusted domains in the same network. {…}"

The text above, is the way of the “Configure HTTP Access to Analysis Services on Internet Information Services (IIS)” guide start. Such steps are typical to setup the development environment or production server of a BI solution that move data over HTTP directly from an instance of SQL Server Analysis Services.

Repeat these steps over and over again encourage me to write the script to automate most of the steps of this guide with Powershell.

The Code

So, without more introduction here is the source:

1) Import or load the WebAdministration Module or PSSnapin (depends on IIS version)

$webAdminModule = Get-Module -ListAvailable | Where-Object { $_.Name -eq "WebAdministration" }
if ($webAdminModule -ne $null) 
{
    Write-Host "Importing WebApplication Module..."
    Import-Module -ModuleInfo $webAdminModule
}
else
{
    Write-Host "Loading WebApplication PSSnapin..."
    Add-PSSnapin WebAdministration
}

2) Create and setup application pool

Write-Host "Creating application pool OLAP..."
New-WebAppPool -Name OLAP -ErrorAction SilentlyContinue
Set-ItemProperty IIS:\AppPools\OLAP -Name ManagedRuntimeVersion -Value v2.0.50727
Set-ItemProperty IIS:\AppPools\OLAP -Name ManagedPipelineMode -Value 1

3) Set application pool credentials

Write-Host "Enter application pool credentials"
$userName = Read-Host "UserName" 
$securedPassword = Read-Host "Password" -AsSecureString
$userName = $credential.UserName
$password = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($securedPassword));
Set-ItemProperty IIS:\AppPools\OLAP -Name ProcessModel -Value @{ userName="$userName"; password="$password"; IdentityType=3 }

3) Create the web application

Write-Host "Creating OLAP Application in Default Web Site..."
New-Item 'IIS:\Sites\Default Web Site\OLAP' -Type Application -PhysicalPath "C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\bin\isapi"
Set-ItemProperty "IIS:\Sites\Default Web Site\OLAP" -Name ApplicationPool -Value OLAP
Set-WebConfiguration system.web/authentication 'IIS:\Sites\Default Web Site\OLAP' -value @{ mode = 'None' }

4) Add the handler mapping (it can't be done with the available command-lets, so)

Write-Host "Adding the handler mapping..."
[System.Reflection.Assembly]::LoadFrom("C:\windows\system32\inetsrv\Microsoft.Web.Administration.dll")
$isapiPath = "C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\bin\isapi\msmdpump.dll"
$isapiConfiguration = Get-WebConfiguration "/system.webServer/security/isapiCgiRestriction/add[@path='$isapiPath']/@allowed"  
if (-not($isapiConfiguration -eq $null -or $isapiConfiguration.value))
{  
    Write-Host "Enabling ISAPI Module - $isapiPath"
    Set-WebConfiguration "/system.webServer/security/isapiCgiRestriction/add[@path='$isapiPath']/@allowed" -value "True" -PSPath:IIS:\  
}

$serverManager = New-Object Microsoft.Web.Administration.ServerManager
if ($isapiConfiguration -eq $null)
{
    Write-Host "Adding and enabling ISAPI Module - $isapiPath"
    $appHostConfig = $serverManager.GetApplicationHostConfiguration();
    $isapiCgiRestrictionSection = $appHostConfig.GetSection("system.webServer/security/isapiCgiRestriction");
    $isapiCgiRestrictionCollection =  $isapiCgiRestrictionSection.GetCollection();
    
    $cgiRestrictionElement = $isapiCgiRestrictionCollection.CreateElement("add");
    $cgiRestrictionElement.SetAttributeValue("path", "C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\bin\isapi\msmdpump.dll");
    $cgiRestrictionElement.SetAttributeValue("allowed", "true");
    $cgiRestrictionElement.SetAttributeValue("groupId", "");

    $isapiCgiRestrictionCollection.AddAt(0, $cgiRestrictionElement);
}

$webConfig = $serverManager.GetWebConfiguration("Default Web Site", "OLAP");
$handlersSection = $webConfig.GetSection("system.webServer/handlers");
$handlersCollection = $handlersSection.GetCollection();

$handlerElement = $handlersCollection.CreateElement("add");
$handlerElement.SetAttributeValue("name", "OLAP");
$handlerElement.SetAttributeValue("path", "msmdpump.dll");
$handlerElement.SetAttributeValue("verb", "*");
$handlerElement.SetAttributeValue("modules", "IsapiModule");
$handlerElement.SetAttributeValue("scriptProcessor", "C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\bin\isapi\msmdpump.dll");

$handlersCollection.AddAt(0, $handlerElement);

$serverManager.CommitChanges();

The main differences with the original non-automated guide are: 

• Doesn’t create a copy of the files in ‘C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\bin\isapi’ to a ‘c:\inetpub\wwwroot\olap’ just publishes directly the original directory as application. Our deployments (production or workstation) typically involve a single instance of SQL Server Analysis Service.
• Doesn’t modify the archive msmdpump.ini because the default configuration is enough.
• Doesn't grant data access permissions. Assumes that the  application pool’s credentials have the right data access permissions.

Conclusions

I’m agree with you. This script can be more parameterized. But with this simple “coded” blog post I just want to send you a message. 

When you have to face automation tasks of your daily work follow this tips:

• Just do a small step at once.
• Don’t add complexity to the solution more than your needs.
• Don’t overwhelm you, do it for fun. 
• The code will be better tomorrow. 
• One day as result of your small improvements the solution will look just like you ever wanted. 

But if I you have something to automate just start NOW ;-).