Microsoft Edge Try Microsoft Edge A fast and secure browser that's designed for Windows 10 Get started

Skip to main content
Microsoft
Microsoft Support
  • Office
  • Windows
  • Surface
  • Xbox
  • Deals
  • Support
      • Windows apps
      • OneDrive
      • Outlook
      • Skype
      • OneNote
      • PCs & tablets
      • Accessories
      • VR & mixed reality
      • Microsoft HoloLens
      • Xbox games
      • PC games
      • Windows digital games
      • Movies & TV
      • Books
      • Microsoft Azure
      • Microsoft Dynamics 365
      • Microsoft 365
      • Cloud platform
      • Enterprise
      • Data platform
      • .NET
      • Visual Studio
      • Windows Dev Center
      • Docs
      • Microsoft Store
      • Free downloads & security
      • Education
      • Store locations
      • Gift cards
    • View all
    0
    Sign in
    Microsoft Support

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

    Content provided by Microsoft

    Content provided by Microsoft

    Applies to: Microsoft SQL Server 2008 Analysis ServicesMicrosoft SQL Server 2005 Analysis Services


    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 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
      • 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
    • 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


    Last Updated: Aug 16, 2012
    • Email
    • Print
    Thanks! Your feedback will help us improve the support experience.

    What's new

    • Surface Book 2
    • Surface Pro
    • Xbox One X
    • Xbox One S
    • VR & mixed reality
    • Windows 10 apps
    • Office apps

    Store & Support

    • Account profile
    • Download Center
    • Sales & support
    • Returns
    • Order tracking
    • Store locations
    • Support
    • Buy online, pick up in store

    Education

    • Microsoft in education
    • Office for students
    • Office 365 for schools
    • Deals for students & parents
    • Microsoft Azure in education

    Enterprise

    • Microsoft Azure
    • Enterprise
    • Data platform
    • Find a solutions provider
    • Microsoft partner resources
    • Microsoft AppSource
    • Manufacturing & resources
    • Financial services

    Developer

    • Microsoft Visual Studio
    • Windows Dev Center
    • Developer Network
    • TechNet
    • Microsoft Virtual Academy
    • Microsoft developer program
    • Channel 9
    • Office Dev Center

    Company

    • Careers
    • About Microsoft
    • Company news
    • Privacy at Microsoft
    • Investors
    • Diversity and inclusion
    • Accessibility
    • Security
    English (United States)
    • Terms of use
    • Privacy & cookies
    • Trademarks
    • © Microsoft 2018
    This site in other countries/regions
    Algérie - Français
    Argentina - Español
    Australia - English
    Belgique - Français
    België - Nederlands
    Bolivia - Español
    Bosna i Hercegovina - Hrvatski
    Brasil - Português
    Canada - English
    Canada - Français
    Chile - Español
    Colombia - Español
    Costa Rica - Español
    Crna Gora - Srpski
    Danmark - Dansk
    Deutschland - Deutsch
    Dominican Republic - Español
    Ecuador - Español
    Eesti - Eesti
    El Salvador - Español
    España - Español
    Estados Unidos - Español
    France - Français
    Guatemala - Español
    Hong Kong SAR - English
    Hrvatska - Hrvatski
    India - English
    Indonesia (Bahasa) - Bahasa
    Ireland - English
    Italia - Italiano
    Latvija - Latviešu
    Lietuva - Lietuvių
    Luxembourg - Français
    Magyarország - Magyar
    Malaysia - English
    Maroc - Français
    México - Español
    Nederland - Nederlands
    New Zealand - English
    Norge - Bokmål
    Panamá - Español
    Paraguay - Español
    Perú - Español
    Philippines - English
    Polska - Polski
    Portugal - Português
    Puerto Rico - Español
    România - Română
    Schweiz - Deutsch
    Singapore - English
    Slovenija - Slovenščina
    Slovensko - Slovenčina
    South Africa - English
    Srbija - Srpski
    Suisse - Français
    Suomi - Suomi
    Sverige - Svenska
    Tunisie - Français
    Türkiye - Türkçe
    United Kingdom - English
    United States - English
    Uruguay - Español
    Venezuela - Español
    Việt Nam - Tiếng việt
    Ísland - Íslenska
    Österreich - Deutsch
    Česká Republika - Čeština
    Ελλάδα - Ελληνικά
    България - Български
    Казахстан - Русский
    Россия - Русский
    Україна - Українська
    ישראל - עברית
    الإمارات العربية المتحدة - العربية
    المملكة العربية السعودية - العربية
    مصر - العربية
    भारत - हिंदी
    ไทย - ไทย
    中国 - 简体中文
    台灣 - 繁體中文
    日本 - 日本語
    香港特別行政區 - 繁體中文
    대한민국 - 한국어