Create new Dashboard Designer data source to Analysis Services using per-user identity

Issue

There are a number of different types of data sources that we can create in PerformancePoint Services Dashboard Designer, and a number of different ways of creating them.

This article describes a way in which we can create a new Analysis Services data source in Dashboard Designer using per-user identity, and some steps for setting up the Kerberos constrained delegation that is required in order for it to work.

These are general steps for a typical environment, but they work well in the majority of cases.

Part 1 – Gather the information

Here is some sample environment information.

1. The account that is running PerformancePoint Services Application Pool is:  svcPPS
2. The name of the SharePoint Server where PerformancePoint Services is running is:   APP01
3. The account that is running the Claims-To-Windows-Token-Service is:   svcClaims
4. The name of the SharePoint Server where the Claims-To-Windows-Token-Service is running is:   APP01
5. The account that is running the Analysis Services instance that we are making the new data source connection to using per-user identity is:  svcSSAS
6. The name of the Analysis Services server that we are making the new data source connection to using per-user identity is:   SSAS01
7. The FQDN naming for domain objects is:   contoso.com

Part 2 – Set the SPNs in Active Directory

Based on our environment information above, below are the SPNs that we need set in Active Directory.

• NOTE 1: You will need to do these at a PowerShell command prompt on a Domain Controller.
• NOTE 2: Copy and paste sometimes does not work with these, so you may need to type them in manually.
• NOTE 3: The first 2 SPNs below are not valid SPNs. They are applied to the specified service accounts in order to reveal the delegation options in the AD users and computers add-in.
• NOTE 4: Using -s rather than -a will check for duplicate SPNs before adding that SPN.

1. setspn - a PPS/APP01 svcPPS
2. setspn - a CTWTS/APP01 svcClaims
3. setspn - a MSOLAPSVC.3/SSAS01 svcSSAS
4. setspn - a MSOLAPSVC.3/SSAS01 .contoso.com svcSSAS

Part 3: – Configure constrained delegation

Once the SPNs have been added in Active Directory, then we will need to configure constrained delegation for the PerformancePoint Services account, and for the Claims-To-Windows-Token-Service account.

Below are general steps for doing that.

1. In Active Directory Users and Computers > svcPPS > Properties > Delegation Tab > Trust this user for delegation to specified services only > Use any authentication protocol > Add > Users or Computers > svcSSAS > Check Names > OK > “Select All” Available Services > OK > OK.
2. In Active Directory Users and Computers > svcClaims > Properties > Delegation Tab > Trust this user for delegation to specified services only > Use any authentication protocol > Add > Users or Computers > svcSSAS > Check Names > OK > “Select All” Available Services > OK > OK.

Part 4: – Make sure Claims-To-Windows-Token-Service setup is complete

Since the Claims-To-Windows-Token-Service is involved in the Kerberos constrained delegation process, the account that is running the Claims-To-Windows-Token-Service needs to have certain permissions on the SharePoint servers where the Claims-To-Windows-Token-Service is running. 

Make sure that the account that is running the Claims-To-Windows-Token-Service (svcClaims in my sample environment information above) has the permissions below on ALL of the SharePoint servers where the Claims-To-Windows-Token-Service is running. 

1. Must be in the local Administrators group
2. In the server’s local security policy (Start > Administrative Tools > Local Security Policy > Local Policies > User Rights Assignment), the Claims-To-Windows-Token-Service account needs to have these permissions:
   1. Act as part of the operating system
   2. Impersonate a client after authentication
   3. Log on as a service

Part 5: – Testing

After all of the steps above have been completed, there may be an Active Directory replication delay before we are able to create a new Analysis Services data source in Dashboard Designer using per-user identity, but sometimes it will start working right away. If it doesn’t start working right away, wait for a little while for Active Directory replications to happen (or force it), and then close and reopen Dashboard Designer and try it again.