Answered by:
Bug in Power Query OAuth handshake

Question
-
Hi,
I'd like to report a bug in the OAuth HTTP handshake. Included below are the
request/response sequence as reported by Fiddler. Note that I am redirecting to
a URL which requires authentication.
Note that in the PowerQuery handshake that the third request is sent to the
original URL instead of to the correct redirect URL, and results in an endless
loop of asking for authentication. Compare that to the sequence done by a
browser. The third request which include the authentication header is sent to the
redirect URL and succeeds.
Demonstration A: Sequence when using OAuth in Power Query
=============================================================
1. GET /odata/Aql2OData HTTP/1.1
Authorization: Basic c3A6M0loT3RuQ3lkRzJuQVVWZWFheWo5bEttd252bTVPTjRuNzUxcnNlL3BWemRXbDBIUGJBaXovZ1NDRnNWeHMvWFlCTnZSa0JkeEhoVzFxTlBaVE5kTnNLSmZtVGwzbFUva25YZ3NCUEdsb3M9LVJRNkVmR0V6VTNuVHgvVEEzRkY1b1E9PQ==
HTTP/1.1 302 Found
Location: /odata/c4e6baed-efc1-457d-a3dc-32b47a5a5a94/Aql2OData
-------------
2. GET /odata/c4e6baed-efc1-457d-a3dc-32b47a5a5a94/Aql2OData HTTP/1.1
HTTP/1.1 401 Unauthorized
-------------
3. GET /odata/Aql2OData HTTP/1.1
Authorization: Basic c3A6M0loT3RuQ3lkRzJuQVVWZWFheWo5bEttd252bTVPTjRuNzUxcnNlL3BWemRXbDBIUGJBaXovZ1NDRnNWeHMvWFlCTnZSa0JkeEhoVzFxTlBaVE5kTnNLSmZtVGwzbFUva25YZ3NCUEdsb3M9LVJRNkVmR0V6VTNuVHgvVEEzRkY1b1E9PQ==
HTTP/1.1 302 Found
Location: /odata/86247d7c-7417-4b47-a17f-8ee1644cfc02/Aql2OData
--------------------------
4. GET /odata/86247d7c-7417-4b47-a17f-8ee1644cfc02/Aql2OData HTTP/1.1
HTTP/1.1 401 Unauthorized
Demonstration B: Sequence when using Internet Explorer
=============================================================
1. GET /odata/Aql2OData HTTP/1.1
Authorization: Basic c3A6M0loT3RuQ3lkRzJuQVVWZWFheWo5bEttd252bTVPTjRuNzUxcnNlL3BWemRXbDBIUGJBaXovZ1NDRnNWeHMvWFlCTnZSa0JkeEhoVzFxTlBaVE5kTnNLSmZtVGwzbFUva25YZ3NCUEdsb3M9LVJRNkVmR0V6VTNuVHgvVEEzRkY1b1E9PQ==
HTTP/1.1 302 Found
Location: /odata/c4e6baed-efc1-457d-a3dc-32b47a5a5a94/Aql2OData
-------------
2. GET /odata/c4e6baed-efc1-457d-a3dc-32b47a5a5a94/Aql2OData HTTP/1.1
HTTP/1.1 401 Unauthorized
-------------
3. GET /odata/c4e6baed-efc1-457d-a3dc-32b47a5a5a94/Aql2OData HTTP/1.1
Authorization: Basic c3A6M0loT3RuQ3lkRzJuQVVWZWFheWo5bEttd252bTVPTjRuNzUxcnNlL3BWemRXbDBIUGJBaXovZ1NDRnNWeHMvWFlCTnZSa0JkeEhoVzFxTlBaVE5kTnNLSmZtVGwzbFUva25YZ3NCUEdsb3M9LVJRNkVmR0V6VTNuVHgvVEEzRkY1b1E9PQ==
HTTP/1.1 200 OKSaturday, December 7, 2013 7:31 PM
Answers
-
You are right - there is a credentials bug. I had specified with the radio that the credentials apply to root (http://foo.com in your example).
As for the redirect, which is a different issue, the ideal would be that if the service URL was a redirect that Power Query would reflect this redirect in the OData.Feed() code generated. So I'll call that a feature request and not a bug.
Thanks,
Chris
- Marked as answer by Miguel.LlopisMicrosoft employee Monday, December 9, 2013 4:44 PM
Monday, December 9, 2013 12:08 AM
All replies
-
I confess that I haven't taken any time to absorb your traces. But right now, Power Query doesn't support arbitrary sites using OAuth; only a small number of whitelisted sites will work with it. We're quite aware of the need to provide more generalized OAuth support, but while I would personally describe it as "high priority" we don't currently have a timeframe for implementing it.Sunday, December 8, 2013 3:56 AM
-
Hi Curt,
I was about to respond "What?" when I saw "OAuth" in you response.
Please re-read my post and replace OAuth with OData. Sorry I always confuse those when typing.
"I'd like to report a bug in the OData HTTP handshake."
Sunday, December 8, 2013 4:18 AM -
Thanks for clarifying!
This looks like it's not specific to OData; we don't let basic auth credentials follow HTTP redirects. This is actually the built-in behavior for the BCL's HttpWebRequest class. As there may be security implications for forwarding credentials to a new destination, I don't know how quickly we'll be able to resolve this.
In any event, I've filed a bug.- Edited by Curt Hagenlocher Sunday, December 8, 2013 4:09 PM
- Proposed as answer by Curt Hagenlocher Sunday, December 8, 2013 4:09 PM
Sunday, December 8, 2013 3:00 PM -
Thanks. There is a work-around I wanted to share. One can on the server-side associate the auth sent in #3 with the URL sent in #4Sunday, December 8, 2013 5:36 PM
-
Workaround sort of works. After further reflection, I take it back that it is a bug. The reason that PQ behaves differently from a browser is that the browser is stateless where the URL is involved. It goes where redirected and stays there. But in the PQ OData handshake, PQ has stored the original URL entered by the user and that is the URL it is going to use - regardless of redirects.Sunday, December 8, 2013 6:26 PM
-
Well, PQ stores the credentials for a given URL in your user profile. The bug here is that it should prompt you for additional credentials if necessary. That is, if you initially specified that the credentials you enter should apply to everything underneath http://foo.com, then a redirect to http://foo.com/baz should use those same credentials while a redirect to http://bar.com/baz should ask for new credentials. This may seem a little more unwieldy than necessary, but it achieves the twin goals of 1) making this scenario work and 2) always letting the user know where their credentials are being forwarded.
Sunday, December 8, 2013 8:22 PM -
You are right - there is a credentials bug. I had specified with the radio that the credentials apply to root (http://foo.com in your example).
As for the redirect, which is a different issue, the ideal would be that if the service URL was a redirect that Power Query would reflect this redirect in the OData.Feed() code generated. So I'll call that a feature request and not a bug.
Thanks,
Chris
- Marked as answer by Miguel.LlopisMicrosoft employee Monday, December 9, 2013 4:44 PM
Monday, December 9, 2013 12:08 AM