This is the second part in a small series on my adventures to get Excel Services working with SQL Server 2005 Analysis Services. The first part describes the changes to the Active Directory to setup the Kerberos authentication for delegation of user accounts. In this part we will prepare the SharePoint server.

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

Step 1 – Set the Access Model

The first thing to do is set the Access Model on the server. After installing MOSS in a farm with multiple servers, the access model is default set to ‘Trusted subsystem’. This has to be set to ‘Delegation’. To do this you have to run these STSADM commands:

• stsadm -o set-ecssecurity -ssp SharedServices1 -accessmodel delegation
• stsadm -o execadmsvcjobs

In the first command, ‘SharedServices1’ is the name of my Shared Services provider.

Step 2 – Create a Data Connection Library

A Data Connection Library is a special type of document library that can hold ODC files. These are the data connection files that you use in your Excel sheets to connect to external data sources. Go to your SharePoint site and create a Data Connection Library (DCL) where you want it to be.
     

After creating the DCL, you need to trust it. By doing this, you tell SharePoint the the ODC files in this library are safe and can be used to get data from external data sources. To do this, go to the admin site for your shared services provider, and select ‘Trusted data connection libraries’ in the ‘Excel services settings’ section. Click ‘Add Trusted Data Connection Library’and enter the url to the DCL you just created.

Step 3 – Create a document library

Anywhere in your SharePoint site, create a document library that you will use to publish you Excel sheets to. After creating this library, you have to tell Excel Services that it can trust this location for loading excel sheets. Navigate to the Shared Services admin site and select ‘Trusted file locations’ in the ‘Excel services settings’ section. Click ‘Add Trusted File Location’ and enter the url to the document library you just created.

In the External Data section on the same page, make sure that you set the setting ‘Allow External Data’ to ‘Trusted Data Connection Libraries only’.

    

Step 4 – switch your web application to Kerberos authentication

• Start the Central Administration and navigate to the Application Management tab.
• In the ‘Application Security’ section, click ‘Authentication Providers’. Make sure that the web application of your site is selected in the dropdown on the topright of the page.
• Click on the ‘’Default’’zone.
• Change the IIS Authentication Settings from ‘NTLM’ to ‘Negotiate (Kerberos)’.
      
• Reset IIS
• Log off your client and log back on again

At this stage all server are ready for us to test the solution, which we will do in the next (and last) post.