Share via

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

  • Article

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 my classic-mode web application – “portal” – and outlines some basic user identity tests for SQL Server. These tests will be applied to the BI service applications running in the two web applications (“portal” and “claims”) in the coming posts.

Portal Web Application

I configured the “portal” web app first, running with classic-mode authentication. 

Although my VM environment is only for test purposes, to be more life-like, each service application is running under a different service account, granted a minimum set of permissions.

Kerberos Constrained Delegation has been configured through the service applications to the SQL Server instances (to both the Relational Database Engine instance and the Analysis Services instance).

The web application is configured to use the Negotiate (Kerberos) protocol.

User Identity Testing for SQL Server

Let’s spend a few moments talking about how to test that the user identity is reaching the back end systems…

[ASIDE: Since the SQL Server Relational Database Engine and Analysis Services Engine aren’t Claims aware, the tests described below are also relevant to testing that identity delegation is working for the Claims web app as described in later posts].

As stated earlier, I’m not going to spend any time on how to configure the Kerberos protocol with SharePoint. That information is available in detail at:

  • Plan for Kerberos authentication - Technet
  • Configure Kerberos Authentication for SharePoint 2010 – Whitepaper

However, it is worth noting a couple of quick ways to test that identity delegation is working, which we’ll be using later on.

A very handy piece of SQL for testing whether Kerberos is working is:

select s.Session_Id, s.Login_Name, s.Host_name, c.Auth_Scheme,
case
      when c.session_id = @@SPID then '<<<<<<<<<< **YOU** <<<<<<<<<<'
      else null end Current_Connection
from sys.dm_exec_connections c inner join sys.dm_exec_sessions s on s.session_id = c.session_id

This makes use of a couple of DMVs to list the current connections into SQL Server - plus a handy additional field that identifies your connection (hey, I like to be lazy efficient). Running this from different servers/clients and client applications enables a quick test of Kerberos / Kerberos Delegation paths. Listing all the connections also gives a quick feel for the types of authentication mechanisms being used at a given point in time.

[NB: this SQL statement requires that the view server state permission be granted to the caller].

For example, in my environment, running the above statement from SQL Server Management Studio on the client machine shows:

This shows that I (cblab\usertest) am authenticated against the server using the Kerberos protocol. Note – any connections from clients running on the SQL Server itself will use NTLM, by design.

We’ll use the same SQL statement again later.

Testing that delegation to Analysis Services is working is more difficult since it doesn’t expose the same level of connection detail. However, the following MDX query can help:

with
    member Measures.User as UserName()
    member Measures.[CustomData] as CustomData()
select {Measures.User, Measures.[CustomData]} on columns
from [Adventure Works]

The User measure in this query will return the user identity associated with the query inside Analysis Services, thus showing whether the user identity has been successfully delegated. The CustomData measure shows the value that has been passed into Analysis Services on the connection string (this is client application dependent – we’ll use it later).

For example, in my environment, running the above MDX query from SQL Server Management Studio on the client machine shows:

This shows that my identity (cblab\usertest) has reached Analysis Services.  Note - this doesn’t prove anything to do with Kerberos in this example (we could have authenticated using NTLM).  However, when this MDX query is used via other client applications in a double-hop scenario / via a SharePoint service application, it’s a reasonable test that Kerberos Delegation is working.  (If you want to be absolutely certain, turn on Kerberos logging or use NetMon).

Continued...

Continue reading in Part 3.

Comments

  • Anonymous
    November 29, 2011
    it would be nice if you could leave the link to the next post at the end of this one.