Kerberos Authentication & Excel Services

After battling with setting this up for 3 days before it finally started to authenticate, I thought I’d add the lessons learned to the configuration processes listed here including the troubleshooting tools used.

Firstly, Credits and Thanks – Without Martin Kearn’s blog entry, I would have had no hope translating these 2 Technet articles (1, 2) into the steps in Martin’s guide. If I’d needed to configure delegation to the SQL server, I would have also taken advantage of this article from Mosha Pasumansky. James World’s blog article validated what I was seeing in NetMon with Site access authentication requests being passed through without a port number (I’m glad it wasn’t just me). Finally, the tips in this article gave me significant insight into how Kerberos worked and how it was supposed to work in a multi-tiered web environment, as well as how to troubleshoot it.

The major challenge that I had following the steps in Martin’s blog is that he does not give an explanation on how to determine what SPN’s you need – this makes determining the correct SPN’s for configurations different from Martin’s configuration difficult – it’s my aim to alleviate some of that confusion for others also venturing down this path, especially if your setup is a high-availability one.

Some of the following will be similar to Martin’s blog, however I’m also going to build in:

  • How to determine the SPN’s required
  • What SPN’s are necessary, what are not
  • Troubleshooting Kerberos issues that may be seen while setting up Excel Services (and which tools to use) – Coming soon…
  • What can still be achieved without using Kerberos in Excel Services (Yes, you don’t NEED Kerberos or SSO to run most ECS functionality… but there are some bits that will refuse to work) – Coming soon…

So where does SharePoint need to double-hop credentials anyway? How can you test to see if your issue is caused by a double-hop failure because you are using NTLM authentication? Is a Double-hop something a Kangaroo does? Find out here… 🙂

Kerberos was created to be more secure and faster than NTLM – and to fill the double-hop void… but by default, it does not allow accounts to impersonate other accounts unless you explicitly allow them to do so. That’s probably why you’re here – because in order to get the full functionality of Excel Services working with Kerberos authentication, you need to allow the service accounts of the SharePoint application to impersonate the user’s account that is currently logged into SharePoint. If you do not need to authenticate users and you don’t mind if people who are connected to your network can see your back-end data source then there are instructions coming soon on how to set up ECS without using Kerberos.

In the diagram below, I outline the process that occurs to retrieve data from the SSAS cube via ECS using Kerberos & Delegation.

 

You can see that it’s a fairly involved process, and each arrow encapsulates a further series of sub-tasks that occur to achieve the desired outcome (The Visio diagram used to create this image is here). In some cases you may also require delegation (represented by the arrows with the yellow highlights) at the SSAS server, if you are querying live data from the database and you want the figures to be security trimmed.

Based on the diagram above, in the table below I highlight which SPN’s are required for Excel to work and which ones can be left alone. I recommend migrating all of the authentication types over at once – mainly because it’s easier to manage… however it’s not necessary as indicated…

 

Application Pool
Account Description

Used to

Required to be Kerberos-enabled for ECS to work

MOSS Server Farm account

Run the MOSS Central Admin application pool, SharePoint services and connect to the database to make application changes, create new sites, etc.

No

My Sites Application Pool account

Used to run the application pool for the My Sites area of MOSS. Not required unless your users want to use the RSS reader supplied with MOSS to read RSS feeds from the SharePoint application (Or you could use the Smiling Goat RSS Reader which does NTLM Authentication).

No

The Web site rendering the Excel pages

Used to run the web site that the user sees and interacts with. Must support Kerberos if you want to put an RSS feed reader on the site that points to another area of the site or another MOSS site. Also required if you want to display figures from Analysis services that are security-trimmed, use Connection files or Analysis Services filters.

Yes

Search Service Account

Used to manage the jobs that run on WFE servers, crawling content

No

Default Content Access account

Used to crawl content on web sites, file shares, etc

No

Content Access account

Used to crawl content that the account is specifically configured to access

No

Shared Service Provider (SSP) service account

Used to run the Shared service provider Services, same account as the SSP Application pool account and therefore has the same privileges

Yes

SSP Application pool account

Used to manage SSP’s, Application servers

Yes

ECS Web Service App Pool Account

Used by the Web Site to generate the HTML that renders the Excel Spreadsheet (Normally that same account as the SSP App pool)

Yes

Other accounts that are needed during an installation of SharePoint

Account Description

Used to

Required to be Kerberos-enabled for ECS to work

SSAS SQL Server Service Account

Run SQL Server 2005 SSAS

No

Let’s get underway. First we’ll run through the work that needs to be done by your local friendly Domain Administrator 🙂


Create the SPN’s:

Start by filling out the following table – this makes it easier to work out the required SPN’s

ID

Account

More Info

Fill out the information here

PWSNBN

Primary Web Server’s Net Bios name

Get this from the "My Computer" properties (Computer Name tab and look beside the "Full Computer Name" heading – will look like servername.companyname.internal – write down the servername part, not the whole lot)

PWS Machine NetBIOS name:

PWSDNSN

Primary Web Server’s DNS Name

The name set up in DNS that allows access to the site using a name and normally the default port 80 (this site would normally have a host header mapping in IIS and a matching Alternate Access Mapping in MOSS if set up correctly) eg http://ExcelSite

PWS DNS Name:

IDN

Internal Domain Name

Get this from the "My Computer" properties (Computer Name tab and look beside the Domain heading)

eg companyname.internal

PWSID

Primary Web Site
application Pool Identity

Open up IIS Admin console, expand primary web site, view properties and look on the "Home" tab. These’s a Web Application field listed and it’s the identity of this app pool we are after

domain\username

ECSNBN

The ECS Machine’s NetBIOS Name

Get this from the "My Computer" properties of the server running ECS, if it is different from the server running the SSP (Go to the Computer Name tab and look beside the "Full Computer Name" heading – will look like servername.companyname.internal – write down the servername part, not the whole lot)

ECS Machine NetBIOS Name:

SSPNBN

SSP Machine’s NetBIOS Name

Get this from the "My Computer" properties (Computer Name tab and look beside the "Full Computer Name" heading – will look like servername.companyname.internal – write down the servername part, not the whole lot)

SSP Machine’s NetBIOS Name:

SSPID

SSP application Pool Identity

The application pool identity for the Shared Services Management site

domain\username

SSPNAME

The SSP Name

The name you have assigned the SSP attached to your web application

SharedServices1

MYSITEDNS

The My Site DNS name (If defined)

This is the DNS name you have assigned to My Sites (eg mysite.intranet) – only required if you are planning on running My Sites under Kerberos

mysite.intranet

Now we need to create the SPN’s attached to the Accounts used to run the site’s app pool. You need an SPN for the machine’s NetBIOS name, the FQDN and any DNS names you have set up. So, using the table above you need to run the following lines (without the square braces[], replacing the ID’s from the table you have just created):

setspn -a HTTP/[PWSNBN] [PWSID]
setspn -a HTTP/[PWSNBN].[IDN] [PWSID]
*setspn -a HTTP/[PWSDNSN] [PWSID]
*setspn -a HTTP/[PWSDNSN].[IDN] [PWSID]
setspn -a HTTP/[ECSNBN] [SSPID]
setspn -a HTTP/[ECSNBN].[IDN] [SSPID]
setspn -a HTTP/[SSPNBN] [SSPID]
setspn -a HTTP/[SSPNBN].[IDN] [SSPID]
^setspn -a HTTP/[MYSITEDNS] [SSPID]
^setspn -a HTTP/[MYSITEDNS].[IDN] [SSPID]

Update 13/9/2008:
Thanks go to deannie for pointing out the missing HTTP/ in the setspn commands

Update 23/2/2009:
A good friend of mine, James Milne from Myriad Solutions used the information in this article to create an InfoPath form that generates all of the settings for you – which means all you need to do is copy and paste it into a Command prompt. You can download the package and user guide here. You do need InfoPath 2007 installed to view the form, though.

* – Note there may be more than one pair of these to do if you have multiple DNS / Host names set up for the main site.
^ – You only need to set these up if you plan for users to access MySites under a different DNS name using Kerberos

Example:

if you ran a site called  http://mymossWFE:1234 with a host header mapping to http://myintranet and the web service ran under the Application pool account SVC_MOSS_WEB, you would need the following SPN’s created.
http://myintranet domainName\SVC_MOSS_WEB
http://myintranet.companyname.internal domainName\SVC_MOSS_WEB
*http://mymossWFE domainName\SVC_MOSS_WEB
*http://mymossWFE domainName\SVC_MOSS_WEB

The Shared Services account being used by the Excel site – building on the previous example, if the SSP used by the myintranet site was running on the site http://ecsserver:10070 under the user SRVC_MOSS_SSP, you would also need the following SPN’s:
http://ecsserver domain\SRVC_MOSS_SSP
http://ecsserver.companyname.internal domain\SRVC_MOSS_SSP

* Note – If the ECS Server and the WFE server are the same, do not create these SPN’s (as you will be mapping the same SPN’s to 2 different user ID’s)

For all of the user accounts you have just created SPN’s for, set them up in Active Directory so they can use delegation – ‘Trust this user for delegation to any service (Kerberos)* – and ensure that the account is not marked ‘Sensitive (Cannot be delegated)’

*Note – Microsoft recommend using constrained delegation – where you nominate the servers you are delegating user credentials to (on the same tab in AD Users & Computers). I think that’s a great idea as part of a process to lock down the production environment once you have everything up and running – but make it work first, that way when you make changes locking it down, you know what breaks the system as soon as it breaks.


This ends the work that the AD Administrator needs to complete

Next, Enable Kerberos on your web applications (this section is direct from Martin’s Blog):


  • Open Central Administration
  • Navigation to Application Management > Authentication Providers
  • For each web application that you need to change, based on the SPN’s you have just created:
    • Choose the web applications you wish to configure for Kerberos from the drop-down in the top right corner
    • Click on ‘Default’
    • Set the authentication to Negotiate (Kerberos)
    • Click OK
  • IISRESET when complete
    • Enable Kerberos on your SSP (The machine hosting your Admin Site):

      • Open a Command Prompt and navigate to your ’12\Bin’ directory (normally c:\program files\common files\microsoft shared\web server extensions\12\bin) 
      • Run ‘STSADM.exe -o SetSharedWebServiceAuthn -negotiate’

      Configure Component Services on all web servers:

      • Open Component Services on the MOSS server
      • Navigation to Component Services > Computers > My Computer
      • Click on Properties (for My Computer) > Default Properties > Default Impersonation Level = Delegate (see http://support.microsoft.com/kb/917409)
      • Navigate to Component Services > Computers > My Computer > DCOM Config > IIS WAMREG Admin Service
      • Click on Properties (for IIS WAMREG Admin Service) and navigate to the Security tab
      • Edit Launch and Activate Permissions
      • Grant all of your application pool accounts ‘Local Activation’ permissions (see http://support.microsoft.com/kb/920783). In our example, these accounts would be domain\MySiteAppPool, domain\SSPAdminAppPool, domain\PortalAppPool

      Configure Excel Calculation Services for Delegation:

      • On your SharePoint server running ECS, Run the following command where [SSPNAME] is the name of your Shared Service Provider:
      • stsadm.exe -o set-ecssecurity -ssp [SSPNAME] -accessmodel delegation
      • Now run the following command:
      • stsadm.exe -o execadmsvcjobs
      • IISRESET


      Excel Services is now ready to run & publish spreadsheets… but for those settings, I’ll come back another night.

      Still to come:

      • Configuring ECS & Moss to display Spreadsheets
      • Kerberos Troubleshooting – What to do when it turns to poo
      • How to run multi-tier ECS without Kerberos or SSO (and what you miss out on)

      Hopefully this article has been of use to you. Thanks for dropping by!

      Advertisements

      About Brad Saide

      I'm a SharePoint consultant. I'm also slowly going bald, seem to have a permanent spare tyre around my waist and enjoy socialising with friends over a beer or 10. The last 2 may possibly be related. Started working with SharePoint when the first version was in limited beta release (participated in the Technology Adoption Program while at Woolworths) and have been committed to the adoption of the technology as a business enabler ever since.
      This entry was posted in Kerberos. Bookmark the permalink.

      One Response to Kerberos Authentication & Excel Services

      1. Mike says:

        I\’ve found several references to a new Service Class (MSSP) that should be used when setting up SPNs for the Shared Service Provider. (http://blogs.technet.com/tothesharepoint/archive/2008/08/21/3107508.aspx)

      Leave a Reply

      Fill in your details below or click an icon to log in:

      WordPress.com Logo

      You are commenting using your WordPress.com account. Log Out / Change )

      Twitter picture

      You are commenting using your Twitter account. Log Out / Change )

      Facebook photo

      You are commenting using your Facebook account. Log Out / Change )

      Google+ photo

      You are commenting using your Google+ account. Log Out / Change )

      Connecting to %s