How to configure SQL Server 2005 Analysis Services to use Kerberos authentication (917409)

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 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: For more information about how to obtain the Windows Server 2003 Service Pack 1 Support Tools, click the following article number to view the article in the Microsoft Knowledge Base: Additionally, you can use the Kerbtray utility to verify and to remove Kerberos tickets from any one or more of the associated computers that are being used. To download the Kerbtray utility, visit the following Microsoft Web site: 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.

1. To create the SPN for the Analysis Services server that is running under a domain account, run the following command at a command prompt.

   Setspn.exe -A MSOLAPSvc.3/serverHostName.Fully_Qualified_domainName 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 command at a command prompt.

   Setspn.exe -A 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.

MSOLAPSvc.3/serverHostName.Fully_Qualified_domainName:instanceName
MSOLAPSvc.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.
• If a domain account is used to log on to the Analysis Services service, the Account is trusted for delegation setting is enabled for the domain account.
• The Account is trusted for delegation setting 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.

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.

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 impersonation level for the COM+ packages is set to Delegate. For more information about how to set an impersonation level, visit the following Microsoft Web site:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cossdk/htm/pgservices_security_6ip8.asp

  • The application identity for the COM+ packages is set to a Windows domain account where the Account is trusted for delegation setting is enabled. For more information about how to set an application identity, visit the following Microsoft Web site:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/com/htm/security_3gjd.asp

• 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. To create an SPN for the computer that is running IIS, run the following command at a command prompt from the installation folder of the Setspn utility.

  setspn -A 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.