Hi, I'm Martin Kearn ...
I am a Technical Evangelist for Microsoft, UK. I blog about everything relating to developing apps and websites on the Microsoft stack. This includes ASP.net, Azure, Windows Apps and Office Apps. I used to do a load of stuff with SharePoint so many of my older post are SharePoint related.
Follow me on Twitter @MartinKearn
This is the second of my several-part series on how to configure Kerberos for MOSS 2007. In the first article, I outlined the steps that are required in order to get Kerberos working for a basic MOSS installation. In this article I am going to address one of the most common scenarios that actually requires Kerberos in order to work; that is using Excel Services to display data from a SQL Analysis Services Cube (or a normal SQL database) via SharePoint.
Why does this scenario require Kerberos?
As outlined in part 1 of this series, Kerberos is required whenever something in SharePoint needs to access another service on the user's behalf (i.e. "double hop"), this scenario does exactly that. The spreadsheets that are hosted by Excel Services will need to access a SQL server in order to get the information that is required. This is a double-hop and unless Kerberos is enabled, you will get errors in your Excel Services Webparts.
Step 1: Configure Kerberos for MOSS
The first step in configuring this scenario is to get your base MOSS configuration sorted. Follow the steps in my first article to do this (Configuring Kerberos for SharePoint 2007: Part 1, Base Configuration for SharePoint).
Step 2: Configure Permissions in SQL AS
This section specifically relates to using SQL Analysis Services, but similar steps will be required for normal SQL or Reporting Services.
In order that users can access your SQL AS cube, you need to configure permissions inside SQL Management Studio. Follow these steps:
This will means that user have access to actually read the data from eth AS cube
Step 3: Configure Excel Services for Delegation
One of the key things that people get caught out with on when attempting this scenario is configuring Excel Services to use Delegation (i.e. to use Kerberos rather than NTLM). This is a setting that you can only set by using STSADM.exe; you cannot set it through the SharePoint Administration pages and it is not well documented. The discussion thread outlines this step: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1224539&SiteID=17
Follow these steps:
Step 4: Create your Data Connection file
Now Excel Services has been configured, you need to make sure that the data connection has the right settings for Kerberos. Typically in this scenario, a data connection file will be created and stored in a SharePoint data connections library. This ensures that you only have to set the data connection up once and use it many times.
There are several key settings that must be in the data connection file in order for Kerberos to work, these include using the FQDN of the SQL server and adding SSPI=Kerberos to the connection string. Follow these steps:
Step 5: Configure your site
Now you have created the data connection, you can go ahead and configure your site.
Generally one of the first things to do is to add an 'SQL Server 2005 Analysis Services Filter' webpart which uses the data connection to provide filters to other webparts on your site. This is one of the first places to test Kerberos. When you add the SQL AS Webpart, you will first need to choose the data connection. Upon doing this the Dimension drop-down should populate with dimensions from SQL. If this works then Kerberos is working! J
These articles were useful for me when trying to configure this:
Displaying Workbook using external data with periodic refresh in EWA ... this is a good discussion thread on TechNet that talks about some of the Excel Services settings that are required.
How to configure SQL Server 2005 Analysis Services to use Kerberos authentication ... a great MSDN article that covers some of the AS and connection string parameters.
How to use Kerberos authentication in SQL Server ... a very comprehensive article that covers configuring Kerberos for SQL