Marketing Platform and Services
One of the main charters of our group, Marketing Platform and Services, is to be a partner to Microsoft Marketing and help them in their day to day job by building a suite of software tools to increase their productivity and be effective at it. The software translation if it was that we had a whole range of software applications and tools revolving around planning, execution and sales of Microsoft's products.
Last year this time, a major overhaul was planned for our suite of reporting tools. Until then reporting was done in silos where each application would provide its own set of reports. Listening to our customers, we could see there was huge ask for our team to build a reporting platform which can transcend application data and have the ability to report and analyze it all in one place.
In this blog series, I wanted to share how we went ahead in putting the platform together, the technologies chosen; some of the architectural decisions made and share our experiences along the way. The content for this blog series is out of a white paper jointly authored by your host and a colleague of mine, Subbu. I would like to take this opportunity and acknowledge the time and effort, Derek Bevan, Senior Architect of our group, had put in to help us publish this white paper. We are also grateful to the contributions of Mark Grossbard, Ira Levin, Pej Javaheri, John Campbell and last but not the least Praba Manoharan.
Without further ado ...
Scenario:
Most enterprise reporting and analytics applications (referred to as Business Intelligence (BI)), require knowing the user who is requesting the data at the data layer. Ours was no different as our platform would be required to host secured data wherein authorizing at each layer became one of the top requirements from a security stand point.
Technologies:
Microsoft Office SharePoint (MOSS) 2007 offers a complete suite of components and services to build rich business intelligence (BI) portals. Excel is the most widely used tool for performing data analysis and reporting. Excel Services, offered as part of MOSS 2007, enhances this capability by enabling these features to be available on the browser through a server-based excel calculation service (ECS) and interactive web-based user interface.
A typical BI portal using SharePoint farm can be represented by the figure below:
In the above scenario, if the Excel report, hosted in a SharePoint site, connects to external data sources which require the end user credentials, then client identity (1) has to flow from the SharePoint farm (2) to the data source server (3) as illustrated in the figure (x).
In this scenario, following three authentication types are supported by Excel Services
- Windows Authentication
- SSO
- None
Once the choice to go with Windows authentication was made we had to decide what the underlying protocol would be, choices being - NTLM or Kerberos. NTLM is plain easy in a Windows domain and doesn't require any additional configuration as long as you dont cross a physical machine boundary. In our scenario above NTLM would have lead us to the well-known "double hop" issue and would result in authentication failure at Excel Services layer (ECS) given the multiple hops it would take for us to reach the data source. Kerberos protocol can address this issue as it supports delegating credentials across services/physical machines.
In order to help readers of this blog series understand the technicalities of enabling Kerberos in a SharePoint farm, I plan to provide an overview of the technologies involved in the process:
and then move to:
and a few follow ups once SSP is Kerberos enabled: