Applies to: SQL Server Analysis Services (SSAS), SQL Server Database Engine, SQL Server Reporting Services (SSRS), PowerPivot for SharePoint, SharePoint 2010 and 2013, Excel Services, PerformancePoint
Services, Excel, PowerPivot for Excel, Power View
This post is for Solution Architects who are looking for a clear and concise description of Claims authentication support across the Microsoft BI stack. In its entirety, this post
can be summed up as a response to this simple question: “Can I use Claims authentication in a BI solution based on Microsoft software?”
In this post, I’ll share my findings regarding claims support across the BI stack. Nothing in this post is new. If you review existing product documentation and blogs, you’ll find
the same information I did. In fact, you can follow the links at the end of this document to read the original resource.
For those who like to cut straight to the chase, here are the important takeaways.
No claims support on direct connections to the SQL Server database engine, Analysis Services (SSAS), PowerPivot for SharePoint, or native mode Reporting Services (SSRS). SSAS and PowerPivot support Windows authentication
exclusively, requiring a Windows user identity to authenticate the connection request. In a SharePoint environment, SSRS can be configured to work with a claims-based web application, but without SharePoint, a native mode report server does not support
Workarounds exist that allow for designing a claims-based solution that includes SSAS or PowerPivot in the stack. The key point to take away is that ultimately, the connection to SSAS from a middle tier service or application must be made via a Windows user
identity. Workarounds include using stored credentials, passing a Windows user identity on a connection string, or using Windows shadow accounts that map to non-Windows user accounts based on SAML tokens. See Authentication
methodologies supported by Analysis Services for more information.
Windows Azure SQL Database is not claims-aware. SQL Database on Windows Azure only supports connections from a SQL Server database user identity (database authentication). As with SSAS, if you are building a custom BI solution that includes SQL Server features,
your solution must eventually pass a Windows user identity or database user identity to SQL Server.
SharePoint is claims aware, but BI features such as Excel Services, PowerPivot, and PerformancePoint Services require a Windows security token for external data access (i.e., data refresh). If your solution does not require identity
delegation or external data access, and all connections can be resolved internally on the farm, you can use claims SAML tokens as proof of user identity.
Most BI client applications, such as Excel, PerformancePoint connecting to SSAS, or an SSRS report connecting to SSAS, are not claims-aware. Data modeling and report authoring tools are predominantly rich Windows clients that
run under a Windows user identity. Most client libraries used to connect to backend databases from these tools will require Windows authentication or database authentication methods.
Exceptions include models based on web data, such as an OData feed. You can import web data as a one-time operation in the client app. Stored credentials can be used for subsequent connections from an application server. Both
native mode SSRS and SharePoint provide for credential storage. In this case, the stored credentials are a claims identity that is used to access the OData feed.
Power View, as it exists in SharePoint 2010, is not claims aware. Power View has a dependency on SSAS tabular models or Power Pivot data models. Both of these are SSAS data sources; both require a Windows user identity for authentication
Report Builder supports custom and forms-based authentication, but does not support SAML tokens.
When it comes to Claims authentication support in Microsoft products, platforms lead the way. Both Windows Identity Framework (WIF) and Active Directory Federations Services (AD FS) fully support Claims authentication. Good news
for programmers who want to build a Claims-aware BI solution using custom code.
On the middle tier, SharePoint also supports inbound and outbound Claims authentication. Imagine a custom SharePoint site that combines application data from Facebook, Google, LinkedIn, or Twitter. This is certainly possible, and
Claims authentication would clearly play a big role in such a scenario. Full spectrum Claims support (inbound and outbound) was added to SharePoint to expressly support scenarios like this, as well as integration with other non-Windows security environments.
Lagging behind are the line of business applications. Commonly used database products, such as the SQL Server relational database engine and SQL Server Analysis Services (SSAS), do not currently support Claims authentication. If
your custom application needs to flow an individual user identity to a SQL Server data store, the user identity must be a Windows user account, or a SQL Server database user (if using the relational database engine or Windows Azure SQL Database).
Many BI solutions do not include SharePoint. If you are not using SharePoint, your MS BI solution might include all or some of the following components:
Without SharePoint, you might be using a Windows file server to store and manage files. File access might be through custom web sites in the organization’s intranet environment, or Report Manager if
you are using a native mode report server.
None of the client applications or middle tier servers in the above list will support an inbound Claims authentication request.
As noted earlier, outbound authentication to backend tier is similarly not supported.
Connections from SSAS, SSIS, and other data stores to external databases can use database credentials. In this case, credentials are passed on the connection string. The only requirement is that the
credentials are valid on the receiving end.
Client applications used to create data models or reports (this includes Excel and SQL Server Data Tools) use client libraries to connect to external data sources. Some of these libraries only support
Windows authentication, or have default connection information that resolves to the ‘current user’, which on a rich client, will always be a Windows user identity.
Report Builder can use alternative authentication methods, with the exception of SAML claims.
Both SharePoint 2010 and 2013 implement Claims for all internal, intra-farm identity management. Outside the farm, particularly when external data refresh is involved, Claims support begins to break down as additional services
and applications come into play.
Excel Services, PerformancePoint Services, PowerPivot for SharePoint, and Reporting Services in SharePoint mode all use the Claims to Windows Token Service (C2WTS) to hydrate a Windows user identity from a SPUser claims token.
For this to happen, the inbound claim must have originated as a Windows user identity.
Because Reporting Services is the only SQL Server feature that provides a level of Claims support, it’s worth delving into what that means.
SSRS server supports claims in SharePoint, meaning you can access a static report using inbound claim identity. SSRS does this by using the SharePoint object model to spin up a local security context
based on a SharePoint user. As you can see, this is not the same as being fully Claims aware by itself. As such, you can’t integrate SSRS into a custom Claims-aware application unless you plan to supply the same type of local security context that SharePoint
Requirements include configuring the report server to use Trusted Accounts.
PowerPivot has mixed support for claims identities. In SharePoint 2010, PowerPivot 2010 and 2012 require a Classic Web Application and only supports Windows Claims. However, as of SharePoint 2013 with
PowerPivot 2012 SP1, PowerPivot supports Claims-enabled Web Applications with Windows Claims identities. PowerPivot does support non-Windows claims, such as SAML, with limited functionality.
Power View supports Windows, SAML, and Forms-based Claims Identities.
Microsoft BI Authentication and Identity Delegation (this is the definitive guide for understanding authentication and delegation across the BI stack. If you only read one thing,
make it this whitepaper)
Plan for user authentication methods in SharePoint 2013 (read this to gain an understanding of claims, and how its supported in SharePoint 2013.)
How to use Windows Azure SQL Database in .NET applications
(states that Claims authentication is not supported)
Claims Authentication and Reporting Services (states that Report Builder
does not support SAML claims. Describes report server configuration so that you can access a static report using an inbound claim)
Data Authentication for Excel Services in SharePoint Server 2013 (states that
connections to SSAS and SQL Server databases must use Windows authentication or SQL Server authentication)
Use Excel Services with Secure Store Service in SharePoint Server 2013 (explains
how to use stored credentials for refreshing data in an Excel workbook that’s hosted in SharePoint content library. Stored credentials, along with the unattended data refresh account, are workarounds that allow you to provide a Windows user identity to a backend
Store credentials for a report data source (Report Manager) (explains how to
store credentials used to access data in an SSRS report)
Service Applications in SharePoint 2010 – Authentication (Classic vs. Claims) and Identity Delegation (Kerberos) (provides detailed, step-by-step exploration of attempt to use Claims authentication with BI service applications)
Plan for claims-based authentication or classic-mode authentication (SharePoint Server 2010)
(provides background information for choosing an authentication methodology, especially relevant for non-BI solutions)