Sharepoint 2010 Excel Services refresh account

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?
Who is Participating?
pritamshConnect With a Mentor Commented:
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.

abgscAuthor Commented:
This did not do the trick, unfortunately.

I found a solution, needed to change authentication method in the connection
I mentioned in my comment changing the authentication settings in connections properties.
Even how to set it was mentioned..
abgscAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.