Tips to resolve “Unable to Refresh the Excel Connection in Excel Webaccess Webpart” in SharePoint 2010

Aftwe users start to use excel service on SharePoint 2010, we have encoutered many issues. One os the top issues is users could not refresh excel workbook and got “Unable to Refresh the Excel Connection in Excel Webaccess Webpart”. 

As you might not know that all authentication and communication inside SharePoint is through claim. Any SharePoint service that relies on the Claims to Windows token service (C2WTS) must use Kerberos constrained delegation to allow the C2WTS to use Kerberos protocol transition to translate claims into Windows credentials. Excel Services along with PerformancePoint Services and Visio Services are the three service applications and products require the C2WTS and Kerberos constrained delegation.

In most cases, we will need to configure the SharePoint Server Excel Services service account for Kerberos constrained delegation to the SQL Server service. You may use security store also. The scenario is detailed in the following screen shot as explained in Microsoft forum.

Authentication in this scenario begins with the client authenticating with Kerberos authentication at the web front end. SharePoint Server 2010 will convert the Windows authentication token into a claims token using the local Security Token Service (STS). The excel service application will accept the claims token and convert it into a windows token (Kerberos) using the local Claims to Windows Token Service (C2WTS) that is a part of Windows Identity Framework (WIF). The excel service application will then use the client’s Kerberos ticket to authenticate with the backend DataSource.

At this point, you might be clear that we need to set up the following three setting in order to make excel refresh work.

1. Enable Kerberos on the webapp to delegate user account
2. Start C2WTS on the farm to transition to translate claims into Windows credentials
3. Add Trusted Data Connection Libraries for connection files and Trusted File Location for excel file

Yes, you have to have all THREE configured correctly in order to resolve this issue. You could refer to Microsoft plan and instruction to set up Kerberos for excel service.