Refreshing PowerPivot Data in SharePoint 2013

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.

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.

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.