Share via

Data source authentication in PerformancePoint Services for SharePoint 2010

  • Article

PerformancePoint Services supports five types of data sources:

  1. Analysis Services
  2. Excel Services
  3. Excel Workbook
  4. SharePoint List
  5. SQL Server Table

Of the five, only the Excel Workbook data source does not have an authentication model (the workbook is embedded directly into the data source).  The four remaining all support Unattended Service Account and Per-user Identity.  In addition, the Analysis Services data source supports a third authentication mode which I will talk about later.

Excel Services, SharePoint List, SQL Server Table

 image71_66afb0ea7c554498b9640d3bd6a5b988_2F49BB66 

One of the great new features of PerformancePoint Services is that you can now select different authentication options for each data source you create rather than having to decide on one authentication model to use for each web application.  The web.config properties Bpm.ServerConnectionPerUser and Bpm.UseASCustomData no longer exist (New Authentication Options for Data Sources).

An Unattended Service Account (USA) is a system identity, using minimal privileges, that is separate from an application pool identity.  The USA is configured via SharePoint Central Administration under "Manage Service Applications" –> "PerformancePoint Service Application" and credentials are stored in SharePoint's Secure Store.  For more information, see Configure the Secure Store Service (SharePoint Server 2010).  USA credentials are passed to the data source which allows all users to see the same data and also allows for greater caching opportunities.

Per-user Identity passes user’s credentials, as determined by SharePoint, to the data source.  When installing SharePoint 2010 there are two different configuration options: Basic (standalone) and Advanced (farm).  If SharePoint is configured in standalone mode Per-user Identity authentication is only supported if the data source and the application server are located on the same machine.  Kerberos constrained delegation is required to connect to a data source located on a separate machine from the application server in a farm deployment.  For more information, see Configure Kerberos authentication (SharePoint Server 2010).

Also, Per-user Identity is only supported with Windows credentials, neither anonymous or forms login are supported.

An additional authentication option for Analysis Services

image44_8b114694d3f142c4b66310de86b9be53_2F49BB66

This can be referred to as dynamic OLAP security roles and is typically a good option in environments where running constrained delegation is not an option.  The CustomData field (which contains a user name) is used by administrator defined custom MDX queries for determining user security on data that is tagged with a username.  Here is sample XML of what’s passed to Analysis Services:

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
    <DbpropMsmdMDXCompatibility>2</DbpropMsmdMDXCompatibility>
    <Catalog>Adventure Works DW 2008</Catalog>
    <CustomData>DOMAIN\user</CustomData>
    <LocaleIdentifier>1033</LocaleIdentifier>
    <Content>SchemaData</Content>
    <Format>Tabular</Format>
</PropertyList>

A TechNet article on this topic is planned but not yet available. 

Analysis Services roles

 image77_604ec61c971b493fa72441981993370f_2F49BB66 

Analysis Services roles can be used to further restrict access to data and are configured using the SQL Server Management Studio.  The connection credentials must be included in the Membership of each of the roles available to that identity.  In this example I select a role “No US” which uses dimensional security to restrict access to “US” data to ensure that data will not visible to users in this role.  As a further example an administrator could setup different data sources per sales region to view data relating just to that sales region.

Further Information

More may be found on the PerformancePoint Services blog.

Randy Sartin, SDE, Microsoft

Comments

  • Anonymous
    May 26, 2010
    My question to you is, if the Excel workbook does not support the unattended service account, can you schedule a refresh using an excel workbook published in a SharePoint library?  In our case the libary is in the parent site and Power Pivot is a subsite of that.

  • Anonymous
    November 28, 2011
    Hi, I have a question, lets say that Im managing more than one role...

  1. User can see info from the US only
  2. User can see info from Canada only
  3. User can see info for US and Canada but not from Panama For this scenario, from my understanding you will have to create in performance point one data source per role, is this correct?? ...