Microsoft KB Archive/917409

= How to configure SQL Server 2005 Analysis Services to use Kerberos authentication =

Article ID: 917409

Article Last Modified on 11/22/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Analysis Services

-





SUMMARY


When a connection is made to a computer that is running 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 2005 Analysis Services to use Kerberos authentication.



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:

http://www.microsoft.com/downloads/details.aspx?FamilyID=5fd831fd-ab77-46a3-9cfe-ff01d29e5c46&DisplayLang=en

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:

892777 Windows Server 2003 Service Pack 1 Support Tools

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:

http://www.microsoft.com/downloads/details.aspx?FamilyID=4e3a58be-29f6-49f6-85be-e866af8e7a88&displaylang=en

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.  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 -A MSOLAPSvc.3/   Setspn.exe -A MSOLAPSvc.3/   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 -A MSOLAPSvc.3/ </li>  Setspn.exe -A MSOLAPSvc.3/ </li></ul> </li>  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 </li></ol>

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.
 * 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 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. If you have native Windows 2003 forests and you want more information about how to enable cross-forest delegation, see the &quot;Forest Trusts&quot; 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.

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.</li> The application protection level is set to High (Isolated).</li></ul> </li> 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://msdn2.microsoft.com/en-us/library/ms681722.aspx

</li> 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://msdn2.microsoft.com/en-us/library/ms681314.aspx

</li></ul> </li> The connection string that is used by the Analysis Services client computer to connect to the Analysis Services server contains the SSPI= Kerberos parameter.</li> 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. .com, and the NETBIOS name may be. If you specify a numeric IP address, Kerberos authentication is disabled.</li>  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 &quot;Configure Analysis Services to use the Kerberos authentication protocol&quot; section. </li></ul>

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

<div class="references_section">