For awhile now, we here at PowerPivot for SharePoint Support have seen a variety of Claims Identity/Windows Token/UPN errors.  Primarily with PowerPivot failing to refresh a workbook in the browser.

But recently, I noticed another product presenting this error.

In the ULS logs, you will see:

10/29/2013 10:11:28.57 w3wp.exe (0x1DA4) 0x33F4 SharePoint Foundation Claims Authentication bz7l Medium SPSecurityContext.WindowsIdentity: Could not retrieve a valid windows identity for NTName='Domain\User', UPN='User@Domain.com'. UPN is required when Kerberos constrained delegation is used. Exception: System.ServiceModel.Security.SecurityAccessDeniedException: Access is denied.    Server stack trace:      at System.ServiceModel.Channels.ServiceChannel.ThrowIfFaultUnderstood(Message reply, MessageFault fault, String action, MessageVersion version, FaultConverter faultConverter)     at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc)     at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)     at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)     at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)    Exception rethrown at [0]:      at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)     at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)     at Microsoft.IdentityModel.WindowsTokenService.S4UClient.IS4UService_dup.UpnLogon(String upn, Int32 pid)     at Microsoft.IdentityModel.WindowsTokenService.S4UClient.<>c__DisplayClass1.<UpnLogon>b__0(IS4UService_dup channel)     at Microsoft.IdentityModel.WindowsTokenService.S4UClient.CallService(Func`2 contractOperation)     at Microsoft.SharePoint.SPSecurityContext.GetWindowsIdentity().

In the GUI, you will see the error "Cannot convert identity to windows token."

Resolution:

Any user that is trying to refresh data, needs to have a check next to Read under Authenticated Users when looking at AD > Security (note: may need to enable Advanced Features under View tab so you can see the Security tab in AD).

 You may receive the following warning dialog box when opening a workbook using Excel Services in SharePoint:

Be careful. This workbook contains one or more queries that might be unsafe. Do you want to enable these queries?

To suppress this and other warnings from Excel Services, go to SharePoint Central Administration > Application Management > Service Applications > Manage service applications > Excel Services application > Trusted File Locations and click on the Address that contains the workbook.

In the External Data section, under Warn on Refresh, uncheck Refresh warning enabled and click OK.

Users who access the workbook will no longer be presented with a warning dialog box.

Now that we have a PowerPivot workbook in the Excel Rich Client, we are ready to publish that workbook to SharePoint.

In "What is Power Pivot for SharePoint? Part 1" I discussed the Data Model and how it is a database inside of Excel.  When you publish/upload a regular Excel workbook to SharePoint, that file gets chunked up into blobs and stored in the Content Database.  With a PowerPivot workbook the Excel workbook also gets stored in the Content Database, BUT the Data Model gets created and stored (after the initial (successful) refresh in browser) on the server running POWERPIVOT.

Note: In SharePoint 2010, the Analysis Service POWERPIVOT instance is going to be on a SharePoint server in the farm.  In SharePoint 2013, we recommend using a SQL Server off the farm.  Step by step install instructions here.

I am now going to touch on the three refreshes of PowerPivot 2013 and how they work.

1. Browser Refresh

When you open workbook and click a slicer, you are merely accessing the Data Model.  In other words, (in this example) the PowerPivot workbook stored in a SharePoint Report Library is going our to the SQL Server running PowerPivot > Databases > PowerPivot20Test20Workbook and is pulling whatever data (fresh or stale) that is currently in that cube.

If you login to SQL Management Studio > Analysis Services > Servername\POWERPIVOT > Databases you will see the Data Model (in this case PowerPivot20Test20Workbook_ae1bfdf63a44395b27a908c0ac0855_1f7abf568bb14cd4bb070b5eb858911c_SSPM):

Troubleshooting:  I have seen the refresh in browser fail periodically.  This was caused by not having the correct ASOLEDB and ADOMD.Net drivers (located here: for SQL 2008 R2 SP1 & SQL 2012 SP1) on the SharePoint Servers running Excel Services and/or the Claims to Windows Token Service not running on these machines/lacking proper permissions.

2. Scheduled Data Refresh

In SharePoint 2010, running the Scheduled Data Refresh is the only way to get fresh data into the PowerPivot workbook (this is designed to run once daily during "after business hours").  A timer job runs, the Data Model is updated with fresh data and workbook is republished back to SharePoint.  When you open the workbook and click on a slicer, the Browser Refresh occurs a you will see data from the time the Scheduled Data Refresh ran (data from the time the Scheduled Data Refresh ran that night).  In 2013, you are not 100% dependent on the "Scheduled Data Refresh" (if your backend data source accepts Windows Credentials; SQL or Analysis Services) to see fresh data.  In 2013, you can use the "Interactive Data Refresh" which I will touch on next.  If you want to refresh from a data source that does not accept Windows Credentials (Oracle, IBM (in some cases) SQL, etc.)  you will need to use the Scheduled Data Refresh in both versions of SharePoint (2010 & 2013).

For this functionality to be present is SharePoint 2013, you need to install the PowerPivot.msi on a SharePoint server.

Troubleshooting: For detailed instructions on how to set this up please follow this article. For help pulling from a data source that does not accept Windows Credentials, please see our blog.

 3. Interactive Data Refresh

The "Interactive Data Refresh" is only available in SharePoint 2013.  The "Interactive Data Refresh" passes the SharePoint user's credentials all the way to the backend and pulls real-time data into a PowerPivot workbook.  To do this, you need to choose Data > Refresh All Connections. 

You can pull data from a data source that accepts Windows Credentials (SQL & SSAS).  For steps on how to set this up, please see our blog.

I find myself explaining PowerPivot to customers over and over.  Most understand parts of how it works but not the whole story.  I will touch on two parts; PowerPivot for Excel and PowerPivot for SharePoint.

Part 1: PowerPivot for Excel

PowerPivot for SharePoint is nothing without the Excel Rich Client and the PowerPivot Add-in.  Pre-Excel 2013, the PowerPivot Add-in was a separate COM Add-in that you had to download.  In Excel 2013, the PowerPivot Add-in ships with the product.

Before you can do anything in SharePoint, you need to create a PowerPivot workbook in Excel.  To do this, open the PowerPivot tab choose Manage > From Database (choose your datasource, servername and database name).

You (potentially) will be pulling in a large amount of data into Excel.  I have seen people with Excel (PowerPivot) workbooks 1GB in size (which is huge since .xlsx (Open Office XML) files are zipped).  This large amount of data is essentially a database and you can see this in the workbook.  To view the Data Model, rename workbook file extension (.xlsx to .zip), open the workbook > xl > model > item.data. 

After you have pulled this data (essentially a database) into the PowerPivot environment, Excel can point to this database and build a PivotTable off it.  In the PowerPivot environment choose PivotTable > PivotTable

Excel is now pointing at the database (Data Model) embedded inside itself.  You can see for yourself via Excel > Data Connections > Properties.  In SharePoint (when we get there), we will call this the "Refresh in Browser".

Keep in mind there is still the PowerPivot Connection that can also refresh.  In SharePoint, we will call this the "Scheduled Data Refresh".  You can view this connection in Excel > PowerPivot > Manage > Existing Connections (choose the PowerPivot Data Connection) > Edit > Advanced.

There is still one more refresh (in SharePoint 2013 & SQL 2012 SP1) called the "Interactive Data Refresh".  I will touch on that later in the "What is PowerPivot for SharePoint?  Part 2".

At this point, we should have a beautiful Excel (PowerPivot) workbook that has a Pivot Table and Slicers and looks something like the below sample.

Now that we have a beautiful Excel (PowerPivot) workbook that functions wonderfully in the Excel Rich Client, we are ready to publish this to SharePoint!

Please see "What is PowerPivot for SharePoint? Part 2".

If you come across this error when trying to perform a scheduled data refresh:

"A schedule cannot be enabled for a workbook with no external data sources."

This is because the Data Model cannot be created on the PowerPivot Server.

Your probably have two questions.

1. What is a "Data Model".

The Data Model is a cube created under the PowerPivotServer\POWERPIVOT instance:

2.Why can't/isn't the Data Model being created?

The reason why this isn't being created is because there is something wrong with the Excel Services "Data Model Settings".

You can find this be going to the Central Administration > Application Management > Manage Service Applications > Excel Services > Data Model Settings.

At this location, you need to enter the proper PowerPivotServer\Instance

Here is the proper way:

Here is a bad example (no need to have just the server name present):

Here is another bad example (PowerPivot) is misspelled:

I hope this helps.

Tom