Information, tips, news and announcements about SQL Server Analysis Services and PowerPivot directly from the product team.
The new SharePoint 2013 release is bringing lots of excitingnew things, and on this blog post we will go over what’s necessary to configurePowerPivot for SharePoint 2013 in a least-privileged accounts environment. Themain idea is to use different accounts for different purposes, but all of themto have the least amount of privilege as possible for the environment to work.This increases the security of the farm by restricting what each service canaccess.
The first noticeable difference for PowerPivot forSharePoint 2013 is that now you can configure the Analysis Services instanceprocess account to be the Network Service account. Note that this is notpossible when running any of the PowerPivot for SharePoint releases onSharePoint 2010, since SharePoint 2010 requires a domain account for AnalysisServices running inside the farm.
Briefly, the Network Service account is a special, built-inaccount that is similar to an authenticated user account. The Network Serviceaccount has the same level of access to resources and objects as members of theUsers group. This limited access helps safeguard your system if individualservices or processes are compromised. Services that run as the Network Serviceaccount access network resources using the credentials of the computer account.
Now off to the PowerPivot for SharePoint 2013 configuration.You will need at least three accounts to configure SharePoint 2013 andPowerPivot for SharePoint 2013 in a min-priv environment:
This account is the domain account you use to configure thefarm. I’ll reference it as the SPAdmin account. That is, it’s the account usedto run the configurations programs such as the SharePoint Configuration Wizard,the PowerPivot Configuration Tool for SharePoint 2013, psconfig.exe,stsadmin.exe, etc.
Let’s say you will be using the PowerPivot ConfigurationTool for SharePoint 2013 (from now on refered as the PowerPivot configurationtool). The SPAdmin account is the onlyaccount that requires local Administrator rights. Also, prior to running the PowerPivotConfiguration tool, you need to grant the SPAdmin account to the backend SQLServer database where SharePoint will place its databases. This minimumprivilege requirement for SPAdmin account in SQL Server is membership in the securityadmin and dbcreator roles.
This is the domain account that the SharePoint Timer serviceand the Web application for Central Administration use to access the SharePointcontent database. I’ll refer to it as the SPFarm account. This account does notneed to be a local administrator, and the PowerPivot Configuration tool willgrant the proper minimal privilege in the back end SQL Server database (whichis also securityadmin and dbcreator roles).
Here is where the extra work needs to be done. By default,if you are configuring a SharePoint farm from scratch, and by that I mean thatthere is no PowerPivot service, nor Excel Services nor Secure Store serviceapplication yet, the PowerPivot Configuration tool will create the PowerPivotService application along with the Excel Services application and Secure Storeapplication and place all of them in an existing application pool thatSharePoint created when the Central Administration Application is created bythe PowerPivot Configuration tool. This application pool runs as the SPFarmaccount, which has access to many resources that a service account does notrequire.
To make the environment a min-priv environment, you willneed to add a new domain account as a SharePoint Service account. This accounthas no local administrator privilege, neither any privilege in the back endSharePoint database. The only privilege this account requires is to be grantedadministrative rights to the PowerPivot Instance of the Analysis Services.
You can do that through the UI, through the CentralAdministration application > Security > Configure Service Accounts. Clickon the link Register new managed account.Let’s name it SPSvc.
Finally, after successfully registering the new SPSvcaccount, go back to Central Administration application > Security >Configure Service Accounts; chose theservice application pool where the PowerPivot Service application is; andselect the new SPSvc account from the drop-down menu.
After changing the service account, you need to grant itaccess to the web application. The way to do it is through the SharePoint 2013Management Shell. Run it as the administrator, and type the following PowerShellcode:
$webApp = Get-SPWebApplication "http://<servername>"
To make the environment even more secure, you could create anew application pool which only the PowerPivot service application would bepart of. And this pool can run as a different service account than the onerunning the Excel Services and Secure Store service applications. The twoservice accounts, the one running the PowerPivot Service application pool andthe one running the Excel Services application pool need administrator rightson the Analysis Services PowerPivot instance and be granted access to the webapplication (through the PowerShell cmdlets above).
SharePoint Administrator account
Backend Database roles:
Service Account for PowerPivot
Administrator at the Analysis Services PowerPivot instance
Thank you guys! Great!