How to configure SQL Server 2008 Analysis Services and SQL Server 2005 Analysis Services to use Kerberos authentication

Summary

When a connection is made to a computer that is running Microsoft SQL Server 2008 Analysis Services or Microsoft SQL Server 2005 Analysis Services and that connection involves a double-hop authentication scenario, you must use Kerberos as the authentication protocol. For example, in a double-hop authentication scenario, a client computer may pass the logon credentials to a computer that is running Microsoft Internet Information Services (IIS). The computer that is running IIS must then pass the logon credentials to the Analysis Services server. The steps that you must follow differ from the steps for SQL Server 2000 Analysis Services.

INTRODUCTION

This article describes how to configure SQL Server 2008 Analysis Services and SQL Server 2005 Analysis Services to use Kerberos authentication.

More Information

Configure an Analysis Services server to use the Kerberos authentication protocol

Register a Service Principal Name (SPN) for the Analysis Services service on the Analysis Services server. If the Analysis Services service is running under the security context of the LocalSystem account in SQL Server 2000, the SPN is created automatically. However, you must manually create the SPN in SQL Server 2008 and in SQL Server 2005 like you create the SPN in SQL Server 2000 when the Analysis Services service is running under the security context of an account other than the LocalSystem account. To create the SPN, use the Setspn.exe utility in the Microsoft Windows 2000 Resource Kit. This tool is also included in the Windows Server 2003 Support Tools. The Windows Server 2003 Support Tools are included in Windows Server 2003 Service Pack 1 (SP1).

To download the Setspn utility in the Windows 2000 Resource Kit, visit the following Microsoft Web site:
http://www.microsoft.com/downloads/details.aspx?FamilyID=5fd831fd-ab77-46a3-9cfe-ff01d29e5c46&DisplayLang=en
For more information about how to download the Setspn.exe tool for Windows Server 2003, click the following article number to view the article in the Microsoft Knowledge Base:

970536 Setspn.exe support tool update for Windows Server 2003

After you download the Setspn utility, follow these steps.

Note You must be a member of the Domain Administrators group to run the Setspn command. If the instance of Analysis Services is clustered, use the Analysis Services virtual name as the fully qualified domain name (FQDN).
  1. To create the SPN for the Analysis Services server that is running under a domain account, run the following commands at a command prompt:
    • Setspn.exe -S MSOLAPSvc.3/Fully_Qualified_domainName OLAP_Service_Startup_Account

      Note Fully_Qualified_domainName is a placeholder for the FQDN.
    • Setspn.exe -S MSOLAPSvc.3/serverHostName OLAP_Service_Startup_Account
  2. If you must create the SPN for the Analysis Services server that is running under the LocalSystem account, run the following commands at a command prompt:
    • Setspn.exe -S MSOLAPSvc.3/Fully_Qualified_domainName serverHostName
    • Setspn.exe -S MSOLAPSvc.3/serverHostName serverHostName
  3. To verify whether the SPN was created for the Analysis Services server, run the following commands at a command prompt.
    Setspn.exe -L OLAP_Service_Startup_Account 
    Setspn.exe -L serverHostName
    If the SPN was successfully created for the Analysis Services server, the results of this command typically appear in the following format.
    MSOLAPSvc.3/serverHostName.Fully_Qualified_domainName 
    MSOLAPSvc.3/serverHostName
Note SQL Server 2005 Analysis Services can operate as a named instance. This is not supported in SQL Server 2000 Analysis Services. If you are using a named instance, the same steps apply. However, you must configure the following SPN formats. Unlike with the SQL Server engine, you cannot specify a port after the colon. You must use the actual instance name for all functionality to work correctly.
MSOLAPSvc.3/serverHostName.Fully_Qualified_domainName:instanceNameMSOLAPSvc.3/serverHostName:instanceName

Configure Active Directory settings

Make sure that all the following conditions are true for the Active Directory directory service settings:
  • The Account is sensitive and cannot be delegated setting is not enabled for user accounts that will be delegated.
  • The Account is trusted for delegation setting is enabled for the domain account of the middle tier that is connecting to Analysis Services. For example, if IIS is the middle tier and a domain account is used for the application pool, that application pool domain account must have the Account is trusted for delegation setting enabled.
  • The Account is trusted for delegationsetting is enabled for the accounts of all services and COM+ components that are involved in the process.
  • The Trust computer for delegation setting is enabled for all the computers that are involved in the process.
Note All accounts and servers that are involved in the process must belong to the same Active Directory domain or to trusted domains in the same forest. If you have native Windows 2003 forests and you want more information about how to enable cross-forest delegation, see the "Forest Trusts" section of the following Microsoft Web site:
http://technet2.microsoft.com/WindowsServer/en/library/f5c70774-25cd-4481-8b7a-3d65c86e69b11033.mspx?mfr=true

Configure Analysis Services client computers

Make sure that the following conditions are true on the Analysis Services client computers:
  • Microsoft Internet Explorer 5.0 or a later version is installed.
  • If Internet Explorer 6 is installed on the computer, the
    Enable Integrated Windows Authentication (requires restart)security option is enabled.
  • If the Analysis Services is a named instance, you must create the MSOLAPDisco.3 SPN for SQL Browser. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

    950599 An SPN for the SQL Server Browser service is required when you establish a connection to a named instance of SQL Server 2005 Analysis Services or of SQL Server 2005

Note The Enable Integrated Windows Authentication (requires restart) option is located under Security on the
Advanced tab in the Internet Options dialog box. You may have to restart the computer for this setting to take effect.

Configure the settings on the computer that is running IIS

Make sure that the following conditions are true on the computer that is running IIS in a double-hop authentication scenario:
  • The following settings are configured in IIS for the Web site or for the virtual directory that was created for the client Web application:
    • The authentication method for the directory security is set to Integrated Windows Authentication or to Basic Authentication.
    • The application protection level is set to High (Isolated).
  • The following Component Services settings are configured for the Web site or for the virtual directory that was created for the client Web application:
  • The connection string that is used by the Analysis Services client computer to connect to the Analysis Services server contains the SSPI= Kerberos parameter.
  • In the connection string, the data source name has to be either the fully qualified domain name (FQDN) or a NETBIOS name. For example, the FQDN may be
    myhost.mydomain.com, and the NETBIOS name may be myHostName. If you specify a numeric IP address, Kerberos authentication is disabled.
  • An SPN for the computer that is running IIS may have to be created and registered. The SPN to be created depends on the computer name or host name, and the IIS application pool identity. For more information about how to create an SPN for the computer that is running IIS, click the following article number to view the article in the Microsoft Knowledge Base:

    929650 How to use SPNs when you configure Web applications that are hosted on IIS 6.0

    setspn -S http/IISComputerName IISComputerName
    To manually register an SPN for the computer that is running IIS, follow the steps in the "Configure Analysis Services to use the Kerberos authentication protocol" section.
  • A Negotiate authentication provider must be enabled on the IIS server to allow for Kerberos authentication. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

    215383 How to configure IIS to support both the Kerberos protocol and the NTLM protocol for network authentication

  • The clock for the Kerberos client and for the domain controller must be synchronized as closely as possible. For more information about the maximum time difference, visit the following Microsoft Web site:

    http://technet.microsoft.com/en-us/library/cc779260.aspx
If you have verified all these steps and you still cannot use Kerberos, follow the steps in the following Knowledge Base article to gather more troubleshooting information from the system event log:
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
262177 How to enable Kerberos event logging

References

For more information about how to configure a SQL Server 2000 Analysis server computer to use Kerberos authentication, click the following article number to view the article in the Microsoft Knowledge Base:

828280 How to configure an instance of SQL Server 2000 Analysis Services to use Kerberos authentication

For more information about the TechNet Support WebCast for this subject, click the following article number to view the article in the Microsoft Knowledge Base:

916962 TechNet Support WebCast: Configuring Microsoft SQL Server 2005 Analysis Services for Kerberos authentication

If Microsoft SharePoint Portal Server is installed on the middle-tier computer, the virtual directory may be configured to allow only NTLM authentication. For more information about how to enable the vitual directory to allow negotiate (Kerberos) authentication, click the following article number to view the article in the Microsoft Knowledge Base:

832769 How to configure a Windows SharePoint Services virtual server to use Kerberos authentication and how to switch from Kerberos authentication back to NTLM authentication