This case may be a one-off, but I wanted to write about it in the event someone else comes across it in the future.

Here is the synopsis.  A user was creating an Excel workbook in the rich client using "Use Windows Authentication":

When you do that, the connection string looks like:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=AdventureWorks2012;Data Source=Data;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=RICKCLIENT;Use Encryption for Data=False;Tag with column collation when possible=False

The Security Support Provider Interface
http://technet.microsoft.com/en-us/library/bb742535.aspx

The company had a group of Authors with Role Based permissions to the SDL Data.  The Authors were able to connect to the data source and build the workbooks using their Logon Credentials.  However, the Authors were changing the "Excel Services Authentication Settings" to "Use a stored account Application ID:" prior to publishing to SharePoint.  The reasoning behind this made complete sense, the Members of the Secure Store Application ID did not have Role Based permissions to the SQL Data.  The "Set Credentials" the account was a SQL account that did have access to the SQL Data.  See below how it was built. 

That Application ID "Fargo" was using SQL Credentials.  This is how they built it:

Now when they use a workbook that was created in Excel using  "Use Windows Authentication" with a Secure Store Service Application ID that uses SQL Credentials, things go wacky.  We noticed that the account running Excel Services needs to be given permissions in SQL.

BUT, if the workbook was created (In Excel Rich Client) using "Use the following UserName and Password", "Integrated Security=SSPI" is not present and the SQL "UserID=XXXXX" is saved  in the Connection String and there is no further action required, the refresh in Excel Services completes without error. 

Provider=SQLOLEDB.1;Persist Security Info=True;User ID=EddieVedder;Initial Catalog=AdventureWorks2012;Data Source=Data;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=AMENTCLIENT;Use Encryption for Data=False;Tag with column collation when possible=False

Analysis:

This is design.  You must have the workbook created (in the Excel Rich Client) with the same Authentication Type as the Secure Store Service.  So if the Secure Store Service is holding SQL User ID and Password for SQL Auth, the workbook must be using SQL Auth when it is built, if you see SSPI in the connection string that means that Windows Auth Mode is used.  A connection can only use one AuthMode, either SQL Auth or Windows Auth.

If you have a workbook with a Power View worksheet that was created in Excel, and you upload that workbook to the PowerPivot Gallery in SharePoint, you may receive a red X instead of a thumbnail because of a failure with the snapshot generation:

This is expected behavior as the snapshot process does not support embedded Power View reports in workbooks.

One workaround is to only publish all worksheets except for the Power View worksheets when uploading the workbook to the PowerPivot Gallery. Click on Save As in Excel when saving to SharePoint and you will get a Browser View Options button in the Save As dialog box:

Click on Browser View Options, and on the Show tab, select the dropdown menu and change Entire Workbook to Sheets. This will allow you to uncheck the Power View worksheet:

Click OK and continue to save the workbook. The Power View worksheet will not be displayed in the PowerPivot Gallery thumbnails or in Excel Services when you open the workbook, but it will still be visible when you open the workbook in Excel client. This allows the thumbnails for the rest of the worksheets to be generated successfully in the PowerPivot Gallery, but you will lose the ability to view the Power View report in the browser. As you can see below, the third worksheet, which is the Power View worksheet, is not shown in the workbook thumbnail, and is not part of the worksheet thumbnails:

Another option is to place the Power View report as the last worksheet in the workbook, and then display the first worksheet before saving the file. This will allow snapshot generation of all worksheets before the Power View worksheet as the worksheets are enumerated in order during the snapshot process. As you can see below, the workbook snapshot shows that there are 3 worksheets, but only thumbnails are only generated for the first 2 worksheets as the third one is a Power View worksheet:

The advantage of this option is that the Power View worksheet is published to SharePoint, so even though the thumbnail is not generated, when you open the workbook in Excel Services, the third worksheet is visible and the Power View report is rendered.

In order to view a Power View report thumbnail, you will need to use Power View for SharePoint to create the Power View report instead of creating a Power View worksheet in Excel. To do this, upload the PowerPivot workbook to the PowerPivot Gallery. Next to the thumbnail for the workbook, you have a few icons, one of which allows you to create a standalone Power View report in RDLX format based on the PowerPivot workbook. When you hover over the button it says Create Power View Report:

After building the report and saving it to the PowerPivot Gallery, a thumbnail will be generated because in this case, Power View generates the thumbnail instead of PowerPivot using a different snapshot generation process:

Please note that the Power View feature for SharePoint is only available with SQL Server 2012 SP1 and above as part of the Reporting Services add-in for SharePoint.

Issue Overview:

Post migration of a working PowerPivot system from Windows Authentication to Claims Authentication sometimes not all workbook files author property is updated to the new format, this causes the "PowerPivot Management Dashboard Processing Timer Job" to fail with error:

“Microsoft.SharePoint.SPException: User cannot be found” at Microsoft.AnalysisServices.SPAddin.UsageProcessingTimerJob.PrepareUsageData

Some or all of your PowerPivot files like have their author property set in the old Windows style name like “MYDOMAIN\someuser”

If the author property was set to a claims format the error would go away, like “i:0#.w|MYDOMAIN\someuser”

The workaround for this issue is to delete and re-upload the PowerPivot workbooks affected.

The author field can not be fully examined from the SharePoint UI, so it can be difficult to track down all of these workbooks.

Attached to this post (very bottom) is sample code that shows one way to list all of the workbooks for a PowerPivot service application that have an author property in the Windows style format (these files will throw and error when the "PowerPivot Management Dashboard Processing Timer Job" runs).

** THIS SAMPLE CODE IS PROVIED AS IS, WITH NO WARRANTEES OR SUPPORT OF ANY KIND, USE AT YOUR OWN RISK **

Rename PPTimerUserError.bak to PPTimerUserError.exe so you can run the program (.bak so zip scanners don't block the file)

The .exe must be ran from one of the farm SharePoint 2013 servers

The output is tab delimited so you can open with Excel to sort and filter as needed

useage: PPTimerUserError <DB Server> <PowerPiviot DB Name>

example: PPTimerUserError warrenr-ws1 DefaultPowerPivotServiceApplicationDB-57bc03ae-bd41-4102-ab6f-f03201e3b583

output at C:\temp\PPTimerAuthorReport.txt  and PPTimerAuthorReportDetails.txt

Update

This PowerShell code is known to update the author field if you do not want to remove and re-add the affected files,

**USE AT YOUR OWN RISK **

$web = get-spweb http://reports2013/analytics

$list = $web.lists['Reports']

$user = get-spuser -Web $web -limit all | ? {$_.userlogin -match "SPAdmin"}

$sUser = $user.id.ToString() + ';#' + $user.DisplayName

$listItems = $list.Items

write-host $listItems[0]["Author"]

write-host $listItems[1]["Author"]

write-host $listItems[2]["Author"]

$li = $listItems[1] 

$li["Author"] = $sUser

$li.properties["vti_author"] = $user.loginname

$li["Editor"] = $sUser 

$li.UpdateOverwriteVersion()

write-host $li["Author"]

Issue:

This issue with the email notifications was fixed with Cumulative update package 7 for SQL Server 2012 SP1, but we are seeing cases where the email notifications still do not happen, even after installing CU7 for SQL Server 2012 SP1.

Cause:

The PowerPivot for SharePoint update is not complete until the PowerPivot Configuration Tool is run.

Workaround:

Run the PowerPivot for SharePoint Configuration Tool to complete the CU7 update for PowerPivot for SharePoint, and then the email notifications will begin working.

Cumulative update package 7 for SQL Server 2012 SP1

http://support.microsoft.com/kb/2894115

Summary:

If the Excel Services global option "Use the EffectiveUserName" is turned on, then all workbooks that use secure store service ID's (SSID) will not refresh data and can not create connections to the SSAS data sources. So effectively the SSID authentication option is not useable if the EffectiveUserName global option is turned on.

ULS entry:

ExternalSource.ValidateConnection: Unable to get a connection: ... Microsoft.Office.Excel.Server.CalculationServer.ExternalSource.TryGetValidatedConnection

Status:

The product group is investigating the issue right now, there is an estimated fix date of November 2014.

If you are using a Power View Sheet and it is not rendering in the browser (see below screenshot):

You will want to verify if you are using Office Web Apps Server 2013 (WopiFrame.aspx) to render the workbook by examining the URL:

URL Example:

http://servername/sites/BICenter/_layouts/15/WopiFrame.aspx?sourcedoc=%2Fsites%2FBICenter%2FTest%20PowerPivot%20Gallery%2FPower%2Exlsx&action=view

If you are indeed using the WopiFrame.aspx, you will want to flip this (via the SharePoint 2013 Management Shell) to use the ExcelService.asmx via:

New-SPWOPISuppressionSetting -extension xlsx -action view

In the below article, you will see that Power View Sheets are not visible in Office Web Apps 2013 (WopiFrame.aspx):

Power View in Excel on Office 365 or in SharePoint Server
http://office.microsoft.com/en-us/excel-help/power-view-in-excel-on-office-365-or-in-sharepoint-server-HA103276078.aspx

Additional Information:

To verify the Suppression, run this command in the SharePoint 2013 Management Shell:

Get-SPWOPISuppressionSetting

It should return: XLSX VIEW

To remove this Suppression run this command in the SharePoint 2013 Management Shell:

Remove-SPWOPISuppressionSetting -extension xlsx -action view

To understand the above Office Web Apps SharePoint 2013 Management Shell Commands refer to the below article:

Windows PowerShell for Office Web Apps (SharePoint 2013)
http://technet.microsoft.com/en-us/library/jj219457(v=office.15).aspx