Power Pivot Authentication and Authorization
Applies To: SQL Server 2016
A Power Pivot for SharePoint deployment that runs within a SharePoint 2010 farm uses the authentication subsystem and authorization model provided by SharePoint servers. SharePoint security infrastructure extends to Power Pivot content and operations because all Power Pivot-related content is stored in SharePoint content databases and all Power Pivot-related operations are performed by Power Pivot shared services in the farm. Users who request a workbook that contains Power Pivot data are authenticated using a SharePoint user identity that is based on their Windows user identity. View permissions on the workbook determine whether the request is granted or denied.
Because integration with Excel Services is required for self-service data analytics, securing a Power Pivot server requires that you also understand Excel Services security. When a user queries a PivotTable that has a data connection to Power Pivot data, Excel Services forwards a data connection request to a Power Pivot server in the farm to load the data. This interaction between the servers requires that you understand how to configure security settings for both servers.
Click the following links to read specific sections in this topic:
Power Pivot for SharePoint supports a reduced set of the authentication options that are available in SharePoint. Of the available authentication options, only Windows authentication is supported for a Power Pivot for SharePoint deployment. In addition, the web application through which sign-in occurs must be configured for classic mode.
Windows authentication is required because the Analysis Services data engine in a Power Pivot for SharePoint deployment supports only Windows authentication. Excel Services establishes connections to Analysis Services via the MSOLAP OLE DB provider using a Windows user identity that was authenticated via NTLM or the Kerberos protocol.
The second requirement, classic mode authentication on the web application, is required to ensure the operability of the Power Pivot web service. The web service is a component that runs on a Web front-end and provides HTTP redirection to a Power Pivot for SharePoint server in the farm. While the web service is claims aware for service-to-service communications, it is not claims aware for the data connection requests that it routes to a Power Pivot shared service in the farm. Requests to load Power Pivot data are only supported for authenticated connections that come from IIS using a Windows identity. Classic mode sign-in on the web application is what enables a successful connection from the Power Pivot web service to Power Pivot shared services in the farm.
Although classic mode sign-in is not required for the more common data access scenario (where Power Pivot data is extracted from the same Excel workbook that renders it) do not attempt to use Power Pivot for SharePoint with SharePoint web applications that are configured to use other authentication providers. Doing so will result in a connection failure whenever users try to connect to Power Pivot workbooks as an external data source.
Without classic mode sign-in, the following types of requests that are handled by Power Pivot Web service will fail:
Any request for Power Pivot data that originates from outside the farm (for example, creating a report in Report Designer or Report Builder, where the data source is a SharePoint URL to a Power Pivot workbook)
In-farm requests from a client application or report that uses the Power Pivot workbook as an external data source (for example, creating a workbook in the Excel desktop application, using as your data source a second published Excel workbook containing Power Pivot data)
When creating new web applications, be sure to select the Classic mode authentication option in the Create New Web Application page.
For existing web applications, use the following instructions to verify the web application is configured to use Windows authentication.
In Central Administration, in Application Management, click Manage web applications.
Select the web application.
Click Authentication Providers.
Verify that you have one provider for each zone, and the Default zone is set to Windows.
SharePoint authorization is used exclusively for all levels of access to Power Pivot query and data processing.
The Analysis Services role-based authorization model is not supported. There is no role-based authorization for Power Pivot data at the cell, row, or table level. You cannot secure different parts of the workbook to grant or deny access to sensitive data within it for specific users. Embedded Power Pivot data is wholly available to users who have View permissions on the Excel workbook in a SharePoint library.
Power Pivot for SharePoint will impersonate a SharePoint user in the following cases:
Queries to PivotTables or PivotCharts that have data connections to a Power Pivot database, where a Power Pivot service application establishes connections on behalf of a user to a specific Power Pivot shared service instance that processes the data.
Loading Power Pivot data from cache or a library if the data is not otherwise available. If a data connection request is made for Power Pivot data that is not already loaded in the system, the Analysis Services service instance will use the identity of the SharePoint user to retrieve the data source from a content library and load it into memory.
Data refresh operations that save an updated copy of the data source to the workbook in a content library. In this case, an actual log on operation is performed using the user name and password that is retrieved from a target application in Secure Store Service. Credentials can be the Power Pivot unattended data refresh account, or credentials that were stored with the data refresh schedule when it was created. For more information, see Configure Stored Credentials for Power Pivot Data Refresh (Power Pivot for SharePoint) and Configure the Power Pivot Unattended Data Refresh Account (Power Pivot for SharePoint).
Publishing, managing, and securing a Power Pivot workbook is supported solely through SharePoint integration. SharePoint servers provide authentication and authorization subsystems that ensure legitimate access to data. There are no supported scenarios for securely deploying a Power Pivot workbook outside of a SharePoint farm.
User access to Power Pivot data is read-only on the server through View permissions or higher. Contribute permissions allow for adding and editing the file. Changes to Power Pivot data require that you download the workbook to an Excel desktop application that has Power Pivot for Excel installed. Contribute permissions on the file will determine whether the user can download the file locally and then save changes back to SharePoint.
As such, Contribute and View Only levels of permission define the effective set of permissions for user access to Power Pivot data. Other permission levels work to the extent that they have the same permissions as Contribute and View Only (For example, because Read includes View Only permissions, a user who is assigned to Read will have same level of access as View Only).
The following table summarizes the permission levels that determine access to Power Pivot data and server operations:
|Permission level||Allows these tasks|
|Farm or service administrator||Install, enable, and configure services and applications.|
Use Power Pivot Management Dashboard and view administrative reports.
|Full control||Activate Power Pivot feature integration at the site collection level.|
Create a Power Pivot Gallery library.
Create a data feed library.
|Contribute||Add, edit, delete, and download Power Pivot workbooks.|
Configure data refresh.
Create new workbooks and reports based on Power Pivot workbooks on a SharePoint site.
Create data service documents in a data feed library
|Read||Access Power Pivot workbooks as an external data source, where the workbook URL is explicitly entered in a connection dialog box (for example, in Excel’s Data Connection Wizard).|
|View Only||View Power Pivot workbooks.|
View data refresh history.
Connect a local workbook to a Power Pivot workbook on a SharePoint site, to repurpose its data in other ways.
Download a snapshot of the workbook. The snapshot is a static copy of the data, without slicers, filters, formulas, or data connections. The contents of the snapshot are similar to copying cell values from the browser window.
Power Pivot server-side query processing is tightly coupled with Excel services. Product integration begins at the document level, in that Power Pivot workbooks are Excel workbook (.xlsx) files that either contain or reference Power Pivot data. There is no separate file extension for a Power Pivot workbook.
When a Power Pivot workbook is opened on a SharePoint site, Excel Services reads the embedded Power Pivot data connection string and forwards the request to the local SQL Server Analysis Services OLE DB provider. The provider then passes the connection information to a Power Pivot server in the farm. In order for requests to flow seamlessly between the two servers, Excel Services must be configured to use settings that are required by Power Pivot for SharePoint.
In Excel Services, security-related configuration settings are specified on trusted locations, trusted data providers, and trusted data connection libraries. The following table describes the settings that enable or enhance Power Pivot data access. If a setting is not listed here, it has no effect on Power Pivot server connections. For instructions on how to specify these settings step by step, see the section "Enable Excel Services" in Initial Configuration (Power Pivot for SharePoint).
|Web application||Windows authentication provider||Power Pivot converts a claims token that it gets from Excel Services to a Windows user identity. Any web application that uses Excel Services as a resource must be configured to use the Windows authentication provider.|
|Trusted location||Location Type||This value must be set to Microsoft SharePoint Foundation. Power Pivot servers retrieve a copy of the .xlsx file and load it on an Analysis Services server in the farm. The server can only retrieve .xlsx files from a content library.|
|Allow External Data||This value must be set to Trusted data connection libraries and embedded. Power Pivot data connections are embedded in the workbook. If you disallow embedded connections, users can view the Power Pivot cache, but they will not be able to interact with the Power Pivot data.|
|Warn on Refresh||This value should be disabled if you are using Power Pivot Gallery to store workbooks and reports. Power Pivot Gallery includes a document preview feature that works best if both refresh on open and Warn on Refresh are turned off.|
|Trusted data providers||MSOLAP.4|
|MSOLAP.4 is included by default, but Power Pivot data access requires that the MSOLAP.4 provider be the SQL Server 2008 R2 version.|
MSOLAP.5 is installed with the SQL Server 2016 version of Power Pivot for SharePoint.
Do not remove these providers from the trusted data provider list. In some cases, you might need to install additional copies of this provider on other SharePoint servers in your farm. For more information, see Install the Analysis Services OLE DB Provider on SharePoint Servers.
|Trusted data connection libraries||Optional.||You can use Office Data Connection (.odc) files in Power Pivot workbooks. If you use .odc files to provide connection information to local Power Pivot workbooks, you can add the same .odc files to this library.|
|User defined function assembly||Not applicable.||Power Pivot for SharePoint ignores user-defined function assemblies that you build deploy for Excel Services. If you rely on user-defined assemblies for a specific behavior, be aware that Power Pivot query processing will not use the user-defined functions you created.|
Configure Power Pivot Service Accounts
Configure the Power Pivot Unattended Data Refresh Account (Power Pivot for SharePoint)
Create a trusted location for Power Pivot sites in Central Administration
Power Pivot Securtiy Architecture