Configuring The Unattended Service Account in SP 2010

In the process of configuring Excel Services on my test farm, I ran into an issue with enabling data refresh on Excel spreadsheets. I could get the spreadsheet to display in SharePoint, but I couldn't refresh the data.

I traced the issue to the fact that the unattended service account wasn't working properly. Further digging led me to the fact that my Target Application in the Secure Store Service application wasn't configured correctly.

Here are the steps I took to create a properly configured Target Application, and use that to configure the Unattended Service Account in Excel Services.

The first step is to create a valid Target Application in the Secure Store Service application:

Go to Cenral Administration > Manage Service Applications > Secure Store Service.

Provision the Target Application by giving it a Target Application ID, Display name, etc as shown below. It is important to select "Group" as the target application type, not individual which is the default.

Click Next and accept the defaults on the second screen. Click Next and specify the Target Application administror(s) and desired. The next box is very important. The only way I was able to get data refresh to work was to set the SharePoint Farm Account as the members. Simply enter the name of the farm admin account in the members box and validate it.

Click OK and create the Target Application. Once it's been created, you need to set the credentials for the account, so it can be encryped into the Secure Store database.

To set the credentials, select the dropdown on target Applicaton you just created and choose "Set Credentials". Enter the name of the farm account you entered as 'members', and enter the password.

At this point you've successfully created and provisioned the Target Application. The final step is to set the unattended execution account in Excel Services.

To do this, go to Central Administration > Manage Service Applications > Excel Services > Global Settings.

Scroll to the bottom of the settings page a type in the Target Application ID of the Target Application we just created. Click OK.

Once I completed these steps, I was finally able to get my Excel spreadsheets to properly refresh in Excel Services.

No Excel Calculation Services is available in the farm

In the course of building my new SharePoint 2010 farm, when attempting to view an Excel Spreadsheet in the browser, I received the error below:

Not exactly helpful.

I dug into the log files and found an error that stated "No Excel Calculation Services is available in the farm". The next step was to figure out where this service was located and why it was not available.

A bit of digging in Central Administration led to Central Administration > Services on Server. Once there it was obvious what the problem was. The Excel Calculation Service was stopped.

I started the service and attempted to open the original spreadsheet. This time, I got a different error message, indicating that the operation could not be completed, and asking me to wait a few minutes and try again.

While I waited, I recycled the application pool and restarted the browser. This time, my spreadsheet loaded as expected. Problem solved.