Category Archives

This post is #14 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.

In this series:

Start of series – introduction and layout of subjects
Post #2 – Preparation: install files using Azure disk
Post #3 – Preparation: install files using Azure File Service
Post #4 –Preparation: logging infrastructure
Post #5 – Master script
Post #6 – Disabling Internet Explorer Enhanced Security Configuration
Post #7 – Active Directory setup
Post #8 – Configuring Password policy
Post #9 – Installing System Center Endpoint Protection
Post #10 – Installing SQL Server
Post #11 – Installing SharePoint Server
Post #12 – Installing PowerPivot for SharePoint
Post #13 – Configuring PowerPivot for SharePoint

Wow. This has been a long and wild ride. But you and I made it together. We now have the full recipe to automatically configure a Microsoft BI demo machine with PowerShell. Of course there is more to be done, such as configuring other Service Accounts and deploying demo content; this script however saves me a lot of time every time I need to stand up a new demo machine.

You can download the script on Github. Please note (again) that the code is provided as-is and you should use it at your own risk. It is probably still buggy but should give you a good starting point to adapt it to your needs.

I enjoyed the ride with you; hope I made your life a bit easier of the course of this series. Enjoy!

This post is #13 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.

In this series so far:

Start of series – introduction and layout of subjects
Post #2 – Preparation: install files using Azure disk
Post #3 – Preparation: install files using Azure File Service
Post #4 –Preparation: logging infrastructure
Post #5 – Master script
Post #6 – Disabling Internet Explorer Enhanced Security Configuration
Post #7 – Active Directory setup
Post #8 – Configuring Password policy
Post #9 – Installing System Center Endpoint Protection
Post #10 – Installing SQL Server
Post #11 – Installing SharePoint Server
Post #12 – Installing PowerPivot for SharePoint

Now that PowerPivot for SharePoint has been installed, we need to configure it. I split the configuration into two parts since we need a reboot in between and used MSDN for reference: http://msdn.microsoft.com/en-us/library/hh230903.aspx.

Step A: configuring SharePoint and deploying PowerPivot features

In Post #11 we talked about installing SharePoint, but the actual SharePoint provisioning was not done then. We will do it here in one go with installing PowerPivot features.

Function ConfigurePowerPivot
{
    Param(
        [Parameter(Mandatory=$true,HelpMessage="Passphrase required")]
        [ValidateNotNullOrEmpty()]
        $passphrase,
        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        $Password
    )
    Write-Host "Step 8: Configure PowerPivot"
    try {
    #Load Configure PowerPivot ps1
    $scriptPath = Split-Path -parent $global:script
    . ('C:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools\SPAddinConfiguration\Resources\ConfigurePowerPivot.ps1')
    
    #Create a user for SharePoint DB connection
    #if required, remove the ad user
    Get-ADUser -Filter {Identity -eq '$global:spAccount'} | Remove-ADUser
    CreateServiceAccount -AccountName $global:spAccount -DisplayName "SharePoint Farm account" -Description "Account for SharePoint Farm" -Path $global:path -Password $Password
    $spAccountFQ = $global:domainpart+"\"+$global:spAccount
    $pwd = convertto-securestring $Password -asplaintext -force
    & "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\BIN\psconfig.exe" -cmd configdb -create -server $global:HostName -database 'SharePoint_Config' -user $spAccountFQ -password $Password -passphrase $passphrase -admincontentdatabase 'SharePoint_Admin' -cmd helpcollections -installall -cmd secureresources -cmd services -install -cmd installfeatures -cmd adminvs -provision -port 2000 -windowsauthprovider onlyusentlm -cmd applicationcontent -install -cmd quiet 
    Add-PSSnapin Microsoft.SharePoint.PowerShell
    Add-SPSolution -LiteralPath 'C:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools\SPAddinConfiguration\Resources\powerpivotfarmsolution.wsp'
    Add-SPSolution -LiteralPath 'C:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools\SPAddinConfiguration\Resources\PowerPivotFarm14Solution.wsp'
    Add-SPSolution -LiteralPath 'C:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools\SPAddinConfiguration\Resources\powerpivotwebapplicationsolution.wsp'
    DeployFarmSolution $false
    DeployWebAppSolutionToCentralAdmin $false
    Install-SPFeature -path PowerPivotFarm -Force
    Install-SPFeature -path PowerPivotFarm -Force -CompatibilityLevel 14
    Install-SPFeature -path PowerPivotCA -Force
    InstallSiteCollectionFeatures
    
    Write-Host "PowerPivot Part 1 Configured. Computer needs to be restarted before PowerPivot configuration can continue." -ForegroundColor Green
    if ($global:DoAllTasks) {
        Set-Restart-AndResume $global:script "9"
        }

    }
    catch {
        Write-Host "Failed to configure PowerPivot. Error: $_.Exception.Message" -ForegroundColor Red
    }
}

Step B: updating farm credentials and starting service applications

After the PowerPivot features have been deployed we need to configure Service Applications to get PowerPivot to work.

Function ConfigurePowerPivotPart2 {
    Param(
        [Parameter(Mandatory=$true,HelpMessage="Passphrase required")]
        [ValidateNotNullOrEmpty()]
        $passphrase,
        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        $Password
    )

    try {
     #Load Configure PowerPivot ps1
    $scriptPath = Split-Path -parent $global:script
    . ('C:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools\SPAddinConfiguration\Resources\ConfigurePowerPivot.ps1')
    Add-PSSnapin Microsoft.SharePoint.PowerShell
    Write-Host "DEBUG: updating Farm Credentials"
    $spAccountFQ = $global:domainpart+"\"+$global:spAccount
    stsadm.exe -o updatefarmcredentials -userlogin $spAccountFQ -password $Password
    Write-Host "DEBUG: New-PowerpivotSystemServiceInstance"
    New-PowerPivotSystemServiceInstance -Provision:$true
    Write-Host "DEBUG: New-PowerPivotServiceApplication"
    New-PowerPivotServiceApplication -ServiceApplicationName 'PowerPivot Service Application' -DatabaseServerName $global:HostName -DatabaseName 'PowerPivotServiceApplication' -AddToDefaultProxyGroup:$true
    Write-Host "DEBUG: Set-PowerPivotSystemService"
    Set-PowerPivotSystemService -Confirm:$false
    
    Write-Host "DEBUG: Creating user DefAppPool"
    $appAccountName = "DefAppPool"
    $appAccountNameFQ = $global:domainpart+"\"+$appAccountName
    CreateServiceAccount -AccountName $appAccountName -DisplayName "Default Application Pool" -Description "Service Account for Default Application Pool" -Path $global:path -Password $Password
    Write-Host "DEBUG: CreateWebApplication"
    CreateWebApplication 'SharePoint - 80' $global:HostName 'Default Application Pool' $appAccountNameFQ $pwd $global:HostName 'DefaultWebApplication'
    Write-Host "DEBUG: DeployWebAppSolution"
    DeployWebAppSolution $global:httpHostName 2047 $false
    Write-Host "DEBUG: New-SPSite"
    New-SPSite -Url $global:httpHostName -OwnerEmail 'me@example.com' -OwnerAlias $global:currentUserName -Template 'PowerPivot#0' -Name  'PowerPivot Site'
    Write-Host "DEBUG: EnableSiteFeatures"
    EnableSiteFeatures $global:httpHostName $true
    Write-Host "DEBUG: StartService SPWindowsTokenServiceInstance"
    StartService "Microsoft.SharePoint.Administration.Claims.SPWindowsTokenServiceInstance"
    Write-Host "DEBUG: StartSecureStoreService"
    StartSecureStoreService
    Write-Host "DEBUG: CreateSecureStoreApplicationService"
    CreateSecureStoreApplicationService $global:HostName 'Secure Store Service'
    Write-Host "DEBUG: CreateSecureStoreApplicationServiceProxy"
    CreateSecureStoreApplicationServiceProxy 'Secure Store Service' 'Secure Store Proxy'
    Write-Host "DEBUG: UpdateSecureStoreMasterKey"
    UpdateSecureStoreMasterKey 'Secure Store Proxy' $passphrase 
    Write-Host "DEBUG: CreateUnattendedAccountForDataRefresh"
    CreateUnattendedAccountForDataRefresh $global:httpHostName 'PowerPivotUnattendedAccount' 'PowerPivot Unattended Account for Data Refresh' $spAccountFQ $pwd
    Write-Host "DEBUG: StartService ExcelServerWebServiceInstance"
    StartService "Microsoft.Office.Excel.Server.MossHost.ExcelServerWebServiceInstance"
    Write-Host "DEBUG: New-SPExcelServiceApplication"
    New-SPExcelServiceApplication -name 'ExcelServiceApp1' -Default -ApplicationPool 'SharePoint Web Services System' | Get-SPExcelServiceApplication | Set-SPExcelServiceApplication | iisreset Set-SPExcelFileLocation -ExternalDataAllowed 2 -WorkbookSizeMax 200 -WarnOnDataRefresh:$false -ExcelServiceApplication 'ExcelServiceApp1' -identity 'http://'
    Write-Host "DEBUG: AddExcelBIServer"
    AddExcelBIServer
    Write-Host "DEBUG: SetECSUsageTracker"
    SetECSUsageTracker 'ExcelServiceApp1'
        
    Write-Host "PowerPivot Configured" -ForegroundColor Green
    if ($global:DoAllTasks) {
        Set-Restart-AndResume $global:script "10"
        }

    }
    catch {
        Write-Host "Failed to configure PowerPivot. Error: $_.Exception.Message" -ForegroundColor Red
    }

}

Now we have seen all the steps required to build a Microsoft BI demo machine! The next post will serve as a wrap up and present a download for the full script.

This post is #12 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.

In this series so far:

Start of series – introduction and layout of subjects
Post #2 – Preparation: install files using Azure disk
Post #3 – Preparation: install files using Azure File Service
Post #4 –Preparation: logging infrastructure
Post #5 – Master script
Post #6 – Disabling Internet Explorer Enhanced Security Configuration
Post #7 – Active Directory setup
Post #8 – Configuring Password policy
Post #9 – Installing System Center Endpoint Protection
Post #10 – Installing SQL Server
Post #11 – Installing SharePoint Server

Ok, now that both SQL Server and SharePoint Server are installed, we just need to set up PowerPivot for SharePoint and configure it. Easy huh? Well, it turns out it is pretty difficult to get it right. Installation is not difficult (this post) but the configuration is harder (the next post). Here is how to install PowerPivot. I used MSDN for the info: http://msdn.microsoft.com/en-us/library/ee210645.aspx.

Installing PowerPivot involves mounting the SQL Server Installation Media and calling the setup with the right parameters.

Function InstallPowerPivot
{
Param(
        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        $Password
)
    Write-Log -Verbose  "Step 7: Install PowerPivot"
    #MOUNT SQL ISO
    $mountresult = Mount-DiskImage -ImagePath $global:pathToSQLISO -PassThru
    $driveLetter = ($mountresult | Get-Volume).DriveLetter
    $setupFile = $driveLetter+":\setup.exe"
    #Remove Service Account if it already existed
    Get-ADServiceAccount -Filter {Name -eq 'PP'} | Remove-ADServiceAccount
    $ppAccountName = "PP"
    $ppAccountNameFQ = $global:domainpart+"\"+$ppAccountName
    CreateServiceAccount -AccountName $ppAccountName -DisplayName "PowerPivot" -Description "Service Account for PowerPivot for SharePoint" -Path $global:path -Password $Password
    #do PP installation
    #trying with plain text pwd in call
    $process = Start-Process -NoNewWindow -Wait $setupFile -ArgumentList "/ACTION=INSTALL /IACCEPTSQLSERVERLICENSETERMS /Q /INSTANCENAME=POWERPIVOT /ERRORREPORTING=1 /SQMREPORTING=1 /ASSVCACCOUNT=$ppAccountNameFQ /ASSVCPASSWORD=$Password /ASSYSADMINACCOUNTS=$global:currentUserName /ROLE=SPI_AS_ExistingFarm"
    #SPI_AS_ExistingFarm
    
    #dismount
    Dismount-DiskImage -ImagePath $global:pathToSQLISO
    Write-Log -Verbose  "If above an error is shown please check out C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\Summary.txt"
    Write-Log -Verbose  "PowerPivot Installed"
    if ($global:DoAllTasks) {
        Set-Restart-AndResume $global:script "9"
        }
}

Next time: configuring PowerPivot.

This post is #11 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.

In this series so far:

Start of series – introduction and layout of subjects
Post #2 – Preparation: install files using Azure disk
Post #3 – Preparation: install files using Azure File Service
Post #4 –Preparation: logging infrastructure
Post #5 – Master script
Post #6 – Disabling Internet Explorer Enhanced Security Configuration
Post #7 – Active Directory setup
Post #8 – Configuring Password policy
Post #9 – Installing System Center Endpoint Protection
Post #10 – Installing SQL Server

Wow, so the last post was pretty intense, wasn’t it? I think we are ready for the next one: installing SharePoint. To build this script I used the following sources: http://social.technet.microsoft.com/wiki/contents/articles/14582.sharepoint-2013-install-prerequisites-offline-or-manually-on-windows-server-2012-a-comprehensive-guide.aspx#Installing_the_Roles_and_Features_for_SharePoint_2013_on_Windows_Server_2012_Offline_with_PowerShell and http://blogs.msdn.com/b/uksharepoint/archive/2013/03/18/scripted-installation-of-sharepoint-2013-and-office-web-apps-server-from-the-field-part-2.aspx.

Since this is again a quite lengthly script we will split it up in steps.

Step A: enabling IIS and other features

This step enables a whole load of features on Windows that are required by SharePoint, including IIS. If a restart is required, the script will reboot after the setup of the features. Some times your machine might reboot more than once to complete the setup of all these features.

Write-Log -Verbose  "Step 6: Install SharePoint"
Import-Module ServerManager
#Add .Net 4.5 features
Add-WindowsFeature NET-WCF-HTTP-Activation45,NET-WCF-TCP-Activation45,NET-WCF-Pipe-Activation45
#Add the rest of the needed features for IIS role
$result = Add-WindowsFeature Net-Framework-Features,Web-Server,Web-WebServer,Web-Common-Http,Web-Static-Content,Web-Default-Doc,Web-Dir-Browsing,Web-Http-Errors,Web-App-Dev,Web-Asp-Net,Web-Net-Ext,Web-ISAPI-Ext,Web-ISAPI-Filter,Web-Health,Web-Http-Logging,Web-Log-Libraries,Web-Request-Monitor,Web-Http-Tracing,Web-Security,Web-Basic-Auth,Web-Windows-Auth,Web-Filtering,Web-Digest-Auth,Web-Performance,Web-Stat-Compression,Web-Dyn-Compression,Web-Mgmt-Tools,Web-Mgmt-Console,Web-Mgmt-Compat,Web-Metabase,Application-Server,AS-Web-Support,AS-TCP-Port-Sharing,AS-WAS-Support, AS-HTTP-Activation,AS-TCP-Activation,AS-Named-Pipes,AS-Net-Framework,WAS,WAS-Process-Model,WAS-NET-Environment,WAS-Config-APIs,Web-Lgcy-Scripting,Windows-Identity-Foundation,Server-Media-Foundation,Xps-Viewer
if($result.RestartNeeded -eq "Yes")
{
    Set-Restart-AndResume $global:script "7"
}

Step B: Installing SharePoint Prerequisites

SharePoint itself has a number of prerequisites; in this still we will install them all.

#Mount SharePoint iso
$mountresult = Mount-DiskImage -ImagePath $global:pathToSharePointISO -PassThru
$driveLetter = ($mountresult | Get-Volume).DriveLetter
Write-Log -Verbose  "Installing SharePoint PreReqs...."
$setupFile = $driveLetter+":\prerequisiteinstaller.exe"
$process = Start-Process $setupFile -PassThru -Wait -ArgumentList "/unattended /SQLNCli:$global:SharePoint2013Path\PrerequisiteInstallerFiles\sqlncli.msi /IDFX:$global:SharePoint2013Path\PrerequisiteInstallerFiles\Windows6.1-KB974405-x64.msu /IDFX11:$global:SharePoint2013Path\PrerequisiteInstallerFiles\MicrosoftIdentityExtensions-64.msi /Sync:$global:SharePoint2013Path\PrerequisiteInstallerFiles\Synchronization.msi /AppFabric:$global:SharePoint2013Path\PrerequisiteInstallerFiles\WindowsServerAppFabricSetup_x64.exe /KB2671763:$global:SharePoint2013Path\PrerequisiteInstallerFiles\AppFabric1.1-RTM-KB2671763-x64-ENU.exe /MSIPCClient:$global:SharePoint2013Path\PrerequisiteInstallerFiles\setup_msipc_x64.msi /WCFDataServices:$global:SharePoint2013Path\PrerequisiteInstallerFiles\WcfDataServices.exe"

Step C: Installing SharePoint

SharePoint is installed in this step from the ISO that the script mounts.

if($process.ExitCode -eq 0) {
        #install sharepoint
        Write-Log -Verbose  "Installing SharePoint...."
        $path = $driveLetter+":\Setup.exe"
        $sharePointInstallProcess = Start-Process -Wait -PassThru $path -ArgumentList "/config $global:SharePoint2013Path\FarmSilentConfig.xml"
        switch($sharePointInstallProcess.ExitCode)
        {
            0 {
                Write-Log -Verbose  "SharePoint successfully installed"
                Write-Log -Verbose  "SharePoint Installed"
                if ($global:DoAllTasks) {
                    Set-Restart-AndResume $global:script "8"
                    }
            }
            default{
                Write-Log -Verbose  "An error has occured in installing SharePoint. Code: " $sharePointInstallProcess.ExitCode
            }
        }
    }
    else {
        if($process.ExitCode -eq 3010) {
            Write-Log -Verbose  "SharePoint prereqs install requires a reboot"
        }
        else {
            Write-Log -Verbose  "SharePoint prereqs not succesfully installed, please investigate.  Code: " $process.ExitCode
        }
    }

Step D: Cleaning up

This step simply unmounts the SharePoint installation media.

Dismount-DiskImage -ImagePath $global:pathToSharePointISO

Pff, are we done yet? No! Next up: Installing PowerPivot for SharePoint.