Excel services and PowerPivot error: "Unable to refresh data for a data connection in the Workbook. "

Question

My environment is a 3-tier SharePoint 2010 Architecture: Running on SQL Server 2012 Enterprise Edition

WF1

WF2

Both Web Fronts are NLB, and we have they following Application Servers

APP1 - Dedicated for Search Service (Query and Indexing Component).

APP2 - Has been configured to run all the BI services - i.e. PowerPivot, Excel, Secure Store, Claims to Windows Token, Visio Graphics, and BCS.

SO far I tried the following (and I get the error message - see in brackets):

1. Have Excel, Secure Store, and Claims to Windows Token running on both Web Front Ends and Application Servers (Error I recieve: "Unable to refresh data for a data connection in the Workbook. Try again or contact your system administrator. The following connections failed to refresh: Data")
2. Have Excel, Secure Store, and Claims to Windows Token running on WF1 and APP2 only (Error I receive: "Unable to refresh data for a data connection in the Workbook. Try again or contact your system administrator. The following connections failed to refresh: Data")
3. Changed Claims to Windows Token service to run as a Local System
4. Data Location has been added as a Trusted Data Location.

Do you guys have a clue why is this happening?

I spent the entire day Yesterday swapping services between the servers but I still cannot go through.

Answer 1

Confirm that the login used to access the data source (the user’s identity in the case of Windows authentication, or the Secure Store stored credentials) has Read access to the data source.This error may also have other causes, such as an invalid query (for example, an incorrect table name). Check the ULS logs or the data source’s logs for further information.

Excel services data refresh troubleshooting can be cubersome and tiring if you are not looking at the correct causes. Most of the time its due to some minor mis configuration or missing steps.

Additionally, since data refresh primarily follows different flows for the choice of authentication types like Windows , Secured Store or Unattended service accounts your scope of troubleshooting widely increases ranging between changes to kerberos delegations , gpos for service accounts , sharepoint excel service configurations and permissions in the data sources. Considering this, Microsoft did release a detailed flow chart for Excel Service Data Refresh. This is the most useful reference you can have for troubleshooting excel services.

Answer 2

You may need to update your MSOLAP dll version to MSOLAP.5, if it is currently MSOLAP.4, because you are running SQL Server 2012 with SharePoint 2010. You can check the version in Central Administration > Excel Services Application > Trusted Data Providers. Make sure you have MSOLAP.5 installed. If not, you can follow this link on how to do it: https://msdn.microsoft.com/en-us/library/ee210608(v=sql.110).aspx

Also, after installing the correct dll, make sure you set up your Secure Store Service correctly if SharePoint and SQL Server are on different domains: https://msdn.microsoft.com/en-us/library/ee210671(v=sql.110).aspx