Publishing Excel Services over the Internet using Kerberos Constrained Delegation

So here is a very common requirement – You have Excel Services connecting to a Cube and its working great in your network. Now you want to publish it to over the internet so that your Customers can access it. You want to ensure that your Cube Security takes precedence and don’t want to use Secure Socket Service to impersonate the “God Account”. I was recently working on this requirement with one of my partner and didn’t find much help. At the end I decided to set up a DC – WFE – APP – SQL server on the internet and test out the theory.  I hit a couple of road blocks initially with IIS falling back to NTLM no matter what I do when hitting from the internet and quickly found out that its by design. However, that doesn’t stop SharePoint from being your platform for BI.

No matter what people posts on the internet, let me tell you that you don’t need any custom code or special configuration to make this work. It works like a charm if you follow the directions – step-by-step.

NOTE: Some SPN’s here are redundant, result of endless hours of testing and I didn’t get a chance to remove them and test out the deployment. When and If I get a chance, will update them later.

So here are the list of service accounts that I am using:

Service Account Name	Purpose
SQLSvc	Running SQL DB Engine and Agent
SQLASSvc	Running Analysis Services
SPAdmin	SharePoint Setup Admin Account. Local Admin on WFE and APP.
SPFarm	Running Central Admin App Pool. Not a local admin
SPSiteAppPool	Application Pool for SharePoint Web App – Extranet.fabricam.local
SPServicesAppPool	Running all services Applications apart from Excel Services and Claims
SPExcel	Running Excel Services
SPClaims	Running Claims to Windows Token Services. Note: by default this runs under Local System Account. Additional steps are required for this account and this is the most important Account. More details below.

 

Below are the Servers and the services that are running on them

Server Name	What's running on them
DC01	Domain Controller
WFE01	SharePoint running the following services: -- Microsoft SharePoint Foundation Incoming E-Mail -- Microsoft SharePoint Foundation Web Application -- Microsoft SharePoint Foundation Workflow Timer Service Note: in this testing Search or any other Service App was not provisioned.
APP01	SharePoint running the following services: -- Microsoft SharePoint Foundation Incoming E-Mail -- Microsoft SharePoint Foundation Web Application (You can stop this if you want and make it a true “App Server”) -- Microsoft SharePoint Foundation Workflow Timer Service -- Central Administration -- Claims to Windows Token Service -- Excel Calculation Services
SQL01	Running SQL Database Engine and Analysis Services

 

I followed this document for to configure this environment and its pretty comprehensive.

First step is to configure the SQL Account and the Analysis Services Account to run under Kerberos. To do so, log on to DC, fire up command prompt under elevated mode and run the following commands: (Note: I registered for both FQDN and NetBIOS)

SetSPN -S MSSQLSVC/SQL01.fabricam.local:1433 fabricam\SQLSvc

SetSPN -S MSSQLSVC/SQL01:1433 fabricam\SQLSvc

SetSPN -S MSOLAPSvc.3/SQL01.fabricam.local fabricam\SQLASSvc

SetSPN -S MSOLAPSvc.3/SQL01 fabricam\SQLASSvc

I had provisioned Central Admin site to run under Kerberos as well and hence had to register the SPN for that: (Optional)

SetSPN –S HTTP/APP01.fabricam.com:2010 fabricam\spFarm

SetSPN –S HTTP/APP01:2010 fabricam\spFarm

After this you can open up SQL management studio and run this query to see if your SharePoint servers are communicating with SQL using Kerberos or not:

 Select 
     s.session_id,
     s.login_name,
     s.host_name,
     c.auth_scheme
 from
 sys.dm_exec_connections c
 inner join
 sys.dm_exec_sessions s
 on c.session_id = s.session_id

The result is something similar to:

Now its time to register SPN’s for the main Extranet Site. my Extranet site URL is https://extranet.fabricam.local and in my DNS it’s a CNAME mapped to the WFE Server. I registered the following SPNs

SetSPN –S HTTP/Extranet.Fabricam.Local fabricam\SPSiteAppPool

SetSPN –S HTTP/WFE01.Fabricam.Local fabricam\SPSiteAppPool

SetSPN –S HTTP/APP01.Fabricam.Local fabricam\SPSiteAppPool

Set Delegation for the SiteAppPool Account

For the SiteAppPool >> Open up the User Account in AD Users and Computers >> Delegation Tab and set Delegation for

• SiteAppPool – Self Delegation is required. This will list the following FQDN (Service Type – HTTP)

  • Extranet.fabricam.local
  • WFE01.fabricam.local
  • APP01.fabricam.local

• SQLSvc – This will set delegation for (Service Type – MSSQLSVC):

  • SQL01:1433
  • SQL01.fabricam.local:1433

• SQLASSvc - This will set delegation for (Service Type – MSOLAPSVC.3):

  • • SQL01
    • SQL01.fabricam.local

Next step is setting up the Excel Services. Follow the exact steps from the Kerberos Config Guide:

On the command line, run the following command (On DC):

SETSPN -S SP/ExcelServices fabricam\SPExcel

Note: the SPN is not a valid SPN. It is applied to the specified service account to reveal the delegation options in the AD users and computers add-in. There are other supported ways of specifying the delegation settings (specifically the msDS-AllowedToDelegateTo AD attribute) but this topic will not be covered in this document.

Now in User Account – Delegation Tab click on Trust this user for Delegation >> Use Any Authentication Protocol and Select SQLSvc and SQLASSvc and select all the SPN’s.

Register the same delegation for the App Server Computer Account. (Refer to the whitepaper for screenshots under “Configure Kerberos constrained delegation for Excel Services”)

Last piece of the puzzle is to configure the Claims to Token Services.

We already have a Service Account for this called fabricam\SPClaims, use this command to register a fake SPN:

SetSPN -S SP/C2WTS Fabricam\SPClaims

Now like the steps above – Delegate credentials >> User Account – Delegation Tab click on Trust this user for Delegation >> Use Any Authentication Protocol and Select SQLSvc and SQLASSvc and select all the SPN’s.

Make sure the following criteria are met:

• SPClaims Account is a local Admin on the App Server
• SPClaims Account has been added to the following under secpol.msc

  • i. Act as part of the operating system

    ii. Impersonate a client after authentication

    iii. Log on as a service

Log back in Central Admin and change the Claims Service to run under this Service Account and not local system.

Add Startup dependencies the WIF C2WTS service

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:

1. Open the command window.

2. Type: sc config “c2wts” depend= CryptSvc

3. Find the Claims to Windows Token Service.

4. Open the properties for the service.

5. Check the Dependencies tab. Make sure “Cryptographic Services” is listed:

6. Click “OK”.

Grant the Excel Services service account permissions on the web application content database

A required step in configuring SharePoint Server 2010 Office Web Applications is allowing the web application’s service account access to the content databases for a given web application. In this example, we will grant the Excel Services service account access to the “portal” web application’s content database by using Windows PowerShell.

Run the following command from the SharePoint 2010 Management Shell:

$w = Get-SPWebApplication -Identity https://extranet.fabricam.local

$w.GrantAccessToProcessIdentity("fabricam\SPExcel")

You are all set.

See something interesting below…

when I hit extranet.fabricam.local from the internet it hits the WFE and the authentication is NTLM (this is by design):

For the same attempt, see the security log from the App Server:

Claims to Windows Token Service is doing its job!!

Cheers!

Priyo