Hi All,

I have deployed a Data Model Excel on SharePoint 2013. In Data Model, initially I used SQL Server Authentication to pull the data from SQL Database.  Then I scheduled the External Data Refresh via Manage Data refresh on SharePoint. But it failed to refresh the data model by throwing following error- 

"Call to Excel Service returned an error."

Then I used Windows Authentication instead of SQL Server Authentication in Data model to pull data from Sql Server. And suddenly, the External Data refresh started working. So now there is no issue with External data refresh. 

Later on, I created a new excel report out of deployed data model and uploaded it to SharePoint. Multiple users can access and slice/dice this report on SharePoint (Excel Services). However, when they open report as Open in Excel (locally), they are not able to interact with report. 

I really don't understand this behaviour as the report works fine on Excel Service (SharePoint) but doesn't when we open it in Excel. Is there any difference in Authentication policies?

Currently, I have used the Authenticated User Account option in  Excel Service Authentication Settings of Connection Properties shown below - ( Even I marked the Save Password Option )

Can anybody help me to resolve this issue?

Thanks & Regards,

Rameshwar Pawale

• Edited by Rameshwar Pawale Thursday, July 02, 2015 12:17 PM

Hi Rameshwar,

According to your description, you need to know the difference in authentication policies between Excel services and open in Excel, right?

Excel Services in SharePoint Server 2013 is a shared service that you can use to publish Excel 2013 workbooks on SharePoint Server. The published workbooks can be managed and secured according to your organizational needs and shared among SharePoint Server 2013 users, who can render the workbooks in a browser.

When open report as Open in Excel (locally), the report was download to your computer and open it. In this case, there is no user configured to connect to database, so they are not able to interact with report. When open the report in SharePoint site, it used the authentication you configured to connect to database.

https://technet.microsoft.com/en-us/library/ee424405.aspx?f=255&MSPPError=-2147217396

Regards,