Skip to main content

Security Context of PowerPivot Connections in a Farm


About This Video

This 4-part video series from Lee Graber provides an introduction to PowerPivot for SharePoint architecture. Part 2 builds on part 1 by explaining how user identity is flowed when a user requests PowerPivot data.

This video available with closed captions. To view closed captions, click CC on the video control bar.

Previous Video



Despite how fast I talk apparently we're not able to fit everything into one video.

So this is a continuation of the previous section where we will now focus on PowerPivot Integration with Excel Services.

So I believe there are other talks on PowerPivot for Excel, but what ends up, the output of this is I have a XLSX file, which is really, if you look at it is a zip file, and somewhere embedded in the file format is what is essentially an ABF.

You can just think of an ABF as our back-up file format.

So you can think of somewhere embedded in the parts of the XLSX is an ABF and that our system, when given an XLSX, knows how to find the ABF and retrieve it and interact with it.

So, I've built one of these using the PowerPivot for Excel Add-in and I've created this XLSX, it has an ABF embedded in it.

I've uploaded it to SharePoint.

I've, walking back to there, I've got it open, I'm viewing it in the browser and now I say I want to interact.

I want to actually click on a Slicer or expand out some level in the PivotTable.

When that happens, just like any other data source we are a data source.

Our OLE DB Provider is typically called MS OLAP and to Excel Services they would say oh, I need to issue this MDX query to get more data so that I can figure out what I'm supposed to display.

Let me open a connection via MS OLAP and let me send this query.

So let's go back over what we had just talked about a second ago where the user name, the user is authenticated to here.

I have a claims token that flows to here and then I have Claims to Windows Token Service running on my machine that this guy interacts with on a call, essentially, to get token.

So the connection, and there is the physical connection you can actually open up a file called connections.xml, which is embedded in the zip file, and in there you will find a connection whose data source is listed as Dollar Embedded.

If you open this up in the Excel Client you can go to Connections, after you build this workbook go to Connections, and you'll see it.

If you go to the Advanced Properties for the Excel Authentication, you will see we check Windows.

So, I'm probably going a little bit fast, but we talked about a second ago that to do Windows we require, typical Excel would require Kerberos Constrained Delegation or you can't actually flow a valid Windows Token.

You can't make the hop off the machine to some SQL Server on a different machine or an Oracle database.

Our world, though, we allow Windows because we are part of the SharePoint world.

We are actually able to understand claims tokens and use the SharePoint STS to generate a claims token even within MS OLAP.

What ends up happening is in MS OLAP the call comes in and we recognize oh, a call is Dollar Embedded Dollar.

Along with this there's an interface on the OLE DB Provider where Excel Services says, they're essentially telling us that we're in a hosted mode.

So this MS OLAP Provider is the same MS OLAP Provider that would be invoked if I was in the Excel Client opening up my Workbook and clicking on Slicers.

So the fact that we work differently here and here, there had to be a way that we knew and Excel Services actually tells us we're in a hosted environment.

The implication is do not load this embedded ABF into their process space.

We want to load it on a separate server, potentially a separate server, in a separate service, which is actually a full-blown Analysis Services engine.

Sort of the classic way you think of our server where we can do things like Share.

If multiple people are opening the same Workbook we can only load it once.

We can do things like cache the extracted ABF locally on disk in a format that is easier for Analysis Services to quickly load.

We can do a lot of other things that are in different pictures, but this is the key.

There is a way we are able to detect and have a handshake with our caller that says don't load me in process.

So when MS OLAP gets this call it says oh, it's Dollar Embedded, oh and we're in a hosted environment, so we don't want to load anything.

What we're going to say is this, what's the best way? MS OLAP, at its lowest level, has four transports.

We have a TCP, we have an HTTP, we have a Local, and we have what we call a Channel.

This is new. So this is Connect to AS, just regular Connect.

This classically is MSMDPUMP.

This classically was used for cub files.

This is a new component we have written so that just as here there is a WCF binding that's used; we are able to configure and interact with WCF in a managed world.

We actually make a hop back out into the managed world and say alright, let's interact directly with a backend Web service that is fronting Analysis Services.

This is a Physical Entity service and this is actually a Web service.

Across this we flow Claims.

So we take that user, Excel called into the Claims to Windows Tokens Service, got a limited user token.

The limited user token is enough for us to regenerate the same set of Claims that have been flowing through this level.

We regenerate them.

Our binding does the same issue token over transport.

We flow it across and at this point we are able to do things like validate that you had access to the file, and trace information about you, and forward you to the appropriate Analysis Services Server.

That might mean we have to load it.

The database may have already been loaded.

In a multi-machine scenario we might pick any one of n machines based on either round-robin or health-based choices.

Just as a note this local transport that I mentioned here previously used for cub files that's the same thing that's used here.

In the Excel only world it will use local, which essentially loads the ABF file in process.

So we can use this for in process and this is what we're using for embedded where we want to actually go out of process.

So, that is the quick way in which you can understand, why we don't require Kerberos Constrained Delegation even though we are a separate data source.

Typically, any data source that ECS uses that's marked as Windows requires Kerberos.

Now there are a couple of interesting things to note about all this.

One as I mentioned before is that c2wts actually is going to try and make a call somewhere around and go to AD.

If your machine is off the network, we can't talk to Active Directory.

I believe there's been another talk on one of the alternatives being actually configuring Active Directory on the same machine.

So, you can do that, but just know that Claims to Windows Token Service even though we're not trying to use Kerberos Constrained Delegation will try and talk to AD, even just to get a limited token.

Another thing to note is that I mentioned before that while we typically think of as doing Windows NTLM Authentication, you might be doing forms based off, and technically you can do a claims-off.

If you do either of these, this doesn't work.

There is no converting a forms-based auth user to a Windows user.

You can't do it.

At that point this becomes a non-option.

So, if you've actually configured forms-based auth, and we have some things that don't exactly work with forms-based auth, but this scenario can work if you change this to either none or SSO.

It'll affect some of our ability to track information.

So when all of this flow is happening, we're actually tracking the fact that user X did something and within Analysis Services, we do what is effectively setting the effective user, which is a classic AS term so that in SQL Profiler and stuff like that when you see who's interacting, you'll see this user.

If this user is authenticating using one of these methods, we won't be able to flow that.

So, if you use none or SSO, you can get through but you'll lose a little bit of information.

But you can actually get things functionally working.

So those are two important things to understand for different topology configurations.


Presented by: Lee Graber, Heidi Steen, Ed Price And Michele Hart



Video: WMV(Zip) | MP4 | WMV