Sharepoint 2010 Excel Services refresh account

Posted on 2011-05-02
Last Modified: 2012-05-11
a workbook with powerpivot data with embedded data connection is published to our sharepoint site. The workbook is set up with a unattended refresh account and the data is refreshed every night. This works ok. But the problem is this:
After the workbook is refreshed, if a user with "View only" permissions access it first then this error occures and the user can't oen the workbook:

Cannot open this workbook because it contains external data that could not be refreshed. You may not have permission to use the data connections, or the data connections may not be responding to the refresh operation.

If a user with Read permission (or contribute or full access) open the workbookfirst then the workbook is opened ok. AND the user with View only access can access it afterwards also. The problem is only if the View only user opens it first after refresh.

Any ideas how this can be fixed?
Question by:abgsc
    LVL 4

    Accepted Solution

    Try below mentioned steps;

    1. Expand Documents -> Excel Reports

    2. Right-click and edit the excel spreadsheet

    3. Click on Data -> Properties (For Connections)

    4. Click on the Definitions Tab -> Authentication Settings

    5. Here's the bug.  It's on Windows Authentication.  Should work, right?  Wrong.

    6. Click OK and OK again.  Now it will work.

    7. Save the spreadsheet and now access the dashboard through MOSS 2010.

    P.S:To get rid of the security pop-up, just enable all external connections via the Trust Manager in your Office product.


    Author Comment

    This did not do the trick, unfortunately.

    I found a solution, needed to change authentication method in the connection
    LVL 4

    Expert Comment

    I mentioned in my comment changing the authentication settings in connections properties.
    LVL 4

    Expert Comment

    Even how to set it was mentioned..

    Author Comment

    no, you misunderstand, I needed to change it from windows authentication to None, then it will use the authentication in the connection settings itself and not the win user id

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Pimping Sharepoint 2007 without Server-Side Code Part 1 One of my biggest frustrations with Sharepoint 2007 in the corporate world is that while good-intentioned managers lock down the more interesting capabilities of Sharepoint programming in…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now