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:

image

Now its time to register SPN’s for the main Extranet Site. my Extranet site URL is http://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

image

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:

image

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 http://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):

image

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

image

Claims to Windows Token Service is doing its job!!

Cheers!

Priyo