Connecting to PowerPivot on Sharepoint via Excel

Question

More specifically, using credentials.

1. I was able to connect to a powerpivot workbook on sharepoint using *windows authentication* (my windows login has permission to access the sharepoint site).

2. But I wasn't able to connect using Sharepoint Credentials (my windows login doesn't have permission to access the sharepoint site). Is this not supported by Excel?

 

The reason I'm asking is that I am trying to implement a similar thing. I want my users to be able to access the powerpivot workbooks on sharepoint. Some of them might not be using a windows acc that has permissions to the sharepoint site and I would like to allow them to specify credentials. I haven't found a way to do this. Passing them via connection strings didn't work for me.

Answer 1

Hi Lusien,

What kind of user are you trying to use? PowerPivot relies on the Windows Identity Foundation (WIF)-based SharePoint security infrastructure for authentication and authorization. So, to support PowerPivot data access, SharePoint Web applications must use the Windows authentication provider. Forms authentication and Web single sign on are not supported...

 

I hope that helps.

 

Mariano

Answer 2

I was actually trying to get different kinds of user access it, but based on what you described above it seems that it is not possible. Just to re-iterate, the only way for a 3rd party to access powerpivot data which lies within sharepoint, is to login into windows using an acc recognized by sharepoint (ie integrated windows auth)?

And also, if the sharepoint site uses form-based auth, then there's no way to access the powerpivot data at all?

Thanks!

Answer 3

 

Hi Lusien,

Yes, currently we only support windows auth. There is no way to access powerpivot data through excel services (opening the workbook in the browser) with form-based auth.

When opening the workbook in the browser, before accessing the data, excel services needs to impersonate the user on the calling thread if using Windows authentication. In a claims-aware envirnment, the only "identity" that Excel Services has is the claims token. When the user connects, the first thing that SharePoint does on the web front end is to translate the authentication method’s user identity to a claims token. And it is that claims token that is passed around within the farm (and not the user's windows identy).

You can find more information on this here: http://msdn.microsoft.com/en-us/library/ff955761.aspx

And here: http://technet.microsoft.com/en-us/library/ee210621.aspx

 

Best,

Mariano

Answer 4

Thanks Mariano that really helps!

One last thing. Let's say I'm currently logged into Windows using id "domain\foo" which doesn't have permission to access the sharepoint site. And then I tried to open up the sharepoint site (eg http://sharepoint) via some browser. It will prompt me to "sign in as a different user" and to enter a new user name and password. So I entered "domain\bar" with its password ("domain\bar" does have permission to access the sharepoint site). After providing creds, I was able to access the sharepoint site just as if I've logged in using "domain\bar" into windows.

Do you mind explaining a little bit about this scenario (this looks like web single sign on)? 

Can I replicate this behavior in my own app? Is this perhaps just an impersonation scenario? Again, Thanks!

Answer 5

 

Hi Lusien,

 

Nope, that scenario does not look like web single sign-on (more on the SSO topic on http://msdn.microsoft.com/en-us/library/aa745042(v=BTS.10).aspx). On that scenario, PowerPivot for SharePoint will work since you're logging in with a domain account user (and this  "domain\bar" user from your example will be translated to a claim token, and finally this token is the identity that will travel along the farm.

So, in the scenario your user does not have access but you log in as another domain user that does have access (as you've described in your last post), it will work.

 

Best,

Mariano

 

Answer 6

Is this something that I can do programmatically?

I remember trying to do this using the Sharepoint Client Object API. It has some interface to allow logging in to sharepoint programmatically. I think it went well until the part when I tried to access powerpivot data. We usually use OLEDB to access it, but I guess in this case there is no way for the API to pass the claim token to OLEDB.

Switching gears to the API itself, it doesn't seem to provide any entry points to access the powerpivot data..

I think I got all the answers I need. But let me know if you have any solution that might work for me ;D

Thanks!

 

Answer 7

Hi Lusien,

 

In order make this thread more useful to other users, if you are satisfied with the answer to your original question, would you mark this thread as answered? That would make it easier for other users browsing the forum's threads to look for questions/answers they might be interested in.

:)

Yet, regarding your second question (on accessing a workbook programmatically), I'd be glad to help you with that too. For the same reasons I just gave you, I'd ask you to start a new thread on accessing a workbook programmatically. (that way it'd be easier for other users to browse/search in case they are looking for something like this)..

 

All the best,

Mariano

Answer 8

Will do :D