Forum Discussion
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 and it also requires BASIC authentication.
When I try to create a new column with the results of the Web API call I get the error:
DataSource.Error: Web.Contents with the Content option is only supported when connecting anonymously.
The Step in my Query that makes the call looks like this:
= Table.AddColumn(#"Create POST XML", "Roof Shape Response", each Web.Contents("https://mywebsite.com/XXXXXXXXXXXX/HTTPRequestListener", [Headers =[#"Content-type"="application/x-www-form-urlencoded"], Content = Text.ToBinary([POST XML])]))
Where the field [POST XML] is a formatted XML string with the street address of the current record included.
Does anyone know of a work-around for this? Or perhaps a more appropriate tool for solving this issue?
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 WebContentsPhil
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.
4 Replies
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 WebContentsPhil
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.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...
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
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.