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.