Kerberos Delegation

Introduction

Delegation will need to be configured if XLCubedWeb is installed on a different server to the underlying database and the Website is configured to use Windows Authentication.

If you have this type of setup and Delegation isn't configured then you will receive a message such as "Server is unavailable" for Analysis Services or a "(null) user" error for SQL Server when trying to access published reports.

Most corporations have a standard setup for Delegation and this would be handled by the Domain Administrators. The following is a guide to how to configure Delegation in the simplest case but is no means a "Best practice" guide as each domain setup and infrastructure can vary enormously.

There are also many other factors that can impact a successful implementation Delegation, such as network infrastructure, client security settings and software. The following guide will assume that all the clients will be running Internet Explorer and there is only one domain with everything located on the same physical network with no firewalls/routers that could filter Delegation requests.

Preparation

Before beginning it is worth ensuring XLCubedWeb is working correctly including loading a report (at which point it should give the server is not available error)

Ensure the XLCubed Website Application pool is running as either NetworkService or a specifically created domain user and then only "Windows Authentication" and "ASP.net Impersonation" are enabled in the Authentication settings of the website.

Check/clear any existing SPNs for the servers - if any already exist and they are invalid then the configuration will not work. The following link contains some good information about how to do that: http://blogs.msdn.com/b/psssql/archive/2009/02/13/searching-for-duplicate-spn-s-got-a-little-easier.aspx

Information Required

Before configuring Delegation you will need to know the following information:

• What the networks domain name is - this is needed to get a server's Fully Qualifed Domain Name (FQDN): server.domain
• The name and FQDN of the Web Server: Webserver
• The name and FQDN of the Analysis Services: ASServer
• The name and FQDN of the SQL Server (if allowing SQL connections): SQLServer
• The port SQLServer is running under (if allowing SQL connections)
• The User ID for the XLCubed Application Pool configured for the XLCubedWeb website: WebUser
• The User ID the Analysis Services service is running as: ASUser
• The User ID the SQL Server service is running as: SQLUser

User ID format

If the User ID's are domain accounts then the format needed is simply:

 domain\user

If the service or application pool is set to run under NetworkService then use the server name:

 server

Access Required

You will need to be a Domain Administrator to perform all of the configuration steps

Domain configuration

Enable Server Delegation

The first step is to enable delegation on ALL the machines involved in the process:

• WebServer
• ASServer
• SQLServer

This is a domain level setting and is set using the "Active Directory Users and Computers" administrative tool which is part of the Microsoft Admin Tools Pack and would normally be on the Domain Controller.

If there is more then one domain controller then you would need to either initiate a propagate or wait for this change to be applied to all the domain controllers.

Set SPNs

The Service Princial Names (SPNs) are set using a Command Line tool: setspn. This needs to be run in an Administrator Command Prompt with an account that has Domain Admin privileges. It needs to be run for BOTH the server name and the Fully Qualified Domain Name.

If there is more than one domain controller then you would need to wait for this change to propagate to all the servers

Web Server

 setspn –A http/WebServer WebUser
 setspn –A http/WebServerFQDN WebUser

Example:

 setspn -A http/webserver domain\appooluser
 setspn -A http/webserver.domain.com domain\appooluser

Analysis Services

setspn –A MSOLAPSvc.3/ASServer ASUser
setspn –A MSOLAPSvc.3/ASServerFQDN ASUser

SQL Server

setspn –A MSSQLSvc/SQLServer:1433 SQLUser
setspn –A MSSQLSvc/SQLServerDQDN:1433 SQLUser

Where 1433 would be replaced with the appropriate SQL Server port number

User account delegation

Now the SPNs have been set you would need to enable delegation for the service account and application pool users, this is again done in "Active Directory Users and Computers" administrative tool.

IIS Configuration

After making the following changes and running the Delegation configuration run an IISReset before testing and restart Internet Explorer on the client machine.

Check the providers and order

In IIS Manager, right click on "Windows Authentication" under the Authentication section and select providers, ensure they match the following:

Advanced settings

You may need to disable "Enable Kernel-mode authentication" - this is accessible by right clicking on "Windows Authentication" under the Authentication and selecting "Advanced Settings". If Delegation doesn't work initially try toggling this setting (with an IISReset)

XLCubed Configuration

There are no steps required within XLCubed - the whole Delegation process is handled between the browser and the various servers before XLCubedWeb starts to process user requests

Troubleshooting

Microsoft Kerberos testing tool

Microsoft have a tool available to test that Delegation has been correctly configured, this can be downloaded from here: http://www.microsoft.com/en-us/download/details.aspx?id=39046

Alternate testing tool

There is also a Kerberos website that you can install - it would need to be setup to duplicate the exact configuration and can then be used to check the configuration. It will also provide options to fix any problems it finds. It can be downloaded from the following link:

http://www.xlcubed.com/downloads/kerberos.zip

Non-IE browsers

It is possible for some non-Internet Explorer browsers to use Kerberos, here are some tips that may help, but if you can't get it working then Internet Explorer would need to be the fall back or consider setting up Dual Authentication for access to remote clients.

Chrome

You would need to add all the servers involved in the process to the "White List", this is done via the shortcut for the browser:

 "C:\Program Files\Google\Chrome\Application\chrome.exe" --auth-server-whitelist="ASServer,WebServer,SQLServer" --auth-negotiate-delegate-whitelist="ASServer,WebServer,SQLServer"

More information

The following pages contain some very useful information or tools regarding Delegation:

• http://support.microsoft.com/kb/929650
• http://blogs.msdn.com/b/psssql/archive/2009/02/13/searching-for-duplicate-spn-s-got-a-little-easier.aspx
• http://www.microsoft.com/en-us/download/details.aspx?id=39046
• http://support.microsoft.com/kb/929650
• http://blogs.technet.com/b/surama/archive/2009/04/06/kerberos-authentication-problem-with-active-directory.aspx
• http://social.technet.microsoft.com/wiki/contents/articles/717.service-principal-names-spns-setspn-syntax-setspn-exe.aspx