“External Data Refresh Failed An error occurred during an attempt to establish a connection to the external data source . The following connections failed to refresh:” error in SharePoint 2013 PowerPivot data refresh

While practicing with SharePoint Excel Services and Power Pivot office web apps, when refreshing the data I got an error saying that

“External Data Refresh Failed……”

By checking the error found a solution from TechNet blog. When using Office web apps in SharePoint 2013, Excel files will be viewed in WOPI (Web Application Open Interface) and edited through Office Web Apps. When SharePoint farm is configured to use excel apps, Excel services and power pivot features will depend on the Excel web app server configurations.

Excel web app runs in SharePoint View ode and Office web server view mode. In URL, if see “xlviewer.aspx” it is running on SharePoint Mode. If URL Contains “WopiFrame.aspx”, excel web app is running in “Office Web Apps Server view Mode”. If we use OWA Server view mode, to view the workbooks, Some BI features like Excel Web Access Web part, Refresh ODataConnections web part, View and interact PowerPivot view/data models...

We have to apply suppression setting on the farm to work with slicers and refresh data. So that we can edit the document in browser with Office Web Apps. We can suppress the Office webapps using following PowerShell command,

New-SPWOPISuppressionSetting –Extension “XLSX” -Action “view” 

SharePoint 2013 PowerPivot manual refresh failed error "An error occurred during an attempt to establish a connection to the external data source… “

While working with SharePoint 2013 PowerPivot, I got an error "An error occurred during an attempt to establish a connection to the external data source… " as shown the image below, while refreshing all the connections.

In logs I have found the error like, “Current Identity is Claims Identity. We need to get a Windows Identity”

We can view PowerPivot workbooks using excel services in browser. To refresh the data in power pivot tables, Claims to Windows Tokens (C2WTS) will be utilized. If C2WTS is running with a domain account, that account needs to have permissions to the SharePoint server. Account must be in Administrator for SharePoint server where the C2WTS is running and service should be run on the name of that account.

Check the account that running the C2WTS service and add “Act as part of the operating system” permissions to that account. We can find the account in services. (run ->services.msc)

To access the Server’s local security policy, go to User Rights Assignments (Start-> Administrative Tools -> Local Security Policy -> Local Policies -> User Rights Assignments)

The account running for C2WTS, need to add permissions for “Act as part of the operating system” 

Configuring automatic data refresh in SharePoint 2013 PowerPivot

After publishing the PowerPivot workbook in SharePoint, we can configure the scheduling for automatic data in workbook. To configure the data refresh we must have contribute level permissions to the workbook and must be windows domain account. Workbook must be checked-in at the time of refresh.

We must have to activate the PowerPivot Integration for Site Collection. To activate that, navigate to site collection features and search for PowerPivot Integration. We cannot find the feature if PowerPivot is not installed properly. In my previous post I have mentioned about PowerPivot installation for SharePoint.

To schedule the automatic refresh, navigate to SharePoint library that contains the workbook and Click on Manage PowerPivot Data Refresh as shown below.

Check Enable check box in schedule definition page Data Refresh section.

We have to specify the type of schedule and when we wanted to refresh the data in Schedule Details section.

In the Earliest Start Time section, we have two options. After business hours specifies the after the business hours. Default value will be 8PM. A specific time specifies the time within 15 min interval.

We have to specify the e-mail address in E-Mail Notification section to notify the data refresh failures.

Enter the account information for SharePoint PowerPivot for data refresh in workbook. Account must have contribute or higher permissions to the work book as mentioned earlier. We have three options here, to use predefined account, click on Use data refresh account configured by the administrator. Connect using following Windows user credentials option to specify the own windows account. Connect using the credentials saved in Secured Store Services (sss) to log on to the data source to run in a specified windows account that defined in Secured Store service. For this option, Admin must have the SSS ID to store the account information.

If we want to refresh all the data sources, select All data sources check box in Data Sources section to data refresh. Click on OK.

Install PowerPivot Add-in for SharePoint 2013

PowerPivot for SharePoint collection of back and middle-tier services that allows getting PowerPivot data in SharePoint farm. If we are using Excel PowerPivot that contains the analytical data in SharePoint site, we must have to use PowerPivot services in server.

PowerPivot for SharePoint 2013 is an installation package called spPowerpivot.msi that deploys the PowerPivot for SharePoint 2013 files. We can download the .msi file from http://www.microsoft.com/en-us/download/details.aspx?id=35577 URL.

After downloading the .msi file, Right click on it and select install command. We can see the see the screen that checks the machine for all installations.

After completing with checking machine installations, We can see the screen with default option selected as shown the image below. Click on OK button.

We can see the following screen for inputs. Here we have to pass Default Account Username, Password to provision the services and PowerPivot service application pool. We have to specify a custom account instead of built-in account. If we provide the built-in accounts, tool will blocks the configurations. Database Server is the server name. We have to specify the Passphrase, which will be useful whenever new servers are added to the farm and PowerPivot Server for Excel Services value like ServerName/PowerPivot.

After entering the values click on Validate button to validate the inputs.

Once we are done with successful validations, Click on Run button. We can see the warning alert says “Configuration settings will be flagged...” click on Yes button.

PowerPivot for SharePoint will install. We can see installation successful message after completion of installation.

We can check PowerPivot installation by checking following things,

Services in SharePoint central Admin (Application Management -> Manage Service Application -> New). SQL Server PowerPivot service Application in the list or not.

Services list (Application Management -> Manage Services in the Server), check for SQL Server PowerPivot System Service is there or not.

Farm features (System Settings -> Manage farm features), check for PowerPivot Integration Feature.