Refreshing Project Server 2013 Excel Services Reports (PowerPivot Data)

Project Server 2013, Excel Services Reports, PowerPivot, SharePoint 2013

There is some setup that needs to occur in order for you to get your PowerPivot Data to Refresh.  In our experience errors relates to the secure store service setup.  This blog posting by Kay Unkroth is a great resource for those experiencing issues/errors.  Thanks again Kay.

—- This is a repost —–

In SharePoint 2013, Excel Services comes with numerous improvements. One of the most significant for PowerPivot is the ability to refresh data models interactively all the way from the original data sources. Unlike Excel Services in SharePoint 2010, which only queries data models but doesn’t refresh them interactively, Excel Services in SharePoint 2013 first sends processing commands to the Analysis Services server hosting the data model and then queries the data model to update the workbook when you click on Refresh Selected Connection or Refresh All Connections on the Data menu in the browser. Note, however, that interactive data refresh is only available for workbooks created in Excel 2013. If you try to refresh an Excel 2010 workbook, Excel Services will display an error message stating that older versions cannot be refreshed until the file is upgraded, as in the following screenshot.

So in SharePoint 2013, users have two options to refresh a data model—Interactive and Scheduled—but note that there are some differences regarding their dependencies. The following table summarizes the most important points.

Interactive Data Refresh

Scheduled Data Refresh

Available out of the box as soon as you have registered an Analysis Services server running in SharePoint mode in the Excel Services configuration.

Requires the deployment of the PowerPivot add-in for SharePoint 2013.

Only refreshes the data in the current user session but does not save the data back to the workbook.

Opens the workbook in a separate refresh session and saves the updated version back to the content database.

Interactive data refresh can use the identity of the currently logged-on user or stored credentials to connect to the data source.

Uses stored credentials.

Only works for workbooks created in Excel 2013.

Works for workbooks created using the SQL Server 2012 PowerPivot add-in for Excel 2010 or using Excel 2013. Note that workbooks created in Excel 2010 with the SQL Server 2008 R2 PowerPivot add-in must be upgraded at least to the 2012 PowerPivot format.

The fact that an interactive data refresh can use the identity of the currently logged-on user is particularly interesting because it is the default setting for data connections in Excel. The following screenshot shows the Excel Services authentication settings that influence how Excel Services performs an interactive data refresh against a given data source. In my case, this is a SQL Server connection. If your workbook uses multiple connections, you can configure authentication settings for each data connection individually.

There are three options in the Excel Services Authentication Settings dialog box. The first “Use the authenticated user’s account” causes Excel Services to perform the refresh under the identity of the currently logged-on user. The second “Use a stored account” expects a Secure Store Service (SSS) application ID, which Excel Services then uses to retrieve the user name and password in order to authenticate for the data refresh operation. The third “None” stands for “Use the Unattended Service Account of Excel Services” which corresponds to a Secure Store application ID registered in the Excel Services configuration.

Before diving deeper, let’s clarify one potentially confusing aspect about these authentication settings and that is that they determine the Windows identity that Excel Services and Analysis Services use for the data refresh, but they are not necessarily the credentials to establish the connection to the data source. Credentials in the connection string can override the authentication settings. For example, a SQL Server data source can use SQL Server authentication in which case Excel Services first applies its own authentication settings and then Analysis Services establishes the connection at which point the SQL Native Client uses the user name and password from the connection string to log on to the data source. The Excel Services authentication settings are effective if SQL Server uses Windows authentication and the connection string specifies integrated security (Integrated Security=SSPI).

So there are two separate phases: (a) Authenticate against Windows and (b) connect to the data source. The authentication settings, summarized in the following table, influence phase A. Connection string parameters influence phase B.

Authentication Setting

Description

Use the authenticated user’s account

Excel Services uses the Claims to Windows Token Service to transform the currently logged-on user’s SharePoint security token into an impersonation-level Windows security token. For this to work, you must start the Claims to Windows Token Service on the SharePoint application server running Excel Services. Moreover, you cannot use this option if the SharePoint Web application hosting the site of the workbook is using forms-based or SAML-based authentication for incoming requests. The authenticated user must be an authenticated Windows user. If Excel Services cannot determine the Windows identity, you will get an error stating that the data connection uses Windows Authentication and user credentials could not be delegated, as in the left screenshot below.

Use a stored account

Retrieves user name and password from a target application in Secure Store Service and performs a Windows logon.

If you use this option and specify an incorrect target application ID, such as a target application that your user account has no permissions to access, you will get an error stating that Excel Services could not access the specified application id from Secure Store Service. Excel Services retrieves the credentials defined for the target application in the context of the currently logged on user, so make sure your account and the accounts of all other users that are supposed to work with your workbook are mapped to the target application when configuring the Members list for the target application in the Secure Store Service.

None

This is similar to specifying a stored account, except that Excel Services now uses the target application registered under its Unattended Service Account. To specify an Unattended Service Account, display the Excel Services configuration settings in Central Administration, and then under Global Settings, in the External Data section, register the account credentials. For detailed instructions, see Use Secure Store with SQL Server Authentication (SharePoint Server 2013).

If you choose “None” as the authentication method and an Unattended Service Account has not been configured, you will get a corresponding error in the browser when you attempt to refresh the data.

More often than not, information workers just use the default setting “Use the authenticated user’s account.” While using the default is certainly convenient, it introduces dependencies in SharePoint and in Analysis Services. The reason for this is that Excel Services doesn’t just use the authentication settings to determine its own way to authenticate in Windows. It also uses these authentication settings to instruct Analysis Services to authenticate in the same way. You can see this if you run SQL Server Profiler on your Analysis Services server.

In the Profiler trace, look for a Batch command that Excel Services sends to Analysis Services in response to an interactive data refresh. It will include an ImpersonationInfo node, which specifies an Account name and a Password when using stored credentials (note that the actual password string is not included in the trace for security reasons), but the <Password/> node is there. This node is entirely missing when refreshing data in the context of the currently logged-on Windows user. The difference is that the Secure Store target application gives Excel Services a user name and a password, while the password is not retrievable for the currently logged-on user.

The following listing shows a Batch command example.

So what’s the big deal about the difference? If Excel Services can send Analysis Services a user name and password, Analysis Services can use the standard Windows Logon function to authenticate the user. If the password is unknown, Analysis Services must use Service for User (S4U) Kerberos Extensions to impersonate the user. S4U requires the right to Act as Part of the Operating System, which Analysis Services does not have by default because it is a very high-privileged permission practically equivalent to running as Local System. So, even with Windows authentication on your SharePoint Web apps and the Claims to Windows Token Service running your application servers, interactive data refresh won’t succeed until you grant the Analysis Services account the right to Act as Part of the Operating System. There is also the issue that S4U returns an impersonation-level Windows token, which requires Kerberos Constrained Delegation to be configured for Analysis Services to access a remote data source, as depicted in the following figure. If Analysis Services does not have the permission to Act as Part of the Operating System, it cannot impersonate the Windows user, and if KCD is not configured, it cannot delegate the identity of the currently logged-on user. In both cases, processing will fail and Excel Services will tell you that it was unable to refresh one or more data connections in the workbook, as shown in the right screenshot above.

Note that stored credentials do not have the dependencies on Act as Part of the Operating System or KCD because Analysis Services can perform a full Windows Logon. If you do not want to elevate the privileges or cannot configure KCD, your users must fall back to Secure Store target applications for interactive data refresh. Then, Analysis Services can run with a low privileged account in the SharePoint 2013 backend.

Creating and using Secure Store target applications is not very convenient for most information workers, it’s also hard to find the Excel Services Authentication Settings dialog box (in Excel, on the Data tab, click Connections, select the desired connection, click Properties, switch to the Definition tab, and then click on Authentication Settings), but there is no way around the S4U permission requirements. Either educate your users accordingly or ask them to use Scheduled Data Refresh because Scheduled Data Refresh always works with stored credentials since there is no interactivity in these scenarios. On the other hand, if your network is structured such that clients cannot directly access your Analysis Services servers in the backend, it should be OK to grant the Act as Part of the Operating System right and fully enable the interactive data refresh scenarios.

The complete blog article can be found here:  http://blogs.msdn.com/b/analysisservices/archive/2012/12/21/refreshing-powerpivot-data-in-sharepoint-2013.aspx

PowerPivot, Analysis Services, Excel, Troubleshooting, Excel Services, Data Refresh