Step by step configuration of Excel Calculation Services (ECS) when using Kerberos
There is no better way of learning something than to observe someone doing it step-by-step. I gathered information from different sources during the beta phase of SharePoint. So my thanks go to Darin Roulston, Eric Adams, Tracy Paddock, Jon Waite and other brave people who created the eco-system for all of us.
The topology is as follows:
• MSF1 is the WFE Server and the site collection is https://msf1
• MSF2 is the APP Server
• DC is the domain controller and SQL server (in real life don’t install SQL in the domain controller)
• Excel Calculation Services account is contoso\xlsvcs and service account running SQL is contoso\SQLSvc
• Domain is contoso.com
A few information before you start:
The Claims to Windows Token Services (c2WTS) is installed by WIF which is part of SharePoint pre-requisites (you don’t have to worry about this installation). The configuration file is called c2wtshost.exe.config, located in c:\Program Files\Windows Identity Foundation\v3.5. The configuration does not allow any user to access the service by default, thus SharePoint add the security group WSS_WPG to enable access from SharePoint services. See screenshot below.
If you have not done yet, install and enable the following via the SharePoint PowerShell Management Console (run as Administrator).
Install-SPFeature –path "DataConnectionLibrary"
Enable-SPFeature –url https://msf1 –identity "DataConnectionLibrary"
Steps:
1. On all the servers running Excel Calculation Services, verify the Claims to Windows Token Service (c2WTS) is Started and set to Automatic.
VERY IMPORTANT: There is a known issue with the C2WTS where it may not automatically startup successfully on system reboot . A workaround to the issue is to configure a service dependency on the Cryptographic Services service:
a) Open the command-prompt window.
b) Type: sc config c2wts depend= CryptSvc
c) Find the Claims to Windows Token Service in the services console.
d) Open the properties for the service.
e) Check the Dependencies tab. Make sure Cryptographic Services is listed.
f) Click OK.
From Central Administration > Manage Services on Server page:
2. Identify the Service Application service account for Excel Services (example: contoso\xlsvcs)
3. Create the service account to be running SQL Server.
a. Open Active Directory Users and Computers and add SQLSvc in the list of users, choose a password and uncheck “requires user to change the password” and make sure password will not expire.
b. Open SQL Configuration Manager from the Start menu,
c. Right-click MSSQLSERVER and click Properties. On the Log On tab of the dialog that appears, click ‘This account’ and enter contoso\SQLSvc and choose a password. Click OK. On the dialog box that appears confirm the restart of the service.
d. Make sure the service is running after restarting.
1. Using the SETSPN tool or the ADSIEdit console, create Service Principal Names (SPNs) for each service account as follows:
a. For each Excel Services Server, add a SPN for the Excel Services service account.
Setspn –a HTTP/MSF2 contoso\xlsvcs Setspn –a HTTP/MSF2.contoso.com contoso\xlsvcs |
2. Set the SPN for the SQL Service account.
Setspn -a MSSQLSvc/DC:1433 contoso\SQLSvc Setspn -a MSSQLSvc/DC.contoso.com:1433 contoso\SQLSvc |
3. Next we will setup our delegation paths. There are two of them: one for the Claims to Windows Token Service, the other for the Excel Service Application.
a. First we will setup the Claims to Windows Token Service delegation path to the back-end data source.
i. In Active Directory Users and Computers, ensure each Computer Account for each ECS Server in the farm is set to constrained delegation to the back-end service. Constrained delegation means that Kerberos delegation is constrained to a specific service.
4. In Active Directory Users and Computers, find MSF2 open its Properties.
a. Click the Delegation tab. Change the Delegation to Trust the computer for delegation to specific services only and select Use any authentication protocol. See example below. Then click the Add button for the next dialog.
5. The next window appears. Click Users or Computers.
At this point, you will need to enter either the SQL Server name or the SQL Server service account.
6. If the SQL server is running under ‘Local System’ then enter the computer name in the following dialog.
7. If the SQL server is running under a service account (like this example), then enter that account. Click OK after you have entered the appropriate Computer or User account.
8. Assuming all steps were completed correctly to this point, you will see the MSSQLSvc principal entry available as a selection. Select your desired entry and click OK.
9. This will be the result of your selection:
10. Next, we will setup the Excel Service Application delegation path. In Active Directory Users and Computers, make each Excel Service Application service account trusted for delegation:
The Excel Services service account should also be set for constrained delegation to the back-end service. Find the Excel Services service account in Active Directory Users and Computers and open its Properties.
11. Repeat the process using Steps 6-9 above for the Excel Service service account and you will see this result:
12. You now have successfully configured Excel Services to use Kerberos delegation (via the Claims to Windows Token Service), and the Windows Authentication method for the workbook data connection is ready to work.