locked
Problem with fetching Odata feed with security Token RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi, 

    i have a problem with fetching Odata feed in Power Query. 

    I am trying to connect to Odata feed, that is secured by secure Token at the end of the query. 

    Query looks like this: https://app.idoklad.cz/odataservice.svc/IssuedInvoices/?secureToken=5324847bbb14ca413ae10b9324825d3f7de53fa57eac2c166a59690cb989ab35

    In Power Pivot everything works just fine, but in Power Query there is 500 Internal Server Error. So i looked into Fiddler and see that first https request is with secure token (handshake probably), but there are two without it instantly after it: "https://app.idoklad.cz/odataservice.svc/IssuedInvoices?top=1000" and "https://app.idoklad.cz/odataservice.svc/IssuedInvoices".

    Is there a way to configure power query to add secure Token to every request? Because without it, i cant get data form Odata feed.

    Thanks for your help

    Tuesday, March 11, 2014 10:39 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    My interim solution was to add a caching proxy server. Since the first request does have the token, you can make the proxy call to get the results, save them, then replay that on subsequent requests without the token.  Not too painful if you are a developer and you need an immediate solution.

     - Chris

    Sunday, March 16, 2014 2:55 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    It looks like there isn't a way to do this right now, unfortunately, though there's already a bug filed to fix the problem.
    Tuesday, March 11, 2014 1:18 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    So I need to wait until next version of PQ and hope that this will be fixed I guess.

    Is there some kind of bug tracking system, where I can see if the problem is fixed or when it will be fixed?

    Thank you

    Ondra Planicka

    Tuesday, March 11, 2014 3:13 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    No, we do not have a public bug tracking system.
    Friday, March 14, 2014 6:51 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    My interim solution was to add a caching proxy server. Since the first request does have the token, you can make the proxy call to get the results, save them, then replay that on subsequent requests without the token.  Not too painful if you are a developer and you need an immediate solution.

     - Chris

    Sunday, March 16, 2014 2:55 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I just checked in a few changes to OData support, including   

    1) When accessing a feed directly, custom query options are now correctly propagated. That is, if you write

    OData.Feed("http://some.url/Foo.svc/feed?key=value")

    It will preserve the "key=value" part of the URL on subsequent calls. This does not work, however, if you start with the service document because it's intended to support feed-specific options and the service document is not semantically a feed. So for authentication purposes, I also implemented

    2) What we call "Web API" in the user interface. For this, your call looks something like

    OData.Feed("http://some.url/Foo.svc/", null, [ApiKeyName="key"])

    and the actual security token is stored separately per user+machine the same way we store all our other passwords. If the security token is "value" then this will generate an URL with query string key=value.

    Unfortunately, the user experience around creating a new Web or OData source with this form of authentication isn't very good right now; instead of using "From Web" or "From OData", you'll need to start with "From Blank Query" in order to be able to enter the formula with the ApiKeyName. We're hoping to improve that at some point.

    This will be part of the next release, which you can probably expect in the first part of April.

    Friday, March 21, 2014 3:31 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Curt,

    That's great news and I look forward to trying it out.  

    I'm not clear on where one would enter "value" - the value for the key.

     - Chris

    Friday, March 21, 2014 3:53 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    To see what I mean, you can today try the following

    =Web.Contents("http://localhost", [ApiKeyName="key"])
    You will get a notification that "The credentials provided are invalid." When you click "Edit Credentials", go to the Web API tab and enter (say) "value". The request that's made will be for http://localhost?key=value. (Presumably, it will also fail, but that's not the point of the test. :)

    Friday, March 21, 2014 1:06 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Curt,

    I would like to write a webservice using LightSwitch using APiKey so that it is accessible to Power Query. Do you have some example solution for that?

    Bas

    Tuesday, August 12, 2014 2:33 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    If you're looking for examples of how to write a web service using LightSwitch, you'll need to ask somewhere else. If you have an existing (or future) web service and can describe how it's used, then we can help you to use it from Power Query. But this isn't something for which an arbitrary example is likely to be useful.
    Tuesday, August 12, 2014 3:28 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    This is works!

    The UX isn't great, but it works.

    However if I use a regular Odata source PQ will automatically get all the tables from metadata and allow me to pick and choose the tables I want. Using this approach certainly solves the authentication issue but leave me hanging again as I have to manually select the tables and create individual queries for each and load to a new sheet.

    We're a couple releases down the road from this original question - is there anything new in the pipeline to solve these issues?

    Best regards,

    Peter

    Monday, December 15, 2014 9:29 AM