Error creating PowerPivot Service Application in SharePoint 2010 (SP1 + June 2011 CU)

<< SharePoint 2010 Not Indexing OneNote 2010 files | Home | Error when trying to access SharePoint 2010 via PowerShell in a new environment >>

Error creating PowerPivot Service Application in SharePoint 2010 (SP1 + June 2011 CU)

A few weeks ago I ran into an error while setting up our test farm. I was creating the service applications and when I got to PowerPivot, I got the following error:

$Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' during PowerPivot Service application creation$

I was able to work around this error by using PowerShell to create the service application.

Background

First, some details of our installation: two web front ends, two application servers, and one SQL server. All are running Windows Server 2008 R2 x64 with Service Pack1. The SQL Server is 2008 R2 with SP1. The SharePoint servers are SharePoint Server 2010 Enterprise, and I installed SharePoint’s Service Pack 1 and the June 2011 Cumulative Update (by running all of the installers before running the first Configuration Wizard).

Clean Up from the error

Once you run into the error, you’ll see that a new database was created and a phantom application pool was created. To clean these up:

Delete the database in SQL server (I did this just using SQL Studio)
Delete the database in SharePoint, using PowerShell: (Please note: this PowerShell line will delete any database entry in SharePoint where the actual database doesn’t exist in SQL)
```
Get-SPDatabase | Where{$_.Exists -eq $false} | ForEach {$_.Delete()} 
```
Now delete the phantom Application Pool (the error causes a service application pool to be created within SharePoint, but doesn’t actually create it in IIS):

Remove-SPServiceApplicationPool -Identity "My App Pool"

Just replace the “My App Pool” text with the name of the application pool you want to delete.

Create PowerPivot Service Application using PowerShell

Now, to create the PowerPivot service application, you can use the following PowerShell script:

   1:  $PowerPivotServiceName = "PowerPivot Service"

2:

   3:  Write-Host "Creating PowerPivot Service Application..."

   4:  New-PowerPivotServiceApplication -ServiceApplicationName $PowerPivotServiceName

       -DatabaseServerName "SQL.MYDOMAIN.LOCAL" -DatabaseName "Service_PowerPivot_1" -AddToDefaultProxyGroup

   5:  Write-Host "PowerPivot Service Application created"

6:

   7:  Write-Host "Creating Application Pool"

   8:  $AppPool = New-SPServiceApplicationPool -Name "AppPool_PowerPivot" -Account "DOMAIN\SERVICEACCOUNT_USERNAME"

   9:  Write-Host "App Pool created"

10:

  11:  Write-Host "Assigning PowerPivot Application Pool"

  12:  $sa = Get-PowerPivotServiceApplication | where {$_.DisplayName -eq $PowerPivotServiceName}

  13:  $sa.ApplicationPool = $AppPool;

  14:  $sa.Update();

  15:  Write-Host "PowerPivot Application Pool Assigned"

16:

  17:  Write-Host "Script Complete"

You’ll need to fill in a few parameters specific to your environment:

Line 4: DatabaseServerName, DatabaseName

Line 8: The Application Pool’s name and the service account that it will run as

Summary

Doing these steps should give you a working PowerPivot service application. We did open a support ticket with Microsoft. They confirmed that other customers have seen this error, but they are still researching the cause.