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

When a connection is made to a computer that is running Microsoft SQL Server 2000 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 server computer. This article describes how to configure an Analysis server computer to use the Kerberos authentication protocol.

Configure an Analysis server computer to use the Kerberos authentication protocol

To configure an Analysis server computer to use Kerberos as the authentication protocol, follow these steps:

1. Install Analysis Services Service Pack 3 (SP3) or later on the Analysis server computer and on the client computers that connect to the Analysis server computer.
2. Register a Service Principal Name (SPN) for the Analysis Services service (MSSQLServerOLAPService) on the Analysis server computer.
   
   Note If the MSSQLServerOLAPService service is running under the security context of the LocalSystem account, the SPN is created automatically. If the MSSQLServerOLAPService service is running under the security context of an account other than the LocalSystem account, you must manually create the SPN. To do so, use the Setspn.exe utility in the Microsoft Windows 2000 Resource Kit. To download the Setspn utility, visit the following Microsoft Web site:
   http://www.microsoft.com/downloads/details.aspx?familyid=5fd831fd-ab77-46a3-9cfe-ff01d29e5c46

   (http://www.microsoft.com/downloads/details.aspx?familyid=5fd831fd-ab77-46a3-9cfe-ff01d29e5c46)
   After you download the Setspn utility, follow these steps:
   1. To create the SPN for the Analysis server computer that is running under a domain account, run the following command at a command prompt:

      full_path_of_Setspn.exe -A MSOLAPSvc/serverHostName.Fully_Qualified_domainNameserverHostName OLAP_Service_Startup_Account

   2. If you must create the SPN for the Analysis server computer that is running under the LocalSystem account, run the following command at a command prompt:

      full_path_of_Setspn.exe -A MSOLAPSvc/serverHostName serverHostName
      

   3. To verify whether the SPN was created for the Analysis server computer, run the following commands at a command prompt:

      full_path_of_Setspn.exe -L OLAP_Service_Startup_Account
      

      full_path_of_Setspn.exe -L hostName

   4. If the SPN was successfully created for the Analysis server computer, the results of the command executed in step 2a or step 2b will typically appear in the following format:

      MSOLAPSvc/serverHostName.domainName MSOLAPSvc/serverHostName

3. Grant the following user rights to the domain user account that is used as the logon account for the MSSQLServerOLAPService service:
   • Log on as a service
   • Act as part of the operating system
   • Replace a process level token
   • Create a token object
   Note You can grant the user rights to the domain user account by using the Local Security Policy utility in Administrative Tools in Control Panel.
4. Grant Full Control permissions to the OLAP Administrators Windows group on the BIN and Data folders in the Analysis Services installation folder. The path of these folders on the Analysis server computer may be similar to the following:
   • C:\Program Files\Microsoft Analysis Services\BIN
   • C:\Program Files\Microsoft Analysis Services\Data
5. Add the domain user account as a member of the OLAP Administrators Windows group.
6. If the Analysis Services repository was migrated to SQL Server, the domain user account must have db_owner permissions on the repository database.

Configure Analysis server computers and Analysis Services client computers

Make sure that the following conditions are true on the Analysis server computers and on Analysis Services client computers:

• The Analysis server computers are running Microsoft Windows 2000 or later.
• The Analysis server computers are in the same Windows domain or in Windows domains that have a two-way trusted relationship. The Windows domains are using the Active Directory directory service.
• The system clocks on the Analysis server computers are synchronized. To synchronize the system clocks, use the net time command. For more information about the net time command, visit the following Microsoft Web site:
  http://technet2.microsoft.com/WindowsServer/f/?en/Library/fbf96287-fc67-46bf-8e9e-e79623e85ab51033.mspx

  (http://technet2.microsoft.com/WindowsServer/f/?en/Library/fbf96287-fc67-46bf-8e9e-e79623e85ab51033.mspx)
• The Reverse Lookup Zones property is configured on the Analysis server computers.
  
  Note Analysis Services performs a reverse lookup of the client computer's IP address to resolve the NetBIOS name.
  
  For more information about how to create reverse lookup zones, click the following article number to view the article in the Microsoft Knowledge Base:
  308201

  (http://support.microsoft.com/kb/308201/ )

  How to create a new zone on a DNS server in Windows 2000
• The Preferred DNS setting on all the Analysis Services client computers in the Windows domain point to the same Domain Name System (DNS) server. For more information about how to configure a preferred DNS server, visit the following Microsoft Web site:
  http://technet2.microsoft.com/WindowsServer/f/?en/Library/28cc8d8c-20fb-4cdb-8915-8df5905cbcf51033.mspx

  (http://technet2.microsoft.com/WindowsServer/f/?en/Library/28cc8d8c-20fb-4cdb-8915-8df5905cbcf51033.mspx)

Configure Active Directory settings

Make sure that all the following conditions are true for the Active Directory 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 not enabled for user accounts that will be delegated.
• If a domain account is used to log on to the MSSQLServerOLAPService 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 process account for any COM+ component.
• The Trust computer for delegation setting is enabled on the computer that is running IIS.

Configure Analysis Services client computers

Make sure that both of the following conditions are true on the Analysis Services client computers:

• Microsoft Internet Explorer 5.0 or later is installed.
• If Internet Explorer 6.0 is installed on the computer, enable the Enable Integrated Windows Authentication (requires restart) security option.
  
  Note The Enable Integrated Windows Authentication (requires restart) option is under Security on the Advanced tab of 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

Configure the IIS metabase to use both Negotiate and NTLM. For more information about how to do this, click the following article number to view the article in the Microsoft Knowledge Base: If the IIS application pool is running under a domain account, follow these steps:

1. Create an HTTP SPN for this account. To do this, run the following command at a command prompt:
   Setspn -a HTTP/FQDN MyAppPoolServiceAccount
   Note In this command, FQDN is the fully qualified domain name of the computer that is running IIS. MyAppPoolServiceAccount is the account that the IIS application pool uses.
2. Grant the application pool account the "Act as a part of the operating system" user right and the "Impersonate a client after authentication" user right.

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 setting an impersonation level, visit the following Microsoft Web site:
    http://msdn2.microsoft.com/en-us/library/ms681722.aspx

    (http://msdn2.microsoft.com/en-us/library/ms681722.aspx)
  • 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 setting an application identity, visit the following Microsoft Web site:
    http://msdn2.microsoft.com/en-us/library/ms687759.aspx

    (http://msdn2.microsoft.com/en-us/library/ms687759.aspx)
• The connection string that is used by the Analysis Services client computer to connect to the Analysis server computer contains the SSPI= Kerberos parameter.
• If the MSSQLServerOLAPService service is running under a domain account, the connection string that is used by the Analysis Services client computer to connect to the Analysis server computer contains the SSPI= Kerberos parameter and uses the fully qualified domain name of the Analysis Server.
• You may have to create and register an SPN for the computer that is running IIS. 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/IIS Computer Name IIS Computer Name

  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 of this article.

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base: