When trying to run the PowerPivot Configuration Tool for SQL Server 2012 SP1 with SharePoint 2010 on a Windows Server 2008 R2 server, have you ever seen this error?
“Current Analysis Services account: DOMAIN\account
Analysis Services service account must be the same for instances in the farm. Change you service properties to log on using account: NT AUTHORITY\LOCAL SERVICE
Plsease address the validation failures and try again”
Also shown in the Picture below:
I’ll try to sort this out for you by carrying out some actions.
The background story to this is that I previously tried to add PowerPivot directly to the Application/WFE server (in my 2-tier SharePoint environment). As this wasn’t successful and also the hardware on that server wasn’t to be upgraded (late decision) I wanted to add PowerPivot to a server outside of the farm. Of course I know that isn’t possible using SharePoint 2010 together with SQL Server 2012 SP1 so I firstly added this server as an Application server to the farm (for those that have added language packs to your SharePoint 2010 farm please install those to your new Application server as well Before trying to add it to your farm).
I looked into using Tyler Durham’s solution (http://tnbluesboy.wordpress.com/2012/04/23/issue-running-powerpivot-configuration-tool/) but to no joy since I already had uninstalled everything regarding my earlier PowerPivot installation attempt, which left me without Service Application or Windows Service – SQL Server Analysis Services in Central Administration. Since this type of solution seem to be the most referred to when googling or binging I was not to hopeful in finding any solution to my problem.
Disregard both of the sloppy written miss-spells (i.e. “Change you” instead of “Change your” and “Pslease” instead of “Please”) in the error message and carry on. 🙂
So… when reading the error message it comes clear that perhaps it would suffice with changing the service account for your PowerPivot SQL instance to use the NT AUTHORITY\LOCAL SERVICE (actual name for this service account is NT AUTHORITY\LocalService). That is NOT the case here 🙂
See the picture below for the result of the above action:
Evidently I.e. no change there!
I started thinking, and slowly it came to my mind that the installation media for the SQL Server 2012 wasn’t including the SP1 so there I had an opening to maybe clear out this error.
I updated the POWERPIVOT SQL instance by running the SP1 update. At the same time I copyied the spPowerPivot.msi file from that SP1-update-temporary-folder into a folder of my choosing.
When update was finished I then ran the spPowerPivot.msi file to install all necessary parts for PowerPivot on a SharePoint Application server.
The result of Action 3 was fortunate! I was able to start the PowerPivot Configuration Tool (Not the one for SharePoint 2013), and complete the configuration.
Hope this can help you out in implementing a PowerPivot for SharePoint backend!