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 operations are performed by 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.

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 for both servers.

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

Windows Authentication Using Classic Mode Sign-in Requirement

PowerPivot Operations Requiring User Authorization

SharePoint Permissions for PowerPivot Data Access

Excel Services Security Considerations for PowerPivot Data Access

Windows Authentication Using Classic Mode Sign-in Requirement

PowerPivot 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 PowerPivot 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 PowerPivot 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 PowerPivot web service. The web service is a component that runs on a Web front-end and provides HTTP redirection to a PowerPivot 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 PowerPivot shared service in the farm. Requests to load PowerPivot 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 PowerPivot web service to PowerPivot shared services in the farm.

Although classic mode sign-in is not required for the more common data access scenario (where PowerPivot data is extracted from the same 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.

Without classic mode sign-in, the following types of requests that are handled by PowerPivot Web service will fail:

• Any request for PowerPivot 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 PowerPivot workbook)

• In-farm requests from a client application or report that uses the PowerPivot 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 PowerPivot data)

How to check the authentication provider for your application

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.

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.

PowerPivot Operations Requiring User Authorization

SharePoint authorization is used exclusively for all levels of access to PowerPivot query and data processing.

The Analysis Services role-based authorization model is not supported. There is no role-based authorization for PowerPivot 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 PowerPivot data is wholly available to users who have View permissions on the Excel workbook in a SharePoint library.

PowerPivot for SharePoint will impersonate a SharePoint user in the following cases:

• Queries to PivotTables or PivotCharts that have data connections to a PowerPivot database, where a PowerPivot service application establishes connections on behalf of a user to a specific PowerPivot shared 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 hizmeti 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 PowerPivot 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 PowerPivot Data Refresh (PowerPivot for SharePoint) and Configure the PowerPivot Unattended Data Refresh Account (PowerPivot for SharePoint).

SharePoint Permissions for PowerPivot Data Access

Publishing, managing, and securing a PowerPivot 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 PowerPivot workbook outside of a SharePoint farm.

User access to PowerPivot data is read-only on the server through View permissions or higher. Contribute permissions allow for adding and editing the file. Changes to PowerPivot data require that you download the workbook to an Excel desktop application that has PowerPivot 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 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:

Excel Services security considerations for PowerPivot workbooks

PowerPivot server-side query processing is tightly coupled with Excel services. Product integration begins at the document level, in that PowerPivot workbooks are Excel workbook (.xlsx) files that either contain or reference PowerPivot data. There is no separate file extension for a PowerPivot 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 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 effect on PowerPivot server connections. For instructions on how to specify these settings step by step, see the section "Enable Excel Services" in Initial Configuration (PowerPivot for SharePoint).

[!NOT]

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 in Central Administration.

Ayrıca bkz.

Görevler

Configure PowerPivot Service Accounts

Configure the PowerPivot Unattended Data Refresh Account (PowerPivot for SharePoint)

Create a trusted location for PowerPivot sites in Central Administration

Diğer Kaynaklar

PowerPivot Securtiy Architecture