PowerPivot Data Refresh - Access Denied


  • Ok...I created a PowerPivot workbook using a SSRS Data Deed (*.atomsvc) and Published to SharePoint in a PowerPivot Gallery. I have a Secure Store Service for PowerPivot called PowerPivotDataRefresh and I Set the Credentials. I keep getting an Access Denied error in the Refesh Schedule log and when I check the server error logs I see this:

    Log Name:      Application
    Source:        Microsoft-SharePoint Products-Secure Store Service
    Date:          22/10/2010 19:41:35
    Event ID:      7493
    Task Category: Secure Store
    Level:         Error
    User:          DS\User
    Computer:     Server Name
    The Microsoft Secure Store Service application Secure Store Service failed to retrieve credentials. The error returned was 'Access is denied.'. For more information, see the Microsoft SharePoint Products and Technologies Software Development Kit (SDK).
    Event Xml:
    <Event xmlns="">
        <Provider Name="Microsoft-SharePoint Products-Secure Store Service" Guid="{73541538-24DA-4282-AE1C-3A6321C23FB8}" />
        <TimeCreated SystemTime="2010-10-22T17:41:35.029766000Z" />
        <Correlation ActivityID="{B7ADA1D5-A91B-44AA-88C4-9F6E532AE1E2}" />
        <Execution ProcessID="7148" ThreadID="5092" />
        <Security UserID="S-1-5-21-861567501-413027322-1801674531-1543315" />
        <Data Name="string0">Secure Store Service</Data>
        <Data Name="string1">Access is denied.</Data>

    There are two VM Servers, one Web one Data. But the error is so generic I can't quite figure out what access is causing the error.

    Can anyone provide any guidance?

    Much appreciated!


    Freitag, 22. Oktober 2010 18:10

Alle Antworten

  • Hi Emilio!

    What's the user you've mapped in the Secure Store target application PowerPivotDataRefresh? Is it the farm account? If it's not, try adding it to the PowerPivotDataRefresh and check if it works.



    Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.
    Samstag, 23. Oktober 2010 07:45
  • Mario,

    I've tried both:

    • when using a regular appID (admin on both servers) the Target Application works and let's me save a Data Refresh Schedule using the SSS Target App ID
    • when using the Farm account (again admin on both servers) the Target Application saves...but when I add it to the Data Refresh Schedule it will not save the schedule...keeps getting kicked back saying that the Target App is not configured correctly

    I set it up the same way and even Set Credentials in both cases so I am very confused!

    The 'Access Denied' error is very ambiguous so I am not sure what the issue is.



    Donnerstag, 4. November 2010 15:46

    Hi Emilio,


    Let me see if I got it right.

    You have a SharePoint farm account, let's say, yourdomain\farmaccountuser, and this very user (yourdomain\farmaccountuser) is also mapped in the Secure Store's PowerPivotUnattendedAccount target application.

    You can do this (map the farm account do the PowerPivotUnattendedAccount target application) by:


    a.       Click Central Administration, Application Management, and Manage Service Applications. Select Secure Store Service, and then click Manage at the top of the page.

    b.      On the ribbon at the top of the page, click New.

    c.       On the Target Application Settings page, click on "PowerPivotUnattendedAccont".

    d.      On the Credentials Field page, accept the default settings and then click OK. 

    e.      On the Target Application Administrators page, enter a user account that has access to this Target Application. You can enter the account you are using to create the Target Application ID (your farm account, for instance). 

    f.        To return to the Manage page for the Secure Store Service Application, click OK. You should be able to see your Target Application ID on this page.

    g.       To set the credentials for this application ID, select the ID check box for your ID and then on the ribbon at the top of the page, click Set.

    h.      On the Set Credentials page, next to Credential Owner, type the name of the PowerPivot Service account (this is the account you specified as an Analysis Services administrator when you ran Analysis Services setup). Next to Windows User Name and password, type the credentials of a Target Application administrative account. This should be one of the user accounts you specified in step e or the farm administrative account. Note that the two accounts that you need to specify can be the same if the PowerPivot service account is also a Target Application administrative account for your application.


    These instructions are off this white paper:


    Please let me know if that helped.




    Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.
    Donnerstag, 4. November 2010 16:34
  • Mariano,

    Many thanks this sorted my problem which was similar; I needed to add my farm account to the analysis security ..


    Donnerstag, 26. Januar 2012 07:47
  • Hi Emilio,

    I'm facing the same issue.

    Did you ever solve it?

    Kind regards

    Kind regards

    Samstag, 25. Februar 2012 13:59
  • That really helped. Thanks a lot.


    Mittwoch, 24. Juli 2013 00:58
  • That helped me, I've just added the Target Application ID as described here. I don't know what was missing...
    Montag, 18. November 2013 11:36