Powerquery | Web.Contents timeout when TLS connection is not already stablished

[safulco]

Hello,

I'm having a strange timeout issue when refreshing the data in Power Query, with the following query that makes an API call:

= let
        url = "https://open.bymadata.com.ar/vanoms-be-core/rest/api/bymadata/free/cedears",
 	timeout = #duration(0,0,0,5),
 	h = [#"Content-Type" = "application/json", Connection = "keep-alive"],
 	c = Json.FromValue([excludeZeroPxAndQty = true, T1 = true, T0 = false, #"Content-Type" = "application/json"]),
 	resb = Web.Contents(url, [Headers = h, Content = c, Timeout = timeout]),
 	res = Json.Document(resb),
 	t = Table.FromRecords(res)
 in
       t

Wireshark flow: wireshark_timeout-from-powerquery.png

Timeour message: powerquery_timeout-message.png

I've done some troubleshoting with the following results:

1. Calling the API from other client applications, such as Postman or a Python script, the result is success.

2. Using a proxy on the same computer (such as Charles or mitmproxy), the data refresh from Power Query success.

3. Using the same Excel file (with the same query) in another computer, the refresh of the data is done with no problems.

   1. Specs of the client computer with the issue:

      1. OS: Windows 11 Home, versión 10.0.22631, compilación 22631

      2. Excel version: Microsoft® Excel® para Microsoft 365 MSO (versión 2409, compilación 16.0.18025.20030) de 64 bits

   2. Specs of the client computer without the issue:

      1. OS: Windows 10 Pro, versión 10.0.19045, compilación 19045

      2. Excel version: Microsoft® Excel® para Microsoft 365 MSO (versión 2409, compilación 16.0.18025.20030) de 64 bits

4. The only way I can get the API results is doing the following:

   1. Change the query URL to use http protocol instead https.

   2. Refresh the data in powerquery.

   3. The server replies with 301 (moved permanently) and gives the secure URL in the 'location' header.

   4. Powerquery starts a TLS connection and get response with status 405 (Method not allowd). It seems the redirect is done using GET method, which is not allowed by the API. (powerquery_method-not-allowed-message.png)

   5. Revert the URL to https and refresh the data immediately in Powerquery (TLS connection keeps stablished) and now the api call success. wireshark_success-when-tls-connection-already-stablished.png

   6. When the TLS connection is closed, the refresh starts to fail.

Thanks for the support,

Regards.

[safulco]

Hello @AmiraBedh, thanks for helping. I verified Internet options and old protocols are disabled. I've tried also disabling my firewall but it fails in the same way.

Regarding the timeout of 5 seconds, putting a higher value does not cause any changes. In fact, I tried setting 2 minutes and in that case the server closes the connection, as expected.
I think if there was any problem during the TLS handshake I would see it through Wireshark, but I don't.

On the other hand, I've tried with an https API using GET method and there is no problems. I'm starting to think if the problem could be related to POST api calls only. But it's strange because in another computer (Win10) with same Excel version I don't have the issue.

Thanks!

Santiago.

[AmiraBedh]

I recommend that you open a ticket to the support team 🙂

[AmiraBedh]

Have you verifed the TLS on the Windows 11 machine is correctly configured? You can do this by:

• Open the Internet Options (you can search for it in the Start menu).
• Go to the Advanced tab and scroll down to the Security section.
• Ensure that TLS 1.2 is enabled and that older protocols (like SSL 2.0 or 3.0) are disabled.
• Click Apply and restart your computer to ensure the settings take effect.

I have a doubt also with your firewall or antivirus they may be interfering with the TLS handshake for Power Query but not for other applications (like Postman or Python).

In PQ, you might try forcing it to use a specific TLS version (such as TLS 1.2) by adjusting the query's Web.Contents params.

Also I think the timeout isn't for the TLS handshake to complete on the problematic machine can you try increasing  and see?