Tame that Kerberos doggie with this MDX biscuit

According to Greek mythology, Kerberos is the three-headed hound that guards the gates of Hades. It is also the industry standard security protocol that allows a user’s security context must be passed from their workstation to multiple back-end server machines, otherwise known as the dreaded, but essential, “Double-Hop” scenario. The “Double-Hop” enables the combination of many of the Microsoft Applications that work in concert, for example SharePoint, PerformancePoint, and SQL Server Analysis Services. Those of us that have had the opportunity to work through a problematic Kerberos deployment would agree that this protocol is aptly named.

NTLM is capable of passing a user’s security token one machine deep. For example, I can use NTLM to pass my identity from my client machine to an IIS process running on a server. In this case, the IIS can recognize my security identifier and grant or deny access to secured objects – but only for those objects on that actual server. If IIS (or SharePoint) were to need to pass my credentials to another back-end server process, for example, Analysis Services, an NTLM authenticated token would not be valid because we’ve already used up our one “hop”. Enter Kerberos.

There are plenty of good postings about understanding and setting up Kerberos Authentication for BI Technologies like PerformancePoint. I won’t attempt to duplicate those postings here. Here are some links to some that are relevant:

• PerformancePoint Server 2007 Deployment Guide
• PerformancePoint Server 2007 Operations Guide
• PerformancePoint Server 2007 Planning and Architecture Guide
• PerformancePoint Planning Business Rules Development Guide
• Configure Monitoring Server for Kerberos constrained delegation
• Configuring Kerberos for SharePoint 2007: Part 2 – Excel Services and SQL Analysis Services
• Enabling Kerberos and Delegation in Analysis Services 2005
• How to configure SQL Server 2005 Analysis Services to use Kerberos authentication
• Kerberos authentication and delegation for Monitoring Server

This post is about a technique that I have been using for many of the recent BI deployments I have done. To validate that Kerberos is working, one of the questions that you need to answer is “Who does the backend database think I am?” This simple MDX query provides that functionality:

The IT Leader's Guide to Multicloud Readiness

This guide provides practical key insights and important factors to consider to make informed decisions in your multicloud journey.

Download the Guide

WITH
MEMBER [Measures].[WhoAmI] AS ‘USERNAME’
SELECT
{
[Measures].[WhoAmI]
} ON 0,
[Fiscal Calendar].[Year].[All] ON 1
FROM
[SupplyAnalytics]

The above is is an actual query that we are using as the source for a PerformancePoint Analytic Grid to test user authentication. The result looks like :

(the Domain has been blanked out to protect client confidentiality)