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: 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:
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).
- 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
- Setspn.exe -S MSOLAPSvc.3/Fully_Qualified_domainName OLAP_Service_Startup_Account
- 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
- To verify whether the SPN was created for the Analysis Services server, run the following commands at a command prompt.If the SPN was successfully created for the Analysis Services server, the results of this command typically appear in the following format.
Setspn.exe -L OLAP_Service_Startup_Account
Setspn.exe -L serverHostNameMSOLAPSvc.3/serverHostName.Fully_Qualified_domainName
MSOLAPSvc.3/serverHostName
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.
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
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: - 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:
- The impersonation level for the COM+ packages is set to
- 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: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.
setspn -S http/IISComputerName IISComputerName
- 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:
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
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