Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Error with Web.Contents and POSTing to a URL that uses basic authentication

For each row in my data I need to do a Web API call to check on some house characteristics for the address in my table.  However, the WebAPI I am working with requires the address data to be POSTed a...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    Hi Anonymous 

    You can set the connection to Anonymous and then specify Basic auth in the Headers like so

     

     

    Headers = [ #"Authorization"=EncodedCredentials, #"Content-Type"="application/x-www-form-urlencoded" ], Content = Text.ToBinary(TheContent)

     

     

    where the EncodedCredentials are 

     

     

    Credentials = "USERNAME:PASSWORD",
    EncodedCredentials = "Basic " & Binary.ToText(Text.ToBinary(Credentials), BinaryEncoding.Base64),

     

     

     

    Here's the full snippet which includes your single line of code from your query above.  You will need to integrate this into your query at the appropriate place

     

     

    let
    URL = "https://mywebsite.com/XXXXXXXXXXXX/HTTPRequestListener",
    Credentials = "USERNAME:PASSWORD",
    EncodedCredentials = "Basic " & Binary.ToText(Text.ToBinary(Credentials), BinaryEncoding.Base64),

    WebRequest = (TheContent) =>

    let

        Options = 

            [
                Headers = [ #"Authorization"=EncodedCredentials, #"Content-Type"="application/x-www-form-urlencoded" ], Content = Text.ToBinary(TheContent)    
            ],  

        Result = Web.Contents(URL, Options)

    in
        Result,




    WebContents = Table.AddColumn(#"Create POST XML", "Roof Shape Response", each WebRequest([POST XML]))

in WebContents

     

     

    Phil

    If I answered your question please mark my post as the solution.
    If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Phil,

 

Thank you so much that seemed to help.  However, now I am getting the error:

 

An error occurred in the ‘’ query. DataSource.Error: The server committed a protocol violation. Section=ResponseHeader Detail=CR must be followed by LF

 

After some quick searching it looks like there isn't a way around this error in Power Query...

PhilipTreacy's avatar
PhilipTreacy
Super User
5 years ago

Hi Anonymous 

I've encountered that problem when the site you are trying to reach is protected by a firewall, something like Sucuri, Imperva or Cloudflare.

These web firewalls alter the response headers in such a way as to make them appear invalid to the caller, resulting in the error you are getting.

More info here

https://community.powerbi.com/t5/Desktop/DataSource-Error-The-server-committed-a-protocol-violation/m-p/313460#M139048

https://stackoverflow.com/questions/37838007/power-bi-power-query-web-request-results-in-cr-must-be-followed-by-lf-error

 

When I last contacted Imperva about the issue the response I got was 

 

Thanks for contacting Imperva's Cloud WAF support.
This error normally relates to the client not being able to parse the proxy testing cookie.
The WAF proxies serve a malformed cookie to the each client, as 1 of the steps to classify the client, i.e. browser or bot
If the client cannot parse this cookie, the proxies know this is not a browser & either a bot, a CLI or a cookie-less client of some sort.
Anyway, I have disabled this cookie & it does not compromise the security of your site in any way, having this disabled.
Please try again & let us know if this resolved the issue or not.
Thank you. 

 

So they can disable it.  Can you contact the firewall provider for thesite you are trying to reach and ask them to do the same?

Cheers

Phil

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.