none
Unable to refresh connection when opening file in Excel Services

    Question

  • Hi all.

    We are
    facing a problem on SharePoint 2013 when refreshing an excel report in excel
    services.

    We have
    several Excel files containing reports (pivot tables, pivot graphs, slicers)
    that display datas from SQL server (in relationnal model). These reports are
    based on connexion files (ODC) stored in a SharePoint datasources library. The
    reports are stored in a powerpivot library in SharePoint.

    When we open
    the report in Excel Service (browser) we got a popup with the message
    "Could not refresh datas, the following datasource cannot be refreshed :
    XXX" with XXX stands for the connexion name.

    Our
    environment is based on the following :



    • SharePoint enterprise 2013
           SP1
    • SQL server 2012 enterprise
           SP1
    • 2 Web Front End servers
    • 2 Excel Calculation Services
           servers hosting SSAS in SharePoint mode (for PowerPivot).
    • PowerPivot for SharePoint
           installed on all servers in the farm.
    • Kerberos activated on the web
           application.
    • Excel files are created with
           Excel 2013.

    We double
    checked the following parameters :



    • Excel Services : SSAS
           instances referenced in the DataModel section .
    • Secure Store : 1 target
           application is declared and the name of the target application is declared
           in the properties of the Excel connexion.
    • User credentials for the
           target application are granted read privileges on the SQL database
           containing the datas.

    What we need
    to achieve is the automatic data refresh when a user open the Excel file in
    Excel Services and the account used for data refresh should be the credentials
    defined in the secure store.

    Thanks in
    advance for any tips to make this work.


    Tuesday, August 05, 2014 6:49 AM

All replies

  • Hi roche,

    Have you take a look at the following article regarding setting up Excel Services to Refresh Workbooks When Opened? This document describes how to setup SharePoint SharedServices for Excel Services, and how to setup the ODC connection to refresh workbooks on open, please see:
    http://blogs.office.com/2008/03/24/setting-up-excel-services-to-refresh-workbooks-when-opened/

    Regards,  


    Elvis Long
    TechNet Community Support

    Thursday, August 07, 2014 8:33 AM
    Moderator
  • Hi Elvis.

    Thanks for the link.

    I double checked every settings described in the article and everything is properly configured.

    Thursday, August 07, 2014 8:57 AM
  • You might take a look at the "Authentication Settings" selected in the ODC file(s) being used.  This typically defaults to "Use the authenticated user's account".  If you're wanting a specific stored account or unattended service account, you'd want to make sure the appropriate option is selected.

    Sunday, August 17, 2014 9:11 PM