Browsed by
Tag: Classic Mode Authentication

PowerPivot & Claims Based Authentication–Is there hope in SQL 2012?

PowerPivot & Claims Based Authentication–Is there hope in SQL 2012?

I promised an update on this in my previous article and it is time to share what I have learned.

The issue:

As many of us have painfully found, PowerPivot v1 on a Claims Based Authentication web application is not supported in SharePoint 2010.  I had a case open with Microsoft last year that had the aspirations of rectifying that situation.  We had hoped that working with the PowerPivot team (a part of the SQL Server Product team) and the SharePoint Product team that we would be able to find a way for Microsoft to change their stance and provide us a way to allow PowerPivot v1 to be supported. 

The answer for v1:

Sadly, the changes that would have to be made were too drastic for Microsoft to make in the middle of PowerPivot v1’s existing product lifecycle. 

The new and improved question:

That begs the question: Is there hope that PowerPivot v2 will be able to work on a Claims Based Authentication web application?

The caveated answer:

Keeping in mind that the product has not launched yet and things can still change (they won’t, but lets keep hoping) and that all of the SQL 2012 TechNet that are posted currently have the following statement at the top of them:

[This documentation is for preview only, and is subject to change in later releases. Blank topics are included as placeholders.]

The answer is still no.  In the TechNet article entitled “Hardware and Software Requirements (PowerPivot for SharePoint and Reporting Services in SharePoint Mode)” you will find the section under PowerPivot Sofware Requirements says in reference to SharePoint web applications:

PowerPivot for SharePoint only supports SharePoint web applications that are configured for classic-mode authentication. If you are adding PowerPivot for SharePoint to an existing farm, be sure that the web application you plan to use it with is configured for classic-mode authentication. For instructions on how to check authentication mode, see the section “Verify the Web application uses Classic mode authentication” in Deploy PowerPivot Solutions to SharePoint.“

Well damn.  Is there any hope that this is ever going to change?

Keep your chins up.  Rumor in the interwebs is that there is going to be a new version of SharePoint coming in sometime soon.  I can tell you that Microsoft has heard and continues to hear our cries out about this matter.  The more we raise this up as an issue, the more real it will be to Microsoft and the more likely they will be to invest in making the change.  If there is no perception that this is an issue, then there is no attention that will be paid and no investment dollars that will be spent.

If this is an issue for you, make sure that your Microsoft reps hear about it.  I talk about this with every Product Team member that I can get to listen to me for 5 minutes and I have never met with anything other than a warm reception and a sincere desire to know the feedback on how to make the product better.

Is this going to stop you from deploying PowerPivot v2 and Power View?

Not a snowman’s chance in the hot Jamaica sun.  I cannot wait for the final bits to drop so that I can get this amazing new product suite into the hands of every customer who wants to do BI.  The new BI story is too game changing not to get on the bus, and if I am riding it why not be driving smile

All PowerPivot Features supported only in Classic Mode Authentication

All PowerPivot Features supported only in Classic Mode Authentication

Surprised?  I was this week as well.  I have had a case open with Microsoft since October that we have been playing with back and forth trying to figure out why automated refreshing of a PowerPivot workbook wasn’t working.We let the case go off and on for a quite a while because in the opening days we found that if we ran the PowerPivot Service Application Pool in the context of the farm admin account, that manually refreshing the data would work.  Having a work around in hand threw this issue to the back of the queue for a while.

After much diagnosis, discussion, and many weeks of thinking that the Secure Store Service wasn’t working properly, along came a very simple explanation: PowerPivot requires Classic Mode Authentication.  FBA and Claims are not supported for doing data refresh or usage data collection scenarios.

The answer to our question came via 3 references:

PowerPivot Data Refresh – Everything you always wanted to know  – Page 14 is a good place to start.  Section called “Anatomy of PowerPivot Data Refresh” – written by Mariano Teixeira Neto

Plan PowerPivot Authentication and Authorization – “To support data refresh and usage data collection scenarios, PowerPivot for SharePoint requires Classic mode authentication. PowerPivot requires a Windows domain user to be the identity behind the SharePoint security token, which it will use to create a history of user activity and document ownership, and to connect to external data sources during data refresh.” – quoted from Technet

Why PowerPivot requires ‘classic-mode’ web applications – “The second one (i.e. ‘b’ above) is a bit trickier and it is the core issue for this blog entry. In PowerPivot, when connecting through our front-end web services (aka, the PowerPivot Web Service, or PWS in our architectural design) the underlying protocol is the same as the one that earlier versions of Analysis Services used for the ‘data pump’ feature ( That protocol does not know about claims – it relies on getting a Windows identity.” – quoted from Dave Wickert, aka PowerPivotGeek

One of the answers that we got regarding this is that PowerPivot is a part of SQL Server 2008 R2 and not a part of SharePoint 2010, so it wasn’t really a SharePoint authentication problem.  ((Does that sound right?))

We have gone back to Microsoft and are looking to see if this is something they are going to be able to fix so that Claims Based Authentication can truly be the end-all-be-all solution that we are all hoping that it can be, or if this is just one of many things that we are going to run into that aren’t yet supported in CBA mode.  For those keeping count we are now up to 2 major issues that have come back to CBA as the root cause, the other being the Site Directory issue (which we are still waiting to hear if they are going to accept our hotfix request or not… last update from the engineer was yesterday).

I have to give a ton of thanks to the MS TAM (Mike Mitchell) that I work with, the engineer (Jason Haak) who worked on this case with us, and the 2 guys who seem to be the experts in the world on PowerPivot, Dave Wickert and Lee Graber.  We spent so much time focused on the technology of the platform and how cool PowerPivot was to work with, that we forgot rule #1 (which they kindly reminded us of)… RTFM.