• Article

Plan PowerPivot Authentication and Authorization

A PowerPivot 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 PowerPivot content and operations because all PowerPivot-related content is stored in SharePoint content databases, and all PowerPivot-related processing is performed on PowerPivot shared services in the farm. Users who request a workbook that contains PowerPivot 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.

SharePoint authorization is used exclusively for all levels of access to PowerPivot data processing and PowerPivot services. For the PowerPivot data that is visualized within an Excel workbook, there is no row-level authorization in the workbook that enables fine-grained security at the 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. You cannot apply Analysis Services role-based security to secure PowerPivot data in an Excel workbook. Embedded PowerPivot data is wholly available to users who have View permissions on the Excel workbook in a SharePoint library.

Because integration with Excel Services is required for self-service data analytics, securing a PowerPivot server requires that you also understand Excel Services security. When a user queries a PivotTable that has a data connection to PowerPivot data, Excel Services forwards a data connection request to a PowerPivot server in the farm to load the data. This interaction between the servers requires that you understand how to configure security settings that are compatible for both.

Click the following links to read specific sections in this topic:

Authentication Providers Supported by PowerPivot for SharePoint

User Authorization

SharePoint Permissions

Using Excel Services security with PowerPivot workbooks

Authentication Providers Supported by PowerPivot for SharePoint

PowerPivot for SharePoint is supported for SharePoint web applications that are configured to use Windows authentication. Windows authentication is required for data connections that are handled by the PowerPivot Web service (specifically, for requests that originate from applications that run outside the farm). This requirement ensures that the user’s Windows security token is transferred correctly from the client application to the web application for subsequent use by the PowerPivot Web service.

Note

For more information about the types of connections that the Web service handles, see PowerPivot Web Service (PowerPivot for SharePoint).

Although Windows authentication is not required for the more common data access scenario (where PowerPivot data is extracted from the Excel workbook that renders it) do not attempt to use PowerPivot 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 PowerPivot workbooks as an external data source.

How to check the authentication provider for your application

For existing web applications, use the following instructions to verify the applications are configured to use Windows authentication. When creating new web applications, be sure to select the Classic mode authentication option in the Create New Web Application page.

  1. In Central Administration, in Application Management, click Manage web applications.

  2. Select the web application.

  3. Click Authentication Providers.

  4. Verify that you have one provider for each zone, and the Default zone is set to Windows.

Understanding the domain account requirement

In production environments, using Windows domain user or group accounts is required. The accounts must be domain accounts. You cannot use local Windows user or group accounts on production servers.

Because of the domain account requirements, the SharePoint server must have network connectivity to a domain controller at all times. This requirement is necessary because the Claims to Windows Token Service authenticates each request using the identity of a Windows domain user (it does not authenticate local accounts). Authentication occurs for each connection. The Claims to Windows Service does not use cached credentials.

Note that requests that flow from a client application to the server must be in the same domain or between domains that have a two-way trust relationship. One-way trust is not sufficient for data refresh on the server.

Note

You can deploy SharePoint 2010, Excel Services, and PowerPivot for SharePoint on a Hyper-V virtual machine if you want to test the features and behavior of SharePoint 2010 in an isolated environment, offline from a corporate network. For more information, see Deploy single server in isolated Hyper-V environment on the TechNet web site.

User Authorization

For specific types of requests, the SharePoint user identity of the person requesting a workbook is verified by PowerPivot service instances to check permissions, generate accurate log information, and establish a usage history. PowerPivot server components will use a SharePoint user identity in the following cases:

  • Queries to PivotTables or PivotCharts that have data connections to a PowerPivot data source, where a PowerPivot service application establishes connections on behalf of a user to a specific PowerPivot service instance that processes the data.

  • Loading PowerPivot data from cache or a library if the data is not otherwise available. If a data connection request is made for PowerPivot data that is not already loaded in the system, the Analysis Services service instance will use the Windows user 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 PowerPivot unattended data refresh account, or credentials that were stored with the data refresh schedule when it was created. For more information, see Configure and Use Stored Credentials for PowerPivot Data Refresh.

SharePoint Permissions

To make full use of the sharing and collaboration features for a PowerPivot workbook, the workbook must be published to a SharePoint library. Only after the workbook is published to a SharePoint server do you get the benefits of fast server-side processing by PowerPivot service instances in the server farm, coordinated and scalable connections, document management features, and administrative insight into usage activity of particular workbooks.

Publishing, managing, and securing a PowerPivot workbook is supported solely through SharePoint integration. SharePoint servers provide authentication and authorization models that ensure legitimate access to data. There are no supported scenarios for securely deploying a PowerPivot workbook outside of a SharePoint farm.

User access to the data source is read-only on the server, but with the ability to download the file to the Excel desktop application. Contribute permissions on the file will determine whether the user can modify the file locally. As such, Contribute and View Only levels of permission define the effective set of permissions for user access to PowerPivot 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 PowerPivot data and server operations:

Permission level

Allows these tasks

Farm or service administrator

Install, enable, and configure services and applications.

Use PowerPivot Management Dashboard and view administrative reports.

Full control

Activate PowerPivot feature integration at the site collection level.

Activate online help.

Create a PowerPivot Gallery library.

Create a data feed library.

Contribute

Add, edit, delete, and download PowerPivot workbooks.

Configure data refresh.

Create new workbooks and reports based on PowerPivot workbooks on a SharePoint site.

Create data service documents in a data feed library

View Only

View PowerPivot workbooks.

View data refresh history.

Connect a local workbook to a PowerPivot 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.

Using Excel Services security with PowerPivot workbooks

PowerPivot server-side processing is tightly coupled with Excel services. Deep integration begins at the document level. PowerPivot workbooks are Excel workbook (.xlsx) files that either contain or reference PowerPivot data. There is no separate file extension for PowerPivot data. The data is stored inside the workbook or referenced from another workbook. When a PowerPivot workbook is opened on a SharePoint site, Excel Services reads the embedded PowerPivot data connection string and forwards the request to the local SQL Server 2008 R2 Analysis Services OLE DB provider. The provider then passes the connection information to a PowerPivot 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 PowerPivot 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 PowerPivot data access. If a setting is not listed here, it has no affect on PowerPivot server connections. For instructions on how to specify these settings step by step, see the section "Enable Excel Services" in Install PowerPivot for SharePoint on an Existing SharePoint Server.

Note

Most security-related settings apply to trusted locations. If you want to preserve default values or use different values for different sites, you can create an additional trusted location for sites that contain PowerPivot data, and then configure the following settings for just that site. For more information, see Create a trusted location for PowerPivot sites.

Area

Setting

Description

Web application

Windows authentication provider

PowerPivot 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. PowerPivot 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. PowerPivot data connections are embedded in the workbook. If you disallow embedded connections, users can view the PivotTable cache, but they will not be able to interact with the PowerPivot data.

Warn on Refresh

This value should be disabled if you are using PowerPivot Gallery to store workbooks and reports. PowerPivot 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. Do not remove it from the trusted data provider list. This version of the OLE DB provider handles requests for PowerPivot data in a SharePoint farm.

Trusted data connection libraries

Optional.

You can use Office Data Connection (.odc) files in PowerPivot workbooks. If you use .odc files to provide connection information to local PowerPivot workbooks, you can add the same .odc files to this library.

User defined function assembly

Not applicable.

PowerPivot servers are unaffected by user-defined function assemblies that you build deploy for Excel Services.