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 listedhere including the troubleshooting tools used.
Firstly, Credits and Thanks – WithoutMartin 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 ofthis 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).
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 requiredWhat SPN’s are necessary, what are notTroubleshooting 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 ishere). 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…
Account DescriptionUsed toRequired to beKerberos-enabledfor ECS to workMOSS Server Farm accountRun the MOSS Central Admin application pool, SharePoint services and connect to the database to make application changes, create new sites, etc.NoMy Sites Application Pool accountUsed 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).NoThe Web site rendering the Excel pagesUsed 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.YesSearch Service AccountUsed to manage the jobs that run on WFE servers, crawling contentNoDefault Content Access accountUsed to crawl content on web sites, file shares, etcNoContent Access accountUsed to crawl content that the account is specifically configured to accessNoShared Service Provider (SSP) service accountUsed to run the Shared service provider Services, same account as the SSP Application pool account and therefore has the same privilegesYesSSP Application pool accountUsed to manage SSP’s, Application serversYesECS Web Service App Pool AccountUsed 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 DescriptionUsed toRequired to beKerberos-enabledfor ECS to workSSAS SQL Server Service AccountRun SQL Server 2005 SSASNo
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’sIDAccountMore InfoFill out the information herePWSNBNPrimary Web Server’s Net Bios nameGet 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:PWSDNSNPrimary Web Server’s DNS NameThe 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) eghttp://ExcelSitePWS DNS Name:IDNInternal Domain NameGet this from the “”My Computer”” properties (Computer Name tab and look beside the Domain heading)eg companyname.internalPWSIDPrimary Web Site
application Pool IdentityOpen 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 afterdomain\usernameECSNBNThe ECS Machine’s NetBIOS NameGet 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:SSPNBNSSP Machine’s NetBIOS NameGet 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:SSPIDSSPapplication Pool IdentityThe application pool identity for the Shared Services Management sitedomain\username
SSPNAMEThe SSP NameThe name you have assigned the SSP attached to your web applicationSharedServices1MYSITEDNSThe 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 Kerberosmysite.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 [PWSNBN] [PWSID]
setspn -a [PWSNBN].[IDN] [PWSID]
*setspn -a [PWSDNSN] [PWSID]
*setspn -a [PWSDNSN].[IDN] [PWSID]
setspn -a [ECSNBN] [SSPID]
setspn -a [ECSNBN].[IDN] [SSPID]
setspn -a [SSPNBN] [SSPID]
setspn -a [SSPNBN].[IDN] [SSPID]
^setspn -a [MYSITEDNS] [SSPID]
^setspn -a [MYSITEDNS].[IDN] [SSPID]
* – 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 tohttp://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 sitehttp://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 AdministrationNavigation to Application Management > Authentication ProvidersFor 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 cornerClick on ‘Default’Set the authentication to Negotiate (Kerberos)Click OKIISRESET 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 serverNavigation to Component Services > Computers > My ComputerClick on Properties (for My Computer) > Default Properties > Default Impersonation Level = Delegate (seehttp://support.microsoft.com/kb/917409)Navigate to Component Services > Computers > My Computer > DCOM Config > IIS WAMREG Admin ServiceClick on Properties (for IIS WAMREG Admin Service) and navigate to the Security tabEdit Launch and Activate PermissionsGrant all of your application pool accounts ‘Local Activation’ permissions (seehttp://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 delegationNow run the following command:stsadm.exe -o execadmsvcjobsIISRESET
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 SpreadsheetsKerberos Troubleshooting – What to do when it turns to pooHow 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!