none
Data Refresh failed for published PowerPivot workbooks

    Question

  • Hi,

    I'm operating with a test SharePoint farm, composed by two SP server in load balancing and adb server with SQL Server 2008 R2 EE.

    I've installed PowerPivot, I've enabled the PowerPivot Management Dashboard (created a PowerPivot service application), I've created a web app + site collection + site for the PowerPivot publishing. I can successfully publish my workbooks.

    When I implement the data refresh for a my workbook, the data refresh fails with this error message: Exception from HRESULT: 0x80070005 (E_ACCESSDENIED). For the data refresh, in the Credentials section and Connect using the following Windows user credentials option, I've specified my Windows account that can access to the db linked inside the PowerPivot workbook. Moreover, in the Data Sources section and Specify a user account option I've specified the same my account. My account is inserted as a SharePoint user (it is one of the farm administrators).

    To bypass this error, I've create a Secure Store service application, named PowerPivotUnatAccount, and the related Target Application named PowerPivotUnattendedAccount. I've assigned this Target Application to PowerPivot service application (in the PowerPivot Unattended Data Refresh Account section) and I've changed the Credentials section of the Data Refresh setting in to the "Use the data refresh account configured by the administrator" option; no changes for the Data Sources option. I've obtained another error: "Target application not found (application id: PowerPivotUnattendedAccount)."

    Now, I'd like to have any suggests to solve this issue. Can it depend to the load balancing architecture for the SharePoint Farm?

    Many thanks

    P.S.: the source db for the PowerPivot workbook is on the db server of the SharePoint farm.
    • Edited by pscorca Saturday, December 24, 2011 9:07 AM update
    Saturday, December 24, 2011 8:55 AM

Answers

  • Hi, I've solved the issue!

    I'm operating on the first SharePoint server as farm administrator.

    I've a target application for the Secure Store service, named "PowerPivotDataRefresh".

    I've operated these settings:

     
     

    a.   PowerPivot Service Application

    PowerPivot Unattended Data Refresh Account = PowerPivotDataRefresh

    b.   Excel Services Application

    Global Settings è Application ID = PowerPivotDataRefresh

    c.   Central Administration è Application Management

         Configure service application associations è choosing the PowerPivot site and clicking on Default (Application Proxy Group) è setting the Secure Store Service as the default one

    d.   Manage Data Refresh (di un workbook) è Configure Schedule …

    ·       Data Refresh = Enable

    ·       Credentials = Use the data refresh account configured by the administrator

    ·       Data Sources è Data Source Credentials = Use the credentials contained in the workbook

     

    Wednesday, December 28, 2011 10:27 AM

All replies

  • Hi, I want to add an observation.

    In the Data Refresh History page of my test PowerPivot workbook I've seen the "NT AUTHORITY\IUSR" account for the "Las Updated By" information.

    Thanks

    Saturday, December 24, 2011 9:13 AM
  • Hi Pscorca,

    The data refresh job is trying to use a target application that provides credentials for a data source connection string. This might be target application you specified in the data sources section of the schedule page. Or, if you did not specify credentials in the data source page, it is the target application used to run the data refresh job, which is also used to access external data if the connection string includes Integrated Security=SSPI. For more information about how Secure Store Service target applications are used in PowerPivot data refresh, see Enable and Configure Data Refresh 

    For more information about Troubleshooting PowerPivot Data Refresh, please see: http://social.technet.microsoft.com/wiki/contents/articles/troubleshooting-powerpivot-data-refresh.aspx 

    Thanks,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, December 26, 2011 6:47 AM
  • Hi Challen, thanks for your suggests, useful but not enough.

    I've executed a new serie of proofs, accessing to the first SP vm as farm administrator. My test workbook connects to a SQL Server db with my Windows account (integrated authenticatio). The connection string inside the workbook is similar to: "Provider=SQLOLEDB;Data Source=MICROSOFTBI2008;Persist Security Info=False;
    Integrated Security=SSPI;Initial Catalog=DB_PowerPivot". Farm administrator and my Window account can read and write to the SQL db underlying the PowerPivot workbook. Moreover, both accounts are inserted in SharePoint as contributors.

    Accessing as farm administrator, I can successfully specify the related administrator account in the "Credentials" section, "Connect using the following Windows user credentials" option, and for the "Data Source Credentials" I can successfully use the "Use the credentials contained in the workbook" (my Windows account) option or "Specify a user account" (a SQL Server login).

    But yet I've a "Target application not found" error when I want to choice the "Use the data refresh account configured by the administrator" for the Credentials section. I've created a target application for the Data Refresh inside Secure Store Service and I've assigned the related id to the PowerPivot service application. The reason for this error isn't very clear for me!

    Any suggests to solve this issue? Thanks

    Tuesday, December 27, 2011 1:17 AM
  • Any ideas, please? Could it depend to the SharePoint in load balancing?

    Thanks

    Tuesday, December 27, 2011 5:56 PM
  • Hi, I've solved the issue!

    I'm operating on the first SharePoint server as farm administrator.

    I've a target application for the Secure Store service, named "PowerPivotDataRefresh".

    I've operated these settings:

     
     

    a.   PowerPivot Service Application

    PowerPivot Unattended Data Refresh Account = PowerPivotDataRefresh

    b.   Excel Services Application

    Global Settings è Application ID = PowerPivotDataRefresh

    c.   Central Administration è Application Management

         Configure service application associations è choosing the PowerPivot site and clicking on Default (Application Proxy Group) è setting the Secure Store Service as the default one

    d.   Manage Data Refresh (di un workbook) è Configure Schedule …

    ·       Data Refresh = Enable

    ·       Credentials = Use the data refresh account configured by the administrator

    ·       Data Sources è Data Source Credentials = Use the credentials contained in the workbook

     

    Wednesday, December 28, 2011 10:27 AM
  • Sorry for the delay, I am happy that you solve the issue by yourself, also thanks for sharing the experience,  which must be helpful for other communites who have the same issue. So I mark it as anwser.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, December 29, 2011 6:20 AM
  • I was also getting same error & followed these steps. Now it is working.

    G Goyal

    Tuesday, October 29, 2013 12:21 PM