Excel Web Query not working through Proxy ANYMORE

Hello,
Suddenly (from one day to another!) my Excel files containing web queries stopped with a message that Can’t reach Internet, Page not available and other sort of messages indicating a network problem.

After going in Control Panel, Internet Options, LAN Settings and removed the Automatically detect settings, everything works file, (transparent proxy is still on but because destinations are HTTPS, these don’t get caught)

Here are my DHCP settings and my wpad.dat file - very straight forward to send my Blue clients through proxy

function FindProxyForURL(url, host)
{
if (
     (isPlainHostName(host)) ||
     (isInNet(host, "127.0.0.1", "255.0.0.0")) ||
     (isInNet(host, "192.168.GREEN.0", "255.255.255.0")) ||
     (isInNet(host, "192.168.BLUE.0", "255.255.255.0")) ||
     (isInNet(host, "192.168.GREEN.0", "255.255.255.0")) ||
     (isInNet(host, "169.254.0.0", "255.255.0.0"))
   )
     return "DIRECT";

 else

if (
     (isInNet(myIpAddress(), "192.168.GREEN.0", "255.255.255.0")) ||
     (isInNet(myIpAddress(), "192.168.VLAN1.0", "255.255.255.0")) ||
     (isInNet(myIpAddress(), "192.168.VLAN2.0", "255.255.255.0"))
   )
     return "PROXY 192.168.GREEN.x:800";

 else

if (
     (isInNet(myIpAddress(), "192.168.BLUE.0", "255.255.255.0"))
   )
     return "PROXY 192.168.BLUE.y:800";
}

While Internet Explorer LAN Settings use Automatic Detect settings I get such messages for ANY page I try to access. Example for Xe Currency Converter - Live Exchange Rates Today

There is not a singe error message in /var/log/messages. And all blocks from /var/log/squidGuard/urlfilter.log are NOT for the forex pages I tried

And, to make thing more confusing, SQUID logs shows me ONLY 200 answers I got when opening the page in Browser - or at least this is what I think (that only Browser access got logged while Excel Web Query did not)!!!

cat /var/log/squid/access.log | grep onlinetrading-fx.com
1659254757.499    518 192.168.11.15 TCP_MISS/200 5953 GET http://www.onlinetrading-fx.com/en/saxobank.htm - HIER_DIRECT/50.87.253.35 text/html
1659254757.742    190 192.168.11.15 TCP_MISS/200 6734 GET http://www.onlinetrading-fx.com/Template1_files/logo2.jpg - HIER_DIRECT/50.87.253.35 image/jpeg
1659254758.050    447 192.168.11.15 TCP_MISS/200 7707 GET http://www.onlinetrading-fx.com/en/bg1.gif - HIER_DIRECT/50.87.253.35 text/html
1659254758.266    189 192.168.11.15 TCP_MISS/200 1240 GET http://www.onlinetrading-fx.com/favicon.ico - HIER_DIRECT/50.87.253.35 image/x-icon
1659254767.583    376 192.168.11.15 TCP_MISS/200 1230 GET http://www.onlinetrading-fx.com/currencyrates.html - HIER_DIRECT/50.87.253.35 text/html
1659254767.827    194 192.168.11.15 TCP_MISS/200 1485 GET http://www.onlinetrading-fx.com/1140161191_1526_easy_forex_site/ef_files/forex.css - HIER_DIRECT/50.87.253.35 text/css
1659254768.008    372 192.168.11.15 TCP_MISS/200 2210 GET http://www.onlinetrading-fx.com/1140161191_1526_easy_forex_site/ef_files/rates_02.gif - HIER_DIRECT/50.87.253.35 image/gif

Where should I look to find what is wrong with proxy? Again, transparent proxy is ON but since this is and HTTPS destination this does not get caught.

URL filter Settings:


If there is nothing in the IPFire logs at all then maybe nothing is getting to IPFire.

I would check what is in the logs on the client machine trying to send the requests.

Were there any OS updates that were implemented overlapping with the stopping working from one day to the next.

1 Like

Let’s think a bit through, shall we?

OS updates - with or w/o them, Excell can reach any site as long as Automatic proxy detection (or WPAD) is off.

So we can rule out the OS updates

But we can’t rule out the elephant in the room which is WPAD - while that is on (for whoever is Not familiar with it - it uses both DHCP option 252 and DNS queries to WPAD.domain suffic where domain suffix is what machine has set) the Win OS gets blocked to reach the site if client = Excel, and works for same site if client = Chrome or Edge browser.

Turning of WPAD on Windows , all clients in Win OS can reach that site - Excel included.

I also turned off Suricata just to be sure - is not Suricata that blocks the traffic.

So: back to my question : what inside the proxy blocks a certain web client (Excel uses old IE libraries) to reach ANY Internet page??

Windows WPAD: Internet Explorer WPAD Diagnostic - Browsers | Microsoft Docs

1 Like

Haven’t read the cited MS doc fully, there came another fact to my mind.
Some time ago there was a problem with the WPAD script on Mozilla Firefox. It was caused by a miscalculation of myIpAddress . If direct web access is prohibited by the firewall, a DIRECT result ( or none ) of the function results in a blocked request.

Just a further search suggestion.

Not holding.
With WPAD on I can access internet from so many clients: Edge, Chrome, curl, on more than one Windows OS.

And since WPAD.dat is the same, we can conclude that is an proxy problem JUST FOR EXCEL CLIENT (aka Web Query client using old IE libraries for Internet access).

Does anybody know where proxy keeps filter for UA? (I.e. USER AGENT)

Thanks!

Does Excel use the system settings or does try to detect at its own?
The script file is interpreted on the client, either by the OS or the application.

Can you see any messages in the log files given on the MS page?

Native Win apps (Excel web query) ALWAYS use OS helpers - this is Windows world!
So WPAD is an OS agent (or whatever is its right name), and all native apps ask it to provide proxy settings

More: since Web Query is using IE, then IE is the one that asks WPAD to provide the current proxy.

Please read MS Documentation or trust me that Excel Web Query uses proxy if in Control Panel, Internet Options, Lan settings is activated Automatic Detect Settings - that turns WPAD ON

Here is Windows implementation for WPAD

And this one : winhttp.h

Did you look in the logs of the client machine?
Can you trace the request per wireshark, on the client?

1 Like