Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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?

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

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.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Thank you so much Phil, I will give them a call.

Anonymous
Not applicable

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

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.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


PhilipTreacy
Super User
Super User

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.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors