Monday, June 13, 2011

PerformancePoint Service Configuration Guide in SharePoint 2010

Recently, my friends and colleagues requested me to list the steps to correctly configure PerformancePoint Service in SharePoint 2010 with the Kerberos authentication since they struggle a lot with the correct setup. So, I spent some time to document the steps that will guide you to configure the service properly (especially for those who struggle a lot with "Per-User Identity" connection string settings). I know that many people call Microsoft Support to get this configuration right and I thought I would share with the public. I work a lot with folks in Microsoft and when we do, we follow the below steps. Some of the steps listed below are not exactly the same as what is documented in TechNet or MSDN (this does not mean Microsoft's online documentation is not right). However, there are more things to consider when it comes to specifically SharePoint 2010 and PerformancePoint configuration to work with the least privileged service accounts. I am not going to spend too much time explaining why I am performing such in each step so please feel free to leave a comment if you need more explanation. Here the step are:

Prepare the following service accounts and have them running as corresponding services:
  • Claims to Windows Token Service Account (domain\sptoken1)
  • PerformancePoint Service Account (domain\spppoint1)
  • Analysis Services Service Account (domain\spsqlas1)
  • SQL Server Browser Service Account (domain\spsqlbrowser1 or LocalSystem built-in account)
    • The steps for both the domain account and the LocalSystem built-in account will be covered in this document.
  • SharePoint Web Application Pool Identity (domain\spwebapp1)
  • SharePoint Server (spserver1.domain.com)
  • Analysis Services SQL Server (sqlserver1.domain.com)
    • We will assume that there are two Analysis Services instances, one in the default instance (MSSQLSERVER) and the other in the named instance (FINANCE).
  • (Optional Settings) The SharePoint web application is being served using an Alternate Access Mapping URL (http://intranet and http://intranet.domain.com)
NOTE: The above user accounts and variables are for a reference purpose only used in this document. Depending on the environment settings, different values may be used.
  1. Verify that the Claims to Windows Token Service is running as domain\sptoken1 and ensure that the service is started in the SharePoint 2010 servers.
  2. Add the Claims to Windows Token Service account (domain\sptoken1) to the following local groups in all SharePoint servers that are running the Claims to Windows Token Servce.
    • WSS_WPG
    • Administrators (UPDATE: this is not required)
  3. Run the following command in the command prompt window to set the dependency on the Claims to Windows Token Service
    • sc config c2wts depend= CryptSvc
      • After executing the command, verify that the command was executed successfully by going to Services -> Claims to Windows Token Service -> Claims to Windows -> Properties -> Dependencies
  4. In all SharePoint server that are running the Claims to Windows Token Service, assign the service account (domain\sptoken1) the following user rights In Local Security Policy:
    • Act as a part of operating system
    • Impersonate a client after authentication
    • Log on as a service
  5. In Command Prompt Window, check for any duplicate SPNs (by executing setspn -x). If any, resolve the duplicate SPN issue before proceeding to the next step.
  6. If you are using a named instance for the Analysis Services, in Command Prompt Window, check whether your SQL Server Browser in the SQL Analysis Services server is running under a domain account (domain\spsqlbrowser1) or a built-in account (LocalSystem).
    • If the SQL Server Browser in the SQL Analysis Services server is running under a domain account (domain\spsqlbrowser1), check whether the SQL Browser Service is set with any SPN (by executing setspn -l domain\spsqlbrowser1). The following SPNs should be listed:
      MSOLAPDisco.3/sqlserver1

      MSOLAPDisco.3/sqlserver1.domain.com
    • If the above SPNs are not listed, set necessary SPNs for the SQL Server that runs the SQL Browser Service by executing the following commands:
      • setspn -s MSOLAPDisco.3/sqlserver1 domain\spsqlbrowser1 
      • setspn -s MSOLAPDisco.3/sqlserver1.domain.com domain\spsqlbrowser1
    • If the SQL Server Browser is running under a built-in account (i.e., LocalSystem), check whether the SQL Browser Service is set with any SPN (by executing setspn -l sqlserver1). The following SPNs should be listed:
      MSOLAPDisco.3/sqlserver1

      MSOLAPDisco.3/sqlserver1.domain.com
    •  If the above SPNs are not listed, set necessary SPNs for the SQL Server that runs the SQL Browser Service by executing the following commands:
      • setspn -s MSOLAPDisco.3/sqlserver1 sqlserver1 
      • setspn -s MSOLAPDisco.3/sqlserver1.domain.com sqlserver1
    • Please refer to Microsoft Knowledge Base (http://support.microsoft.com/kb/950599) for an explanation. 
  7. In Command Prompt Window, check whether the SQL Analysis Service account is set with any SPN (by executing setspn -l domain\spsqlas1). The following SPNs should be listed:
    MSOLAPSvc.3/sqlserver1
    MSOLAPSvc.3/sqlserver1.domain.com
    MSOLAPSvc.3/sqlserver1:finance

    MSOLAPSvc.3/sqlserver1.domain.com:finance
     
    • If the above SPNs are not listed, set necessary SPNs for the SQL Analysis Service account by executing the following commands (for both default and named instances):
      • setspn -s MSOLAPSvc.3/sqlserver1 domain\spsqlas1 
      • setspn -s MSOLAPSvc.3/sqlserver1.domain.com domain\spsqlas1
      • setspn -s MSOLAPSvc.3/sqlserver1:finance domain\spsqlas1
      • setspn -s MSOLAPSvc.3/sqlserver1.domain.com:finance domain\spsqlas1 
    • NOTE: Whether you have a default instance, a named instance, or both, you only need to set SPNs to the only necessary instances. The purpose of this example here is to demonstrate how default and named instances can be set. 
    • The services needed to be added to the account’s delegation list for the account to present delegated credentials.
      • In Active Directory Users and Computers, find the Analysis Services Service account (domain\spsqlas1), right click and select Properties -> Delegation tab. Select the following options: 
        • Trust this user for delegation to specified services only 
        • User any authentication protocol
      • Click Add… button.
      •  
      • Within the Add Services pop-up window, click Users or Computers… button.
      • Find the SQL Analysis Services service account (domain\spsqlas1) and click OK.
      • Click on Select All and hit OK. 
      • Hit OK to exit to Properties window. 
    • Please refer to Microsoft Knowledge Base (http://support.microsoft.com/kb/917409) for an explanation.
  8. In Command Prompt Window, check whether the Claims to Windows Token Service account is set with any SPN (by executing setspn -l domain\sptoken1). The following SPNs should be set:
    CLAIM/spserver1
    CLAIM/spserver1.domain.com
    • If the above SPNs are not listed, set necessary SPNs for the Claims to Windows Token Service by executing the following commands: 
      • setspn -s CLAIM/spserver1 domain\sptoken1
      • setspn -s CLAIM/spserver1.domain.com domain\sptoken1
    • The services needed to be added to the account’s delegation list for the account to present delegated credentials.
      • In Active Directory Users and Computers, find the Claims to Window Token Service account (domain\sptoken1), right click and select Properties -> Delegation tab. Select the following options: 
        • Trust this user for delegation to specified services only 
        • User any authentication protocol
      •  
      • Click Add… button.
      •  
      • Within the Add Services pop-up window, click Users or Computers… button.
      • Find the SQL Analysis Services service account (domain\spsqlas1) and click OK.
      • Click on Select All and hit OK.
      • Hit OK to exit to Properties window. 
  9. In Command Prompt Window, check whether the SharePoint Web Application Pool identity is set with any SPN (by executing setspn -l domain\spwebapp1). The following SPNs should be set:
    HTTP/spserver1
    HTTP/spserver1.domain.com
    HTTP/intranet
    HTTP/intranet.domain.com
     
    • If the above SPNs are not listed, set necessary SPNs for the SharePoint Web Application Pool Identity by executing the following commands: 
      • setspn -s HTTP/spserver1 domain\spwebapp1 
      • setspn -s HTTP/spserver1.domain.com domain\spwebapp1 
      • setspn -s HTTP/intranet domain\spwebapp1 
      • setspn -s HTTP/intranet.domain.com domain\spwebapp1 
    • The services needed to be added to the account’s delegation list for the account to present delegated credentials.
      • In Active Directory Users and Computers, find the SharePoint Web Application Pool Identity account (domain\spwebapp1), right click and select Properties -> Delegation tab. Select the following options: 
        • Trust this user for delegation to specified services only
        • User any authentication protocol
      • \
      • Click Add… button.
      •  
      • Within the Add Services pop-up window, click Users or Computers… button.
      • Find the SharePoint Web Application Pool Identity (domain\spwebapp1) and click OK.
      • Click on Select All and hit OK.
      • Hit OK to exit to Properties window.
  10. In Command Prompt Window, check whether the PerformancePoint Service Account is set with any SPN (by executing setspn -l domain\spppoint1). The following SPNs should be listed:
    PPOINT/spserver1
    PPOINT/spserver1.domain.com
     
    • If the above SPNs are not listed, set necessary SPNs for the PerformancePoint Service Account by executing the following commands: 
      • setspn -s PPOINT/spserver1 domain\spppoint1 
      • setspn -s PPOINT/spserver1.domain.com domain\spppoint1 
    • The services needed to be added to the account’s delegation list for the account to present delegated credentials.
      • In Active Directory Users and Computers, find the PerformancePoint Service account (domain\spppoint1), right click and select Properties -> Delegation tab. Select the following options: 
        • Trust this user for delegation to specified services only 
        • User any authentication protoco
      •   
      • Click Add… button.
      •  
      • Within the Add Services pop-up window, click Users or Computers… button.
      • Find the SQL Analysis Services service account (domain\spsqlas1) and click OK.
      • Click on Select All and hit OK.
      • Hit OK to exit to Properties window. 
  11. The services needed to be added to the SharePoint server’s delegation list for the account to present delegated credentials.
    • In Active Directory Users and Computers, find the SharePoint server (spserver1), right click and select Properties -> Delegation tab. Select the following options: 
      • Trust this user for delegation to specified services only
      • User any authentication protocol
    •    
    • Click Add… button.
    • Within the Add Services pop-up window, click Users or Computers… button.
    • Find the SQL Analysis Services service account (domain\spsqlas1) and click OK.
    • Click on Select All and hit OK.
    • Click Add… button again. 
    • Within the Add Services pop-up window, click Users or Computers… button.
    • Find the SharePoint Web Application Pool Identity account (domain\spwebapp1) and click OK. 
    • Click on Select All and hit OK.
    • Hit OK to exit to Properties window.
  12. Run the following command in SharePoint PowerShell Management Tool
    • $w = Get-SPWebApplication -Identity http://intranet.domain.com
      $w.GrantAccessToProcessIdentity("domain\spppoint1")
       
  13. After the SPN configuration and settings in the Active Directory, restart SQL Server Browser (disco) and OLAP services in the SQL server.

6 comments:

  1. Great blog. Tried everything on the web to make this work but found this the only working solution when configuring PPS with Kerboros.

    ReplyDelete
  2. Nice write-up. Was able to see how this changed since PPS 2007. Scratched my head for a while until I read about the need for constrained delegation. Great to have the screen shots as well.

    ReplyDelete
    Replies
    1. :< the screen shots are GONE when I view it... any way we can get them fixed or a PDF posted?

      George E. Haney III, PMP
      SharePoint and Project Server Engineer, CITI
      haneyge@state.gov

      Delete
    2. Try on computers wit different firewall settings. When I view the post as an annonymous user, all pictures still come up.

      Delete
  3. Great help with dealing Preformance Point and Kerberos. Thx your guide wroks fine :)

    ReplyDelete