Troubleshooting and Removing a Failed Installation or Upgrade of PowerPivot for SharePoint

I recently had the opportunity to perform an upgrade of PowerPivot from SQL Server 2008 R2 to SQL Server 2012 for a customer with a relatively large SharePoint farm. Their farm consisted of 4 SharePoint front end servers, 2 SharePoint application servers, and a SQL Server 2008 R2 cluster. The PowerPivot for SharePoint 2008 R2 service was installed on the 2 application servers. The upgrade didn’t go quite as smoothly as planned and in the process it was necessary to manually remove all traces of the PowerPivot 2008 R2 service. This was a little tricky (to say the least), so I thought that I’d share the experience here in hopes that it helps someone.

The original plan was to remove the PowerPivot service from the second server, then perform a SQL server upgrade on the remaining server, upgrading the PowerPivot for SharePoint instance. The PowerPivot configuration tool would then be run on the single server, upgrading the SharePoint elements. Finally, PowerPivot for SharePoint 2012 would be reinstalled on the second application server.

This is an approach that has worked quite well in the past, but in this particular environment, the configuration tool could not run the upgrade process. Due to the fact that there had been no work done on scheduling data refreshes thus far, it was decided that a complete removal, and install from scratch was in order. This is where things got particularly tricky. The configuration tool was unable to run its uninstall process, so I elected to remove everything manually, which consisted of deactivating the PowerPivot features in the destination site collections and from Central Admin, then retracting the PowerPivot solutions from Farm Solutions. Finally, SQL Server installation was run, and the PowerPivot instance removed from the server completely.

Removing PowerPivot Breaks Central Administration

At this point, Central Administration became unavailable. A quick search through the ULS logs for the correlation ID reported that there were errors loading the Microsoft.AnalysisServices.SharePoint.Integration assembly. As a next step, I ran through the SharePoint Configuration Wizard (psconfigui) to make sure that everything was OK. As it turns out, it wasn’t OK, and the wizard repeatedly failed.

image

After some searching, I located a TechNet article that pointed out the problem. Apparently with 2008 R2, PowerPivot has a little trouble uninstalling itself, and leaves a few artefacts. Two of the artefacts are the registry keys listed in the article, and when the configuration wizard sees them, it tries to load the assembly, which of course no longer exists, and then it fails out. Removing the two keys fixes this particular problem, and I’m reproducing the steps for doing so below for convenience.

Open the registry editor. To do this, type regedit in the Run command.
Expand HKEY_LOCAL_MACHINE
Expand SOFTWARE
Expand Microsoft
Expand Shared Tools
Expand Web Server Extensions
Expand 14.0
Expand WSS
Expand ServiceProxies
Right-click Microsoft.AnalysisServices.Sharepoint.Integration.MidTierServiceProxy and select Delete.
Go back a step in the hierarchy. Under WSS, expand Services
Right-click Microsoft.AnalysisServices.Sharepoint.Integration.MidTierServicea and select Delete.

After getting rid of the registry entries, the configuration wizard completed successfully, and more importantly, Central Administration loaded up properly. In addition, to this, I also removed the same registry keys from the second application server in anticipation of a reinstall.

It was therefore time to reinstall PowerPivot. Installation from the SQL server media went fine (don’t forget to add all necessary accounts as administrators, particularly the account that is running the Analysis Services Windows service).

Parent Service Does Not Exist

However, re-running the PowerPivot configuration Tool resulted in the error “Cannot create the service instance because the parent Service does not exist”.  

image

This turned out to be a particularly vexing problem. The only guidance that I could find in the forums was incorrect (things like, “the Database engine must be installed with the PowerPivot instance”, which is patently false). I went to the source PowerShell scripts that register the service, and was able to run them, and register the service. The PowerPivot service then appeared under Services on Server, and I was even able to create a new PowerPivot service application thorough the Central Administration UI. However, attempting to access it resulted in an error (which I no longer have access to). Examining the ULS logs showed “access denied” attempting to connect to the Analysis Services instance.

In addition, subsequent attempts to run the PowerPivot configuration tool resulted in a perplexing error that stated that all servers in the farm must be running the same service account, and that they should all be changed to run as LOCAL SERVICE. This is particularly odd given that PowerPivot MUST be installed with a domain account, so this isn’t even possible.  As it turned out, this was a red herring, and occurred because I had incorrectly specified the credentials of the service account in my PowerShell scripts from above, and it had provisioned using the LOCAL SERVICE account. I was able to change the identity of the service through Central Administration in the Security section, by configuring the service accounts, and then the PowerPivot configuration tool was able to run to completion. However, the service application, and its proxy appeared as stopped in the Service Applications list.

Manual Uninstallation

Something was still amiss, and for the life of me I couldn’t figure out what it was. At this point, I engaged the PowerPivot support team. After eliminating a few other potential issues, we decided to manually uninstall PowerPivot and use a few clean-up tools afterward. I again deleted service applications, deactivated features, retracted and removed solutions, and uninstalled PowerPivot for SharePoint with the SQL Setup application. Once everything had been theoretically removed, I ran this PowerShell script (repeated below) provided by the Support team to force the removal of all remaining PowerPivot items from the farm.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction:SilentlyContinue

Function DeleteServiceAndInstances
{
    param($service)
    if($service)
    {
        foreach($instance in $service.Instances)
        {
            $instance.Delete()
        }
 
        $service.Delete()
    }
}

Function DeleteServiceApplications
{
    param($service)
    if($service)
    {
        foreach($instance in $service.Applications)
        {
            $instance.Delete()
        }
    }
}

Function DeleteServiceApplicationProxies
{
    param($proxy)
    if($proxy)
    {
        foreach($instance in $proxy.ApplicationProxies)
        {
            $instance.Delete()
        }
    }
}

Function DeletePowerPivotEngineServiceAndInstances
{
    $farm = Get-SPFarm
    $service = $farm.Services | where {$_.GetType().GUID -eq "324A283C-6525-43c8-806C-31D8C92D15B7"}
    DeleteServiceAndInstances $service
}

Function DeletePowerPivotMidTierServiceAndInstances
{
    $farm = Get-SPFarm
    $service = $farm.Services | where {$_.GetType().GUID -eq "35F084BE-5ED5-4735-ADAA-6DB08C03EF26"}
    foreach($job in $service.JobDefinitions)
    {
        $job.Delete()
    }

    DeleteServiceApplications $service    
    DeleteServiceAndInstances $service
}

Function DeletePowerPivotEngineServiceProxy
{
    $farm = Get-SPFarm
    $proxy = $farm.ServiceProxies | where {$_.TypeName -eq "Microsoft.AnalysisServices.SharePoint.Integration.EngineServiceProxy"}
    if ($proxy)
    {
        $proxy.Delete()
    }
}

Function DeletePowerPivotMidTierServiceProxy
{
    $farm = Get-SPFarm
    $proxy = $farm.ServiceProxies | where {$_.TypeName -eq "Microsoft.AnalysisServices.SharePoint.Integration.MidTierServiceProxy"}
    if ($proxy)
    {
        DeleteServiceApplicationProxies $proxy
        $proxy.Delete()
    }
}


DeletePowerPivotEngineServiceProxy
DeletePowerPivotMidTierServiceProxy
DeletePowerPivotEngineServiceAndInstances
DeletePowerPivotMidTierServiceAndInstances

This script ran successfully, and in theory should have removed all of the PowerPivot elements from the SharePoint farm. However, just to be sure, we ran the following SQL script (also provided by MS support) to look for any orphaned PowerPivot elements in the farm configuration database:

   SELECT Id, classid, parentid, name, status, version, properties
   FROM objects
   WHERE name like '%PowerPivot%'
   ORDER BY name

Lo and behold, there was still a record in the configuration database pointing to an instance that obviously no longer existed. Now, direct editing of the configuration databases is not something that anyone should do lightly, and it’s not generally supported. However, in a few cases, it’s the only option, and according to support, this was one of those cases. I therefore took a backup (ALWAYS take a backup first!) of the config database, and then ran the following SQL query to determine if the instance had any configuration dependencies.

SELECT * FROM Objects (NOLOCK) WHERE Properties LIKE ‘%GUID-found-in-earlier-query%'

The GUID is the value of the ID field found in the previous query for the orphaned item. In my case, there were no items returned, but if there were, the dependencies would need to be removed. If you find dependencies, you will need to contact support yourself, as there are additional complicating factors involved that will need to be evaluated by the product group.

Since I had no dependencies, I was safely able to delete the offending record.

   DELETE FROM objects WHERE name like '%PowerPivot%'

My Configuration Database was now clean.

The Return of the Parent Service Error

At this point, I reinstalled PowerPivot for SharePoint, and once again, ran the PowerPivot configuration tool. Unfortunately, I ran right into the “Parent Service Does Not Exist” error discussed above. This was frustrating, to say the least. After several choice words, and some sleep, I decided to do yet another uninstall/reinstall. This difference this time is that instead of retracting solutions manually, I would allow the configuration tool to do the uninstall for me. When I did, it generated an error when it attempted to uninstall a feature:

The feature with ID ‘1a33a234-b4a4-4fc6-96c2-8bdb56388bd5’ is still activated within this farm or stand alone installation. Deactivate this feature in the various locations where it is activated or use -force to force uninstallation of this feature.

This GUID is the PowerPivotSite feature, which was still activated at one of the site collections. I then used stsadm to force the uninstallation of the feature (I used stsadm simply because I’m a dinosaur, and know the switches off the top of my head. Obviously PowerShell would work just a well, if not better).

stsadm -o uninstallfeature -id ‘1a33a234-b4a4-4fc6-96c2-8bdb56388bd5’ -force

After forcibly removing the feature, I used the PowerPivot configuration tool to remove PowerPivot. Once removed, I ran it again to configure the PowerPivot instance, and this time, it completed successfully.

I did note one thing that I thought was odd, and haven’t been able to explain. On this last run of the configuration tool, the step that provisioned the PowerPivot service application took an inordinately long amount of time, about an hour. Not content to leave well enough alone, I deleted the service application, and re-ran the tool. This time it completed in under a minute. I only mention this in case someone tries this and gives up because they think the system has hung up.

Once I got the first server up and running, it was relatively simple to install PowerPivot for SharePoint 2012 on the second application server, and run the configuration tool, which did everything necessary to add the second application server back into the PowerPivot rotation.

After cleaning up the remaining configuration items common to PowerPivot for SharePoint, and cleaning up the Health Analyzer errors, PowerPivot now appears to be running smoothly on this farm.

17 thoughts on “Troubleshooting and Removing a Failed Installation or Upgrade of PowerPivot for SharePoint

  1. Pingback: Установка бизнес-аналитики SQL Server 2012 SP1 для SharePoint 2013 « Microsoft Business Intelligence

  2. Pingback: Fun with SharePoint 2013 BI: When ‘uninstall’ is not an option | Megan's Space

  3. Megan Brooks

    Thank you! I encountered similar issues after uninstalling and re-installing SharePoint 2013 in my development environment. The cleanup script you provided above enabled me to remove PowerPivot after it failed to install (after re-installing SP), but as above it still did not install correctly. The tool was, however, able to remove it this time successfully and then it installed after that.

  4. Liam Burford

    Much of this rings true for me too. I finally mamnged to address the meaningless ‘Parent Service Error’ after disconnecting from the farm, uninstalling Powerpivot (SQL), rebooting, reinstalling Powerpivot (SQL), uninstalling Powerpivot application (SQL Configuration tool-it still appeared even although I’d uninstalled Powerpivot!), closing the configuration tool, reconnecting Sharepoint to the farm, reopening the configuration tool and re-running the PowerPivot configuratyion tool. And yes step 5 took an age to complete. A truely ridiculous process especially as I wasn’t even working in a live environment!

  5. Kesh Nair

    Thank You mate!!! You were right on the money. I had the exact same problem and was able to resolve it with your solution.

  6. ThatGuyRyan

    I had this same issue but did not have to go to the lengths of deleting the orphaned object, although it still exists in my configdb. I rebuilt the SharePoint cache prior to using the deleteconfigurationobject switch with stsadm and was able to successfully provision the service / service application.

  7. Kelly Smith

    I used this PowerShell to delete my orphaned object, then was able to successfully run the PowerPivot Configuration Manager to uninstall PowerPivot. Just posting here in case it helps anyone else:

    $feature = Get-SPSolution | ? { $_.DisplayName -eq “powerpivotwebapplicationsolution.wsp” }
    $feature.Delete()

  8. James Boman

    Hey there – in exactly the same situation, and have found this post very helpful.

    Had the ‘Patent Service does not exist’ problem.
    Had orphaned items in the Config Database that needed removal
    As a precaution I have also refreshed the configuration cache on all the Farm machines (turn off timer, delete XML files, reset cache.ini to 1, restart timer service, etc. etc.)

    I am now waiting for the Powerpivot Configuration Tool to create the Service Application ….
    1 hour and counting…..
    Fingers crossed….

    J.

  9. James Boman

    3 Hours later – and no progress.

    Found this in the ULS log:

    From the PowerPivotConfigTool:
    Waiting for service application instance provisioning job to complete for service application with name ‘PowerPivot Service Application’ and type ‘Microsoft.AnalysisServices.SharePoint.Integration.GeminiServiceApplication’.

    Then from the timer service:
    The Execute method of job definition Microsoft.SharePoint.Administration.SPConfigurationRefreshJobDefinition (ID b6a3b65d-3e68-440c-8a5d-c457429ff7e6) threw an exception. More information is included below. Operation is not valid due to the current state of the object.

    Exception stack trace:
    at Microsoft.SharePoint.Administration.SPConfigurationDatabase.Microsoft.SharePoint.Administration.ISPPersistedStoreProvider.GetParent(SPPersistedObject persistedObject)
    at Microsoft.SharePoint.Administration.SPPersistedObject.get_Parent()
    at Microsoft.SharePoint.Administration.SPJobDefinition.get_Service()
    at Microsoft.SharePoint.Administration.SPTimerStore.RefreshTimer(List`1 newObjects, List`1 deletedObjects, Nullable`1 resumeJobId, Object& jobDefinitionsNew, Object& objectsDeleted)
    at Microsoft.SharePoint.Administration.SPConfigurationRefreshJobDefinition.Execute(Int64& newestVersion, Nullable`1 resumeJobId, Boolean& invalidated, Object& jobDefinitionsNew, Object& objectsDeleted)
    at Microsoft.SharePoint.Administration.SPTimerJobInvoke.InvokeConfigRefresh(TimerJobExecuteData& data, Int64& newestVersion, Int32& result, Boolean& invalidated, Object& jobDefinitionsNew, Object& objectsDeleted, Boolean& isServerBusy, Int32& recycleMode)

    So it doesn’t look good from here – any suggestions appreciated.

  10. James Boman

    I retraced my steps and left it overnight to create the service application.

    Took 10Hrs 22min to produce an error, but appears to have created the service application successfully.

    I have to say, this is the worst experience I have had with Microsoft software in a long time.

  11. John White Post author

    Yikes James – that’s a long time. Yeah – it certainly isn’t the prettiest. And when it goes sideways, it really goes sideways. It is getting better if that’s any consolation.

  12. James Boman

    Thanks, yes, I’m just glad your wrote down your experiences, and it did eventually install – as I was running out of options.

    I guess as everyone moves to the Cloud this problem will eventually disappear.

    J.

  13. Safiya

    Thank you John White. Your blog really helped me to set it up. The delete script from config db solved my issue.

  14. Michael

    SharePoint Online (Plan 2)

    Using PowerShell to enable the PowerPivot Feature (which is not a visible feature within this tenant).

    “Feature with Id ‘1a33a234-b4a4-4fc6-96c2-8bdb56388bd5’ is not installed in this farm, and cannot be added to this scope.”

    Any ideas?

  15. Emmanuel Issaly

    i admire your persistence, John 😉
    Article was very interesting, althought i certainly hope to avoid the issue. Another SQL query that saved me when psconfig crashed the farm (!) on a customer site, to identify any orphan objects in the config DB :

    use SharePoint_Config
    Select distinct Id from dbo.Objects where ParentId not in (Select Id from dbo.Objects)

    pretty straighforward, deleting the items saved the farm. They had no support to begin with, so nothing much was lost 😉

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.