[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Sharepoint 2010 Excel Services refresh account

Posted on 2011-05-02
Medium Priority
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
  • 3
  • 2

Accepted Solution

pritamsh earned 2000 total points
ID: 35504075
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

ID: 35512520
This did not do the trick, unfortunately.

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

Expert Comment

ID: 35512570
I mentioned in my comment changing the authentication settings in connections properties.

Expert Comment

ID: 35512616
Even how to set it was mentioned..

Author Comment

ID: 35512646
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
A while back, I ran into a situation where I was trying to use the calculated columns feature in SharePoint 2013 to do some simple math using values in two lists. Between certain data types not being accessible, and also with trying to make a one to…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

834 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