Section=ResponseHeader Detail=CR must be followed by LF RRS feed

  • Question

  • I want to query the following API and parse the json response in the selected cells through PowerQuery for Excel 2010 (last version):

    Unfortunately I get this error:

        The server committed a protocol violation. Section=ResponseHeader Detail=CR must be followed by LF.

    I contacted the server owners, but they said it had something to do with DDOS protection since they moved their hosting. Is there anything I could try?

    Here is another API that doesn't work and sends the same error:

    • Edited by Fullhdpixel Wednesday, June 4, 2014 7:47 PM
    Saturday, May 31, 2014 9:03 PM


All replies

  • We debugged this a bit and it looks like they are using control characters in their cookie values. That's a violation of the HTTP spec. We're using .NET libraries to parse these web files so unfortunately there isn't a workaround.
    Monday, June 2, 2014 10:53 PM
  • Thanks a lot for your reply.

    It would be awesome if this worked again :)

    Tuesday, June 3, 2014 4:36 PM
  • Here is another API that doesn't work and sends the same error:

    Wednesday, June 4, 2014 7:47 PM
  • Out of curiosity, did they explain how a cookie with an illegal character value helps to protect against DDOS? I wasn't able to find any evidence on the internet that this is a known strategy for dealing with DDOS attacks.

    It would be nontrivial for us to change the current behavior because it doesn't happen in our own code, so it would basically require that we reimplement parts of the .NET framework.

    Thursday, June 5, 2014 1:35 PM
  • no, unfortunately not.

    I did some research too  and I think there should be an option to disable all these kind of problems.

    This for example:


    Thursday, June 5, 2014 1:40 PM
  • There are at least three problems with that:

    1) Ad hoc testing showed that this only disabled the check when actually parsing the header. When we subsequently built a WebHeaderCollection manually, the check still seemed to get enforced.

    2) Presumably, the check exists for a reason and not just because the .NET team likes to rigorously enforce open standards. We would have to go through a security review for such a change.

    3) The Power Query engine runs in contexts other than the Power Query Excel addin, and in those contexts we don't control the app.config. We would have to ask all of those downstream customers to make the same change, and that might not be something they're willing to do. Imagine, for instance, that you're using the hosted refresh feature in SharePoint Online. Your query runs just fine in your workbook on your local computer, but consistently fails to refresh in SharePoint because the refresh service hasn't made a similar configuration change.

    EDIT: I should add that we're as disappointed as you are that Power Query won't work in this case.

    Thursday, June 5, 2014 1:56 PM
  • okay, I just thought it would be possible to add this as an additional feature.
    Friday, June 6, 2014 6:33 PM
  • GAH! I thought this method worked. And it seemed to. Until I saved the file. Now getting same error. For reference tho:

    This (will briefly) correct the "CR must be followed by LF" when it occurs in the page (not the cookies as mentioned below)

        BufferedBinary = Binary.Buffer(Web.Contents("")),
        CleanedUp = Text.Replace(Text.Replace(Text.FromBinary(BufferedBinary), "#(cr,lf)", "#(lf)"), "#(lf)", "#(cr,lf)"),
        Table = Web.Page(CleanedUp)

    It does this by 

    1.) Loading the page into a binary buffer

    2.) Converting it to text

    3.) Replacing all CRLFs with LFs, then replacing LFs with CRLFs

    4.) Reading that text as a table

    Here is a step by step version

        BufferedBinary = Binary.Buffer(Web.Contents("")),
        TextStep1 = Text.FromBinary(BufferedBinary),
        TextStep2 = Text.Replace(TextStep1, "#(cr,lf)", "#(lf)"),
        TextStep3 = Text.Replace(TextStep2, "#(lf)", "#(cr,lf)"),
        Table = Web.Page(TextStep3)

    Woot! Vote Bernie!

    • Edited by James White Wednesday, June 15, 2016 2:27 PM
    Wednesday, June 15, 2016 2:06 PM
  • For what it's worth, every report we've ever gotten of this problem -- including for the results -- has been traced to a single company: Imperva/Incapsula. I've tried contacting them to fix their product to be IETF standards-compliant with the cookies they generate but that just netted me some boilerplate sales response :(.

    Wednesday, June 15, 2016 2:56 PM
  • Hehehe, yup, Incapsula.. Ouch. 

    Maybe there is some way to proxy the content while cleaning up the header. Or build a custom data source in C#.. Hmm

    Friday, June 17, 2016 6:56 PM
  • Sebastian (Incapsula Support)

    Jun 26, 10:03 IDT

    Hello Greg,
      Thank you for bringing this matter to our attention. I'd like to elaborate   regarding CRLF:
      Section=ResponseHeader Detail=CR must be followed by LF error occurs as a   response to our cookie classification method.
      Basically, CR tells the cursor to move to the first position on the same   line, while LF tells the cursor to move to the next line. Combining them   together (<CR><LF>) makes the same effect as “Enter” does. The   request/status line and other header fields must each end with   <CR><LF>
      The cookies that Incapsula sends are "broken" on purpose, and they   include content whose purpose is to test how the client responds to an   irregular cookie - as part of our classification process. While browsers are   capable of handling such cookies, most bots aren't, and this is what serves   as a first line of defense against them.
      While it does serve its purpose, it can sometimes cause a malfunction with   the clients' app/sites.
      This is why we offer additional classification methods in order to allow the   customer to bypass the cookie test and still retain Incapsula's protection.
      Please let us know if you require any further assistance with this matter, or   any other.
      Thank you.

      Incapsula Global Support

    Monday, June 26, 2017 7:19 AM
  • +1.... can't add my Bitstamp url to excel yo!!
    Monday, July 3, 2017 9:28 PM
  • I've just hit this exact problem and it saddens me deeply that this issue doesn't even have a workaround :(
    Thursday, February 15, 2018 10:29 PM
  • Sebastin,

    I am not sure I understood what you mean by on purpose. 

    We are a big company and have a vendor, a big trustful financial company, that made an API available which is giving the same problem while we are trying to consume the API in PowerBI. The aim of providing an API is to consume it automatically using PowerBi bot.

    Having said that. How can that API be consumed by PowerBI automatically if Incapsula does that on purpose? How can the cookie be bypassed so the API can be consumed automatically?


    • Edited by Vannago Friday, June 26, 2020 5:11 PM
    Friday, June 26, 2020 5:11 PM