My name is Lee Graber. I’m a Senior Developer on the Microsoft SQL Server Analysis Services Team.
My team works on PowerPivot for SharePoint Integration.
We’re going to spend a little time today talking about how that integration works with a focus on how the Security Credentials flow from the user in IE all the way to our backend data server.
Hopefully, you’ll be able to keep up; if not, it’s a video, you can rewind it, you can watch it again, you can go read other stuff and come back to the video after you’ve figured out what the heck I’m talking about.
Today we are going to be talking about the overall architecture of the PowerPivot for SharePoint Integration with a specific focus on how security context is flowed throughout the system, how and when Kerberos is or is not, Kerberos Constrained Delegation is or is not required, and what all this Claim stuff is actually doing.
The main focus for the beginning and for actually, a majority of the talk will be our integration with Excel Calculation Services and the Excel Web Access Components.
So, I’ll probably start first by talking about how Basic Excel Web Components, Web Access, Web Services and Excel Calculation Service work even without us in the picture.
So thank you for the pretty drawings already created.
We have a browser. A user sits on his browser; they’ve uploaded a document to SharePoint and now they actually want to visualize the document in their browser without actually downloading it.
So in SharePoint, it’s as easy as, as long as you have Excel Services actually turned on, it’s actually a feature that you have to enable, you click on it and voila, it opens up.
What actually happens in there is a request is sent to an Excel Web Access, which is a set of Web Services that they expose, at which point your user is actually Authenticated.
Now, typically, this is a Windows Authentication, but if you set up your Web Application for Forms or even the latest one they have, integration with alternative Identity Providers for Claims, it’s going to authenticate.
Excel Web Access is done, is responsible for the rendering aspect.
To actually load the Workbook, they are actually going to call into a backend service running not on the Web frontend, but on an App Server; could technically be the same physical machine, but it doesn’t have to be, and communicates sending requests.
Excel Calculation Service also exposes Web Services.
Now, important thing to note right here is we have what could be considered a double-hop.
So if we assume the browser is one machine, my Web frontend is another, and the App Server’s another, in the classic web scenarios, I would have two hops and I would have had to have enabled Kerberos Constrained Delegation to actually know who this user is at the point I get to Excel Calculation Services.
In previous versions of SharePoint, they actually had a system that they called, a model they called Trusted Subsystem where, subsystem; I can actually spell, where this process running as User A.
User A was Trusted and this connection was actually done as User A and he flowed a SharePoint Token saying who this original guy was.
ECS would say, I trust User A, here’s his token and I’m going to go forward.
In SharePoint 2010, they have switched their model and they are attempting to integrate with the Windows Identity Framework and use Secure Token Services to generate Claims Tokens instead of their previous Trusted Subsystem and SharePoint Tokens.
So now what’s ending up happening here is if you actually look, you can dig into the Excel Calculation Service.
You can find their web.config and in the Security configuration you’ll find something configured that says Issued Token Over Transport.
These are all WCF Services.
If you read up on this, this is how WCF integrates with the Windows Identity Framework and Secured Token Services.
So what they’ve actually done at this point here is say, I’m going to get myself a Claims Token and internally, they’ve written their own STS.
You can dig around some in Central Admin, find a reference to their Web Service; you can see it in the list of App Pools.
They have a Secure Token Service App Pool and you can actually dig through and find their actual code.
So what ends up happening is they issue themselves a token and flow it across and on the other side, they actually have code that performs the other side of the Windows Identity Framework hooks and on the Thread you get an IClaims Principle.
So every .NET Thread has a Windows Identity and a Principle.
The Windows Identity is always a Windows user, but the Principle could be a Windows Principle or it could a Claims Principle.
So at this point, I have a Claims Principle and all of their interaction with the SharePoint Object Model is going to be able to use this to validate security.
So now I’ve gotten actually across and I still have everything.
This guy still has knowledge of who he is and if I loaded a Workbook, I can say, alright, well, this guy had access.
He better have had some type of, at a minimum, View Permission.
If he actually had Write Permission, there are ways to actually open this in Edit Mode and he can actually make changes from the Client browser and actually save.
I think these are new features in 2010.
I didn’t try and see if anything was available in 2007.
But, more importantly is, at some point or another, I might have a set of Data Sources that the Excel Workbook is actually getting data from, like I’ve got, built a table or a PivotTable and the data is sourced maybe from SQL or Analysis Services or even something like Oracle.
When the client actually interacts in a way that forces ECS to retrieve more data, we’re going to, again, have some type of, how do we flow credentials?
So let’s say I had a PivotTable and I clicked and expanded some level in the PivotTable at which point Excel Services is going to have to interact with let’s say the PivotTable is, we’ll not say it’s sourced from AS, even though that’s the typical case.
We’ll say it’s sourced from a Relational Database.
At this point, ECS needs to make a Call Out. Now, all they have is a Claims Token.
The Claims Token, last time I checked, which was yesterday, there is no OLE DB Interface to pass a Claims Token.
There is no way on a Connection String to say Security Equals Claims and at the moment, there’s no SSP Security Provider; why did I forget the first part of the S, that actually implements Claims.
So Excel Services needs to figure out a way to say, how am I going to connect?
When you created the Workbook, in the Data Source, there were three options.
There was, for Excel Services Authentication, there was Windows, None, and I think they call it SSS Application.
They might call it SSO if it’s, because it’s been around a while.
SSO, SSS, they’ve renamed some things; SSS stands for the SharePoint Secure Store.
So at this point, they have to say, what am I going to do?
So if it’s None, this translates to something they call an Unattended Account, which is a way for the Administrator of the system to create one account and say I’m going to store a user name and password and I would like, whenever anybody connects to any data source, which is configured to use None, just retrieve these user names and passwords and log in as that.
At which point, we have a new Token, a new Identity Token for that user and then we can make a Call Off with no requirements for a Kerberos Constrained Delegation.
If I have a SSO Application, sometimes also referred to as a Target Application, what I can do is I can store credentials per user.
So if this were John, John might map to Phil and some password, whereas if this is Susan, maybe it maps to Bill and some password.
It’s not really clear why I would map Susan to Bill, but it gives me the ability to actually store a user name and password and then on a per user basis, log in as a different user to connect, which could be useful for a variety of security restrictions.
Windows is the tricky part.
So, if I’ve marked it as Windows, what I’m saying is, I want to flow at this user, John or Susan, whoever it was, I want to flow.
So within the Claims Token, I do actually have the UPN of the user, the User Principal Name, which, for somebody like me, might be leegr@redmond is a simple way of writing what a UPN might look like.
There is a way to convert that UPN into a Windows Identity without actually having to know my password.
So, if I have configured Kerberos Constrained Delegation, I can take that UPN and actually use this Claims To Windows Token Service, which has a method that basically, at some low level, does something like a get Windows Token and takes a UPN.
If I have configured in AD, Kerberos Constrained Delegation, I can take this guy’s UPN, as long as this service is configured appropriately, I can generate a Windows Token and Call Out.
If I haven’t configured Kerberos Constrained Delegation, I get a Limited Token and if I tried to make a call across the network, it would just fail.
So, in any typical case, I need to have configured Kerberos Constrained Delegation to use Windows Authentication to actually flow this user all the way through the backend without any additional storage of user names and passwords.
So Claims to Windows Token Service is actually making calls to the Domain Controller to actually get the Windows Token and to validate whether you have configured Kerberos Constrained Delegation.
So that is the quick and dirty on how Excel Services works and interacts with Custom Data Sources without any PowerPivot in the scenario.
So, we’re now going to walk over to a Pre-Built Board that says what happens when PowerPivot comes into the scenario?