Connect PowerPivot in Excel to LightSwitch oData service

Question

Hi,

I have an oData source created by LightSwitch hosted on an MS Azure web site. The data source has form (or Basic) authentication. I can connect to the data source using a browser without problem (I do have to complete a login popup). We can also connect to it using our own application.

I need to expose some data to PowerPivot in Excel. When I configure the data connection, I select Basic in the advance setting and enter the userid and password, but the server is still returning an 401 error: user not authorized. 

All the answers I found in the forum explains how to connect to an oData source that does not have security. I did find other threads with similar questions, but again no one really explained how to configure PowerPivot in this context or confirm that it can't be done.

Any help will be appreciated.

Thanks

Answer 1

I found a not ideal work around. I can create an ODBC source using the RSSBus ODBC Driver for oData, this driver does support the BASIC authentication and will connect to the source. I then configure PowerPivot to use this ODBC source.

Far from a good solution, but I can show the data to my users.

Why is it that a Microsoft Tool can't support cirrectly a Microsoft solution while thier parties can?

Answer 2

Hi Ragoran,

We can set advanced properties on the provider to specify a User ID and password. In this case, the connection request is sent using Basic authentication, and the user name and password are sent in clear text to the remote server. For security reasons, we should only do this if the connection is over an encrypted channel.

Please also vote the following feedback:
Support ODATA alternate authentication mechanisms in Excel PowerPivot: http://connect.microsoft.com/SQLServer/feedback/details/762076/support-odata-alternate-authentication-mechanisms-in-excel-powerpivot

Regards,

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
Thanks for helping make community forums a great place.