Today's blog is about connecting Internet/Extranet users to a PerformancePoint solution that uses Kerberos delegation to pass end-user credentials through the application layers to back-end databases. This article is about how to do it in a way that doesn't require VPN deployment, is easy to use and convenient for end-users, and adds no additional burden on SharePoint administrators or DBAs.

What? Impossible you say?  Not at all.  In fact it can be relatively easy to implement without the commonly suggested security trap-doors.  The technique has really been around for quite a while, and it's accomplished through the use of a reverse-proxy solution such as Forefront TMG or ISA server (Forefront is the name of the latest version of the product formerly known as ISA Server).

The video below is an overview and demonstration of a working solution combining the following components.

1. Windows Server 2008 R2 x64
2. SharePoint Server 2010 (CTP)
3. PerformancePoint Services (part of SharePoint 2010)
4. SQL Server 2008 R2 (CTP)
5. Forefront TMG 2010

Note: You can view this video full screen by pressing the full screen button on the bottom toolbar. It's the second item from the right-hand side.

BI Mashups

Tuesday I attended an interesting session presented by J.R. Arredondo and Dave Pae about putting together Enterprise Mashups using SharePoint Designer for WSS or SharePoint 2007. 

Mashups are one of those buzzwords, not unlike Twitter, FaceBook, etc., that sounds like a cool technology my 13-year old would be interested in but which I always try to approach with a bit of skepticism given my focus on business solutions that have ROI requirements.

And so it has been with "mashups" for me...on the radar but a bit unproven in terms of business value.

I think that needle has moved for me as a result of this session.  Of course, my primary focus is whether each new technique or technology is relevant to real-world BI solutions (not just something entertaining during a demo).

So how do I see mashups extending a traditional data-driven BI solutions?  Well, the ideal would be to take (A) traditional, planned data in a database or cube; (B) add in unstructured data (like sharepoint lists), and (C) access information on the web or from LOB systems using web services.

Modern BI solutions like PerformancePoint solve A+B, but C is not usually in the realm of end-users or analysts who assemble BI solutions.

While the Arredondo & Pae session didn't address a BI environment directly, I can see quite well how to adapt their techniques to do some interesting things. Using PerformancePoint we already have the ability to link to non-BI components, and by combining this with SharePoint designer mashup capabilities I can easily see integrating maps, internal web services and public services via various protocols.

If you haven't looked at Microsoft's Mashup page, take a look at it here: Enterprise Mashups.  And if you're not aware that SharePoint designer is now free, download it here and take a look at ways to design rich mashup pages in sharepoint.

Just as with other BI front-end technologies in a Microsoft environment, Excel Services worksheets that access back-end data (e.g. Cubes, Databases) require Kerberos delegation configuration.  However, most MOSS installations are initially configured for NTLM security, and making the transition over to Kerberos becomes a challenge since all the things done by installer programs have to be done by hand.

 If you're trying to get your Excel Services worksheets to refresh to a back-end database and receive "Data Refresh Failed" error messages, odds are Excel Services hasn't been configured to delegate security. 

 1. Open Command Prompt
 2. cd C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN
 3. stsadm -o set-ecssecurity -accessmodel delegation -ssp SharedServices1
 4. stsadm -o execadmsvcjobs
 5. iisreset

In the interest of giving credit where due, thanks to Gunter Staes (http://blogs.msdn.com/gunterstaes) for the original command sequence some time back.