I recently was trying to spin up a new SharePoint 2010 development VM with Excel services and I ran into a rather interesting issue. I had published out an Excel 2010 workbook to my SharePoint 2010 server, I would go an open the spreadsheet, and it would render with default data but whenever I changed a value on the slicer or refreshed the data connections I was getting the following error:
The data connection uses None as the external data authentication method and Unattended Service Account has not been configured. The following connections failed to refresh
The thing about this error was that I really believed that I had the Unattended Service Account for my data connection set up properly. I had done the following:
- I set up Excel Services 2010 in Central Admin to trust both the document and data connection libraries.
- I had set up the Data Connection Authentication Settings to None and saved that into the data connection library.
- I had created a target application in the Secure Store Service which had the target application None.
- I had subsequently credentials on the Target Application to an account that has access to the database.
I would get this error even when I was logged in with the administrator account.
The issue actually had to do with the way I had configured the Target Application in the Secure Store Service for my unattended service account. For the Target Application I had set the Target Application Type to Group. However on the third step of the configuration wizard for the Target Application, I had set the Members to just a domain account. The solution was to set it to a group. Once I changed it to xxx\Domain Users everything started working perfectly.
Here is a reference that gave me the clue on what I had done wrong - http://howardmorgenstern.wordpress.com/2010/02/17/configure-excel-services-2010-for-data-refresh/