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…
|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]
Thanks go to deannie for pointing out the missing HTTP/ in the setspn commands
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
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.
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:
* 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
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!