Applies to: PowerPivot data refresh on SharePoint, SQL Server 2008 R2 and SQL Server "Denali".

There is a known error in PowerPivot data refresh that occurs when using the PowerPivot unattended data refresh account in a farm where the PowerPivot service application is running under an account that is different from the farm account. In this configuration, the owner of the account (the PowerPivot service application) does not have permission to request the target application from Secure Store Service.


 
The error appears in the data refresh history page, after data refresh fails.

The error occurs under these conditions:

• Data refresh is running under the PowerPivot unattended data refresh account.
• PowerPivot unattended data refresh account is mapped to a target application based on the Individual account type.
• PowerPivot service application is running under an account other than the farm account.

Resolution

1. In Central Administration, in Application Management, click Manage service applications.
2. Click Secure Store Service.
3. Delete the PowerPivot unattended data refresh account.
4. Click New.
5. In Target Application ID, enter a name (such as PowerPivotUnattended), followed by display name and contact email.
6. In Target Application Type, select Group. Click Next.
7. Click Next again to accept the defaults on the field name and field type page.
8. In Target Application Administrators, enter your account, plus the account of any other administrator who might need to manage this application.
9. In Members, enter the following:
   • A farm administrator account.
   • The farm account.
   • The application identity of the PowerPivot service application (you can find this information in Security, in Configure service accounts).
   • The Windows accounts of any SharePoint group or user who will be running data refresh using the target application you are creating.
10. Set credentials on the target application.
    • Select PowerPivotUnattended.
    • Click Set in Credentials, and specify a Windows domain user account and password. Verify that this account has data read permissions on the external data sources that will be accessed during data refresh.
11. Assign the PowerPivotUnattended target application in the PowerPivot service application configuration page.
    • In Application Management, click Manage service applications.
    • Click PowerPivot Service Application. Wait for the dashboard to load.
    • Click Configure service application settings in the top right corner.
    • Scroll down to the data refresh section, and then type in the name of the target application you just created (PowerPivotUnattended).
12. Verify data refresh succeeds by running data refresh on the workbook, using the third credential option where you type in the target application name. Be sure to click the Also refresh as soon as possible checkbox in the data refresh schedule page.  Click OK.  Re-open the Configure data refresh page to view the outcome. Remember that the data refresh timer job runs every minute. It will take at least that long for you to confirm that data refresh is working.

SQL Server 2008 R2 Service Pack 1 (SP1) does not currently fix this issue, so if you are using SP1, you must still workaround this error.

Other Troubleshooting Tips

1. Enable the Secure Store Service audit log in the service application properties page.
2. Run data refresh immediately using the Also refresh as soon as possible option in a data refresh schedule page.
3. Use SQL Server Management Studio to connect to the farm's database server.
4. Expand the Secure Store Service DB, expand Tables, and select the top 1000 rows of the dbo.SSSAudit table.

Query results will show activity of target application usage in Secure Store Service, including which accounts are requesting access to the PowerPivot unattended data refresh account.

Links

Troubleshooting PowerPivot Data Refresh