Checklist when configuring PowerPivot for SharePoint 2013

Hi everyone!

Currently I am in the middle of a deployment with PowerPivot in SharePoint 2013, for the sake of have as much new stuff as possible, at least from the SharePoint end. My scenario looks like this:

For the installation the high-level steps are:

1. Install a SQL Server Analysis Services for SharePoint in any server (doesn't need to be a farm server). This will install a SSAS instance named PowerPivot.
2. Install the PowerPivot add-in for SharePoint in all the farms.
3. Execute the PowerPivot configuration tool once in any of the servers.

So, the checklist itself to ensure your PowerPivot for SharePoint installation works smoothly:

Least-privilege user layout

If you want a very granular user account setup, you will need the following users:

• Setup: This will be local admin on every box in your farm.
• Excel Services: This can be shared for Visio Graph services as well for example.
• Unattended Account: For miscellaneous purposes, I would recommend one. This will be the user that will be impersonated when accessing external data through the "unattended account" in Excel, Visio, PerformancePoint, for instance. The idea of this user is to be different from any SharePoint managed account.
• Web Application Pool: For the intranet web application pool itself.
• SQL Server Service Account. SQL Server Analysis Services for SharePoint needs a domain account.
• Claims to Windows Token Service. This user needs to have the following privileges under the local policies/User rights assignments (secpol.msc): Act as part of the operative system, log on as a service, impersonate a client after authentication.
• Secure Store Service: This is just a recommendation, but a good one!
• Common Service Application Account: For example, for Managed Metadata, etc.

The mentioned users should be the minimal accounts. One of the main reasons for so many are the Kerberos delegation, you may want to just allow the Excel Service Account for Kerberos delegation, or the C2WTS (Claim to Windows Token Service) who needs specific privileges.

For SQL Server Analysis Services for SharePoint Installation

Ensure you install SQL Server 2012 with Service Pack 2, I've found an issue when trying to connect to the PowerPivot instance with the management studio that is solved in SP1/SP2. Follow the instruction on this article: Install Analysis Services Server in SharePoint Mode for SharePoint 2013 (SQL Server 2012 SP1). Two important points here:

• Add the excel service identity account as administrator in the PowerPivot instance, as well as the unattended (if you want one).
• The user that will execute the Analysis Service (PowerPivot) instance has to be a domain account.

For the SharePoint Servers Installation

• Ensure you have installed both the Microsoft® SQL Server® 2008 R2 ADOMD.NET and the Microsoft® SQL Server® 2008 R2 Analysis Management Objects from the Microsoft® SQL Server® 2008 R2 Feature Pack. Do this on every SharePoint server. (This may be not required in the future).
• Ensure the Secondary Logon service is up and running. See this for more information: https://msdn.microsoft.com/en-us/library/hh230899(v=sql.110).aspx
• Disable the loopback check on the web front end servers. TIP: I followed the article https://support.microsoft.com/kb/896861/en-us but the method 2 didn't work for me, only the method 1 (the one that creates the BackConnectionHostNames value). The catch on the first method is that you have to explicitly put the urls.

This, as mentioned earlier, have been tested over a Windows 2012 R2 installation.

Before executing the PowerPivot configuration tool

• Ensure you have the following Services Application up and running (with the users mentioned earlier)
  • Secure Store Service, with a master key already added
  • Excel Services
• For each web application, grant access to the Excel Service identity by using the following script:
  Get-SPWebApplication -IncludeCentralAdministration | % {$_.GrantAccessToProcessIdentity("your excel service account")}
• Ensure you have assigned the C2WTS user to the service under https://centraladminserver/_admin/FarmCredentialManagement.aspx, and started it on every SharePoint server in the farm.
• Add the PowerPivot instance to the excel service as mentioned in the article. Install Analysis Services Server in SharePoint Mode for SharePoint 2013 (SQL Server 2012 SP1). and wait for the job to add it. In my experience, sometimes it won't show any log unless you open a powerpivot file.

Testing the whole thing

There is a lot of ways to test this, but my way would be:

1. Test if a simple Excel Workbook can be viewed on your browser.
2. Create an Excel with PowerPivot data in it, with no external connections.
3. If you feel courageous enough, create a PowerPivot table from an external connection and test it. In this case ensure you configure the external data connection with the right  authentication settings.