none
OData feed with custom authentication cookie RRS feed

  • Question

  • Hello,

    Current version of Power Query doesn’t seem to work against test OData V3 service with custom authentication. This custom authentication is nothing more than check for specific cookie and redirect to authentication service which sets new cookie for the whole domain and then redirects back to original URL. Fiddler shows that Power Query follows redirects but doesn’t so only once but many times until redirect loop breaks and UI displays error about endpoint not being OData one.

    Attempt to reproduce this problem in a trivial C# console application through plain HttpWebRequest against the same endpoint was successful and framework thrown exception for too many redirects. Looking at Fiddler again it was obvious that web request didn’t retain authentication cookie for subsequent request to the endpoint. This was easily remedied by setting CookieContainer property of the web request to new instance of cookie container so authentication cookie was preserved and test app worked as expected.

    Exactly the same behaviour and Fiddler trace suggests that Power Query doesn’t retain authentication cookies for its OData requests that are subject to authentication redirect.

    Is that correct and if so, would it be possible to support this scenario?
    And is there any reasonable workaround, other than custom proxy between Power Query and OData endpoint?

    Thanks

    Wednesday, November 19, 2014 11:07 PM

Answers

  • There's no easy workaround other than a custom proxy. It's not likely we'd implement direct support for this -- it's the first time we've gotten the request, I believe, and cookies are generally used for interactive web sites and not for API access. If the format of the cookie is predictable and no other authentication information is required, you may be able to set it manually with something like this:

    OData.Feed("http://foo.com/bar", null, [Headers=[Cookie="cookie text"]])

    But this won't work if there's some other initial authentication information that's required (or if the text of the cookie isn't fixed).

    • Marked as answer by Martin 111 Thursday, November 20, 2014 9:57 PM
    Wednesday, November 19, 2014 11:51 PM

All replies

  • There's no easy workaround other than a custom proxy. It's not likely we'd implement direct support for this -- it's the first time we've gotten the request, I believe, and cookies are generally used for interactive web sites and not for API access. If the format of the cookie is predictable and no other authentication information is required, you may be able to set it manually with something like this:

    OData.Feed("http://foo.com/bar", null, [Headers=[Cookie="cookie text"]])

    But this won't work if there's some other initial authentication information that's required (or if the text of the cookie isn't fixed).

    • Marked as answer by Martin 111 Thursday, November 20, 2014 9:57 PM
    Wednesday, November 19, 2014 11:51 PM
  • Thank you for your prompt response.

    It is true that web APIs are traditionally authenticated through API keys but meteoric rise of single-page applications (think Angular JS) in the enterprise environment changes this pattern somewhat. The same authentication mechanism can be used to protect both web application (SPA) and web APIs used by the same application from the JavaScript. This prevents undesirable leak of API key into the front-end and provides consistent authentication across the whole front-end.

    The goal here was to test Power Query against OData endpoint also used from web applications (SPA) while retaining the same (cookie based) single sign-on mechanism. Configurable enrichment of HttpWebRequest with CookieContainer seems rather low effort/risk enhancement enabling this kind of scenarios but it is understandable that you as a product owner have to evaluate cost and benefit of every feature request.

    Your suggestion to pass fixed cookie text is unfortunately not workable (cookie expires and is refreshed now and then) so the only possible approach seems to be a proxy.

    Btw, is there UserVoice or other mechanism to record and vote on Power Query features?

    Thursday, November 20, 2014 9:57 PM
  • The Fortune 30 company that I work for recently implemented an authentication mechanism as described above. Where can I post a request for direct support of this auth method in PowerQuery? 

    Thanks.

    Thursday, July 16, 2015 3:32 AM