BI Service Applications in SharePoint 2010 – Authentication (Classic vs. Claims) and Identity Delegation (Kerberos) – Part 3

Author: Chris Bailiss
Technical Reviewers (Kerberos/Claims): James Noyce, Paul Williams

Introduction

This post is part of a series of posts describing the authentication methods supported by the SharePoint 2010 Business Intelligence service applications. Please see Part 1 for an overview of this series of posts.

This post describes how to test that user identity is being delegated through the BI service applications in a web application utilising classic-mode authentication.

BI Service Application Tests in the Portal Web App – Classic-Authentication

Let’s quickly walk through some authentication tests for each service application. 

Note, for brevity, the tests illustrated here don’t cover all possible delegation paths (extend the tests in your own time, for example, to test delegation via PerformancePoint Services to SQL Relational Engine, via Reporting Services to SQL Analysis Services, etc).

Excel Services

To test identity delegation via Excel Services to the Relational Engine, first create a SQL Server view based on the SQL statement in the previous post. Then create a new Excel Workbook, connect to SQL server and create a new PivotTable based on this view. Expected results are shown in the screenshot below.

Testing identity delegation via Excel Services to Analysis Services is a little more involved. First, create a simple cube in Analysis Services (or modify an existing one, even AdventureWorks - the makeup of the cube doesn’t matter at all). Create some calculated measures based on the MDX query in the previous post. The equivalent MDX for defining measures in a cube is (paste this after the CALCULATE statement):

CREATE MEMBER CurrentCube.Measures.User as UserName();
CREATE MEMBER CurrentCube.Measures.[CustomData] as CustomData()

Deploy the cube. Now, re-open the Excel Workbook, connect to Analysis Services and base a second Pivot Table in the Excel Workbook on the cube (only use the two measures created above – ignore whatever else is in the cube).

Upload the workbook into a document library in SharePoint. View it via Excel Services (after opening, you may need to select Data >> Refresh… to update the contents):

The highlighted cells (on the left show) that the current connection from Excel Services has been authenticated using Kerberos. They also show that the user identity has been delegated through to the SQL Server Relational Engine correctly. 

The highlighted cell on the right shows that the user identity has been delegated to Analysis Services correctly. Given there are multiple hops and multiple protocol transitions involved, it’s a reasonable conclusion that Kerberos is working.

Performance Point Services

To test identity delegation via Performance Point Services to Analysis Services requires creating a simple test dashboard in Dashboard Designer. Use a connection configured with the ‘Per User Identity’ authentication setting. Then create an analytical view either based on the cube created/modified above or simply use the MDX query defined in the previous post.

Deploy this to SharePoint and view:

The highlighted cells show that the user identity has been delegated through to Analysis Services correctly.

Reporting Services

By creating a report based on the SQL View described above, delegation via SSRS can be proven:

PowerPivot for SharePoint

PowerPivot for SharePoint doesn’t directly connect to the back end data source when a user is viewing a workbook containing PowerPivot data. Therefore, no identity delegation tests are applicable.

Visio Services

To test delegation via Vision Services to the Relational Engine requires creating a web drawing and linking some shapes to external data (in this case, SQL Server). First, create a simpler version of the above SQL view that returns information just about your connection:

select s.Session_Id, s.Login_Name, s.Host_name, c.Auth_Scheme,
case when c.Auth_Scheme = 'KERBEROS' then 1 else 0 end IsKerberos
from sys.dm_exec_connections c
inner join sys.dm_exec_sessions s
on s.session_id = c.session_id
where c.session_id = @@SPID

When run from Management Studio, this returns a single record:

Next, create a new Visio Diagram. Link the shapes to this external data view:

Save this as a Web Drawing (*.vdw) into SharePoint, open it in Visio Services and click Refresh:

This shows that the user identity is successfully being delegated back to SQL Server.

Continued...

Continue reading in Part 4.