THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

Kerberos Authentication and Delegation in Analysis Services 2005

Anybody who implemented multi-tier application which needs to access Analysis Services and tried to use Windows integrated security from end to end, has run into the issue of enabling delegation, which also meant forcing Analysis Services to use Kerberos (since this is only supported SSPI provider which implements delegation). One classic example is Intranet Web app, where domain users connect with IE to ASP.NET, and ASP.NET connects to Analysis Services. This was possible to do in Analysis Services 2000 - the detailed steps are documented here: http://www.mosha.com/msolap/articles/enablingdelegation.htm. In Analysis Services 2005, the idea is the same, but few things changed. First, Analysis Services 2005 by default will automatically choose Kerberos if it is available, unlike AS2K which defaulted to NTLM (to be more precise, AS2005 uses by default Negotiate SSPI package, which gives preference to Kerberos. It could be configured through Active Directory group policy to use only Kerberos etc). Second, Analysis Services 2005 supports multiple instances, so SPNs must reflect the instance names. It is important to note, that SPNs don't use port numbers but rather instance names in order to ensure secure mutual authentication of the named instance in case of MITM attack with hijacked redirector. Below are detailed instructions for setting up Kerberos and enabling delegation in AS2005, courtesy of Marius Dumitru who also provided the original instructions for AS2K. The permanent copy of those instructions is kept here: http://www.mosha.com/msolap/articles/kerberos_delegation.htm.

In order for Analysis Services 2005 to use Kerberos as an authentication protocol and enable delegation the following steps are needed:

  1. The correct SPN should be registered in the Active Directory. The "setspn.exe" utility from the Windows 2000 Resource Kit can be used with the following syntax:

    setspn.exe -A MSOLAPSvc.3/<serverhostname>.<domainname> <serviceaccount>
            and/or
    setspn.exe -A MSOLAPSvc.3/<serverhostname> <serviceaccount>

    where <serviceaccount> is either the computer name (for LocalSystem/NetworkService) or the domain account under which the server is running.

  2. The user account(s) you want to be delegated must have the "Account Is Sensitive And Cannot Be Delegated" option cleared (i.e. not checked). You'll find this property in "Active Directory Users And Groups", under the "Account" property tab.

  3. All computers involved must be marked as trusted for delegation (except the first and last computers in the chain).
    For example, if a user on machine A connects to IIS on machine B which uses a COM component on machine C which uses MSOLAP90 to connect to an Analysis Services server on machine D, then the machine accounts of B and C should have the "Computer Is Trusted For Delegation" option checked (enabled) in "Active Directory Users And Computers"->"Computers"->"Computer"->"Properties".

  4. If you have other servers on the chain between the user and the Analysis Services machine, and the other servers run under a service account other than "LocalSystem", then those server accounts should have the "Account Is Trusted For Delegation" option enabled in "Active Directory Users And Groups"->"User"->"Properties"->"Acount"->"Account Options".

  5. All accounts (including machine accounts) must belong to the same Active Directory domain (or to trusted domains in the same forest).

  6. The server (datasource) name has to be either the full DNS name of the server (fully qualified domain name, e.g. myhost.mydomain.com), or a NetBios name (myhost). Specifying a numeric IP address will disable Kerberos.

You can troubleshoot whether a server tries to use Kerberos or not by running "setspn.exe -L <serviceaccount>" and checking whether SPNs with the following format show up in the output:

MSOLAPSvc.3/myhost.mydomain.com
MSOLAPSvc.3/myhost

Also, the Windows Event Log will show success/failure events, but may need additional configuration to do it.

For Yukon Analysis Services named instances, the same steps apply, the only change being that the SPN formats to configure are:

MSOLAPSvc.3/<serverhostname>.<domainname>:<instancename>

MSOLAPSvc.3/<serverhostname>:<instancename>

(i.e. the string ":instancename" gets appended to the regular SPN)

Published Tuesday, January 25, 2005 7:14 PM by mosha
Filed under:
Anonymous comments are disabled
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement