Share via

Using Claims Authentication across the Microsoft BI Stack

  • Article

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?”

Introduction

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.

SQL Server

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 claims.

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

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.

BI Clients

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 purposes.

Report Builder supports custom and forms-based authentication, but does not support SAML tokens.

Claims support across a multi-tier architecture

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).

BI Without SharePoint: Just Excel and SQL Server

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:

  • Excel, connected to a backend data store.
  • SSRS reports on a middle tier report server, connected to a backend data store.
  • SSAS cube or SQL Server relational data warehouse.
  • SSIS used to extract, transform, and load data from OLTP systems to cubes or data warehouses.
  • SQL Server Data Tools (or Business Intelligence Development Studio) authoring templates used to build cubes and Reporting Services reports: Report Designer, Tabular Model Designer, Multidimensional Model Designer.

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 to SSAS must be Windows authentication.
  • Connections to the Database Engine must be made using either Windows authentication or SQL Server authentication.
  • Connections to Windows Azure SQL Database are only through SQL Server Authentication.

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.

BI With SharePoint

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.

SQL Server Reporting Services (report server: SharePoint mode)

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 provides.

Requirements include configuring the report server to use Trusted Accounts. 

At design time, you must build the report using Windows user identity and whatever credentials are appropriate to retrieve data. Once you deploy the report to SharePoint, you can must modify data source connection information to use stored credentials (a windows user identity or database credentials). You can use pages in SharePoint, including subscription pages and model item security pages, to enter SharePoint user identities as subscription owner or to grant read-only access to model items.

 

SQL Server PowerPivot Services (PowerPivot for SharePoint)

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

Power View supports Windows, SAML, and Forms-based Claims Identities. 

 

See also