How to configure PowerPivot for SharePoint scheduled data refresh for a workbook using PowerPivot connection embedded SQL credentials to the data source (with “Save my password” option checked)


Issue

This solution was tested with PowerPivot 2012 for SharePoint 2013.

If you need to get PowerPivot scheduled data refresh to work for a PowerPivot workbook that has a PowerPivot add-in connection in it that uses embedded SQL credentials for refreshing from the data source, like below, then there are a few important additional settings that need to be set in order for it to work.

Solution

In the workbook connection (Data > Connections, the “Save Password” option here also needs to also be selected.

Also the Excel Services Authentication should be set to "Use the authenticated user's account".

Finally, when setting up the refresh schedule for the PowerPivot workbook in the PowerPivot gallery ("Configure Schedule"), chose these options at the bottom:

  • "Use the Default Schedule"
  • "Use the credentials contained in the workbook"

If the settings above are not used, then what will happen is:

  1. When you open the workbook in Excel, and then open the PowerPivot add-in, and then attempt to refresh the PowerPivot add-in connection, you will get prompted there for the SQL password (this is what causes the PowerPivot scheduled refresh to fail in the background).
  2. The PowerPivot scheduled refresh will fail with an error in the refresh history: "Call to Excel Services returned an error".
  3. You will see errors in the ULS logs like these:

01/06/2015 12:49:52.80   w3wp.exe (0x088C)            0x1764  Excel Services Application External Data        ajesn       Medium                ASPPHOST::ShowEngineError: Out of line object 'DataSource', referring to ID(s) '78c26543-e488-4bf2-9e41-9d83ea35b126', has been specified but has not been used.  OLE DB or ODBC error: Login failed for user 'SQLuserACCT'.; 28000.                 

01/06/2015 12:49:52.80   w3wp.exe (0x088C)            0x1764  Excel Services Application External Data        ajeso       Information                Failed to create an external connection or execute a query. Provider message: Out of line object 'DataSource', referring to ID(s) '78c26543-e488-4bf2-9e41-9d83ea35b126', has been specified but has not been used.  OLE DB or ODBC error: Login failed for user 'SQLuserACCT'.; 28000.  , ConnectionName: ThisWorkbookDataModel, Workbook: ExcelWorkbookName.xlsx.          

01/06/2015 12:49:52.99   w3wp.exe (0x088C)            0x286C  Unknown               AS SPClient            513         High        Hit ECS exception name: 'ExternalDataRefreshFailed'; message:'An error occurred while working on the Data Model in the workbook. Please try again.    We were unable to refresh one or more data connections in this workbook.  The following connections failed to refresh:    WorkbookConnectionName  '       

01/06/2015 12:49:53.27   w3wp.exe (0x088C)            0x286C  PowerPivot Service             Data Refresh         99           High                EXCEPTION: System.InvalidOperationException: Call to Excel Services returned an error. —> Microsoft.AnalysisServices.SPClient.Interfaces.ExcelServicesException: ECS failed with non-zero return status. First error is name='ExternalDataRefreshFailed'; message='An error occurred while working on the Data Model in the workbook. Please try again.    We were unable to refresh one or more data connections in this workbook.  The following connections failed to refresh:    WorkbookConnectionName

 

 

Comments (3)

  1. F Watson says:

    Thank you for your detailed explanation… however, I still can’t get it to work! I keep getting the "Call to Excel Services returned an error" message – any other advice?

  2. Greg W says:

    Same. Followed steps and still get same error "Call to excel services returned an error"

  3. Arun Balu says:

    hi guys,

    I guess its too late to reply to your post but i hope it helps. I faced the same error but got it resolved. There are 2 steps to take note of when saving passwords using powerpivot.

    step1: you setup a database connection and enable “save password” and refresh the data in power pivot window.

    Step2: once this is done, you need to store the password in your workbook. this can be done by selecting power pivot -> Data -> connections -> Definition -> “Save Password”. Under authentication settings, choose the first option “use an authenticated service account”.

    Step3: Now try refreshing the data again within the workbook and Excel will prompt you to input the password. Once this is done and refreshed, Excel now has your password stored in it.

    Step4: Do note, the password is and SHOULD BE visible only to admins of the report. Because, everyone with admin rights can view the database password in clear text.

    Once this is done. Now upload your report in Sharepoint 2013 and you should be able to see the refresh work smooth.

    Hope this helps you or anyone else who is stuck with it.

    Here’s the link for a better understanding.

    https://msdn.microsoft.com/en-us/library/jj879294.aspx#bkmk_scheduled_refresh

    Cheers,
    Arun

Skip to main content