Using Analysis Services data in Excel Services by Kerberos

Reference: http://www.sharepointblogs.com/tonstegeman/archive/2007/03/11/using-analysis-services-data-in-excel-services-part-1-preparing-the-ad-for-kerberos.aspx

 

Using Analysis Services data in Excel Services part 1 - Preparing the AD for Kerberos

 I started to work with Excel Services in combination with data from Analysis Services. First I configured this all in 1 virtual machine, which was easy, because it just works. The next step was to get it working in a real world scenario at one of our customers. Here we have a SharePoint server which also runs Excel Services. Databases and the Analysis Services cubes are on a different machine. The requirement is that we need to impersonate the user to Analysis Services. To be able to do this you have to implement delegation of user account using Kerberos authentication (or SSO). NTLM is not enough, because the web server cannot delegate the current user to the SQL Server. This is also known as the double hop.
In this small series I will descibe all steps that I took to get it working in a virtual test environment. First I will describe the setup of my environment and after that I will describe the changes to the Active Directory.

Part 1 – Preparing the AD for Kerberos
Part 2 – Preparing the MOSS server
Part 3 – Create and test an Excel sheet
Part 4 – Overview and updates

In my test lab I have 3 machines:

Active Directory and SQL Server 2005

• Machine name: office2007
• FQDN: office2007.tst.intra
• Domain: tst
• Roles: Active Directory, SQL Server 2005 SP2, Analysis Services
• SQL services run under a domain account (tst/sqlservice)
• Domain functional level: Windows Server 2003

SharePoint and Excel Services

• Machine name: tstmossdev
• FQDN: tstmossdev.tst.intra
• MOSS installed in a small server farm topology
• Separate domain accounts for all application pools and services
• Web Application on http://intranet (application pool identity: tst/intranetapppool)

Client machine

• Windows XP SP2
• Excel 2007
• Username: tst/ton
• tst/ton is a member of the site running on the SharePoint server
• tst/ton has reader access to the Analysis Services cube.

Below you will find all the steps for configuring the AD, to work with Kerberos authentication.

Step 1 – SPN for SQL Server account

The account that runs the SQL Server services (in my case TST/sqlservice) must have a Service Principal Registration in the AD.
To register this SPN, we use SetSPN.exe. This tool is part of the Windows 2003 Support tools. More information can be found in this kb-article. You have to be a domain admin to run these commands. The commands for my environment are:

• setspn –a MSOLAPSvc.3/office2007:tstdev05 tst/sqlservice
• setspn –a MSOLAPSvc.3/office2007.tst.intra:tstdev05 tst/sqlservice

You have to run both commands. In this command, ‘office2007’ is the servername of the SQL server and ’tstdev05’ is the SQLinstance. For more information on the exact syntax of the commands, please read this blog post by Mosha Pasumansky.

If you want to check what SPNs your account has, you can use setspn -l; in my case setspn -l tst/sqlservice. After running the commands above, this returns:
    

Step 2 – SPN for Application pool account for the SharePoint/Excel server

Just as the SQL Service account, the account that runs the application pool of your web application also needs to have a SPN. The commands that we need to run to create the required SPN for this account:

• setspn –a http/tstmossdev tst/intranetapppool
• setspn –a http/tstmossdev.tst.intra tst/intranetapppool

In this example, ‘tstmossdev’is my SharePoint/Excel services server. After running these commands, the spn’s should look like this:
    

Step 3 – Enable delegation for the application account

The account we created a SPN for in step 2, needs to be enabled for delegation. To do this, find the service account in your AD and go the the properties. Navigatie to the Delegation tab and select “Trust this user for delegation to any service”:
    

Some remarks:

• This screenshot is based on a domain running in Windows Server 2003 mode. If you are using the “Windows 2000 mixed” domain functional level, things look a bit different.
• If there is no Delegation tab, check your SPNs, they are probably not correct.
• I couldn’t get it to work using constrained delegation. I suspect it has something to do with the fact that I am using named instances in my SQL Server setup. Therefore I trust the account to delegate to any service.  

Step 4 – User accounts

Make sure that your users that connect to the cube through Analysis Service have the checkbox ‘Account is sensitive and cannot be delegated’ on the account page cleared:
    

 Step 5 – Enable delegation for the SharePoint server

The server that is running Excel services (in my case SharePoint and Excel Services) must be trusted for delegation as well. Find the server in your Active Directory, navigate to the Delegation tab and select “Trust this user for delegation to any service”:
    
In my sample setup this is the TSTMOSSDEV machine.

In part 2 of this series we will configure Excel Services and SharePoint to use the Kerberos authentication.