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.


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"
   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" 
   7:  Write-Host "Creating Application Pool" 
   8:  $AppPool = New-SPServiceApplicationPool -Name "AppPool_PowerPivot" -Account "DOMAIN\SERVICEACCOUNT_USERNAME" 
   9:  Write-Host "App Pool created"
  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"
  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


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.

Print | posted @ Sunday, September 18, 2011 7:51 PM

Comments on this entry:

Gravatar # re: Error creating PowerPivot Service Application in SharePoint 2010 (SP1 + June 2011 CU)
by Duncan Sutcliffe at 9/30/2011 4:17 PM

Kelly, I think this is a Kerberos error (clue is in the NT AUTHORITY\ANONYMOUS LOGON failure message). If you allow your farm account (i.e. the account running Central Admin's web app pool) to delegate to the SQL Server that hosts the content and config databases, you don't seem to get this error.

I guess by doing it with PowerShell you don't need Kerberos because the PS command shell is running under your own context and is directly connecting to the database server, whereas doing it through Central Admin the connection is made with a double hop (you to web site to database server). And you normally wouldn't set up delegation from the farm account to SQL Server because there aren't any user circumstances you would need it.

Gravatar # re: Error creating PowerPivot Service Application in SharePoint 2010 (SP1 + June 2011 CU)
by Ron at 12/9/2011 10:37 AM

Thanks much for the script. I went through Duncan's instructions on his site which unfortunately require elevated AD privs to create the delegations. I didn't get far with that other than to get CA working with Kerberos. Your script saved the day! Now off to the next archane SPS configuration task!
Post A Comment