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
|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
What’s running on them
SharePoint running the following services:
Note: in this testing Search or any other Service App was not provisioned.
SharePoint running the following services:
— Microsoft SharePoint Foundation Incoming E-Mail
|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:
The result is something similar to:
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)
- SQLSvc – This will set delegation for (Service Type – MSSQLSVC):
- SQLASSvc – This will set delegation for (Service Type – MSOLAPSVC.3):
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 http://extranet.fabricam.local
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!!