How to download a file from internet using sql anywhere behind a squid proxy ?

[thomas_duemesnil]

I try to use this function on SQLA 10.0.1.3976 as a test.

alter FUNCTION "DBA"."UNBlacklist"() 
returns long varchar 
url 'http://www.un.org/sc/committees/1267/consolidatedlist.xml' 
type 'HTTP:GET' 
proxy 'http://10.0.10.20:3128/';

But always get a connection timeout.

The squid access log shows the following lines when I run the function on my sql server

1265718173.779      0 10.0.10.10 TCP_DENIED/403 1882 CONNECT www.un.org:80 - NONE/- text/html
1265718225.179      0 10.0.10.10 TCP_DENIED/403 1882 CONNECT www.un.org:80 - NONE/- text/html

When is start the request with Internet explorer the same URL

1265718374.102   4630 10.0.10.10 TCP_MISS/200 980489 GET http://www.un.org/sc/committees/1267/consolidatedlist.xml - DIRECT/157.150.195.10 application/xml

For me it looks like SQLA uses a different request type.

Thanks for your time.

[MarkCulp · Accepted solution]

This issue appears to be a Squid configuration problem and not a issue with SQL Anywhere.

First some background: When you use the 'proxy' clause in your SQL Anywhere http client procedure declaration, SA does change it behaviour slightly: Instead of making a connection to the specified URL, it connects to the specified proxy address & port and sends the adjusted http[s] request to the proxy server (i.e. the URL sent in the request contains the full //host:port/url instead of just /url).

Here's my guess on what has happened:

Initially your Squid configuration was set to only allow port 443 on outgoing connections (look for a line that looks like

http_access allow ssl_ports

or perhaps your config says

http_access deny !ssl_ports

Either way, your squid proxy server was not allowing outgoing port 80 connections. When you added 80 to the list of "ssl_ports", the http_access rules then allowed the connection to occur. (Note that "ssl_ports" is simply a name given to an "access rule".)

Take a look at your squid.conf file, and specifically the http_access rules, to figure out and understand your current configuration. Depending on what you really want to do (in terms of what you want your proxy server to allow) you can then decide upon a solution that best suits your needs.

[thomas_duemesnil · Accepted solution]

I could get the page with IE before I changed the ACL. The ACL ssl_ports is used in 'http_access deny CONNECT !ssl_ports'. 'CONNECT' is defined as 'acl CONNECT method CONNECT'. The difference between IE and ASA is that IE is not using the connect method to retrieve the file. The difference can also be seen in my log file samples (GET http://www.un.org/..) and (CONNECT www.un.org:80).

[thomas_duemesnil]

I found out that CONNECT is normaly used for SSL Connections. My SSL_port directive allows Port 443 only.

My Workaround is to add Port 80 to the Valid SSL Ports in squid.conf.

acl SSL_ports port 443 80

Is there a other solution ? Is SQLA standard conform in regards to web client connections ?

HTH