How to download a file from internet using sql anywhere 11 that requires a certificate?

[cigaras]

I got this working on SA12 and SA16, but some of out clients still use SA11 and in there it does not work neither using the root sertificate nor the ones I found on United Nations website.

I need to get UN Blacklist XML periodically, same as Kumar back in 2009, however since 2015-10-14 UN website requires a certificate so I specify certificate like this:

CREATE OR REPLACE FUNCTION "DBA"."UNBlacklist"()
RETURNS LONG VARCHAR
URL 'https://www.un.org/sc/resources/files/consolidated.xml'
TYPE 'HTTP:GET'
CERTIFICATE 'cert=!cert';

where instead of !cert I put Base-64 encoded certificate string that I got from UN site using Google Chrome or the root certificate I got from COMODO, I get the following error on SQL Anywhere version 11.0.1.3069 and 11.0.1.3158:

Invalid response from the HTTP server
SQLCODE=-988, ODBC 3 State="HY000"

However it works using versions 12.0.1.4231 and 16.0.0.2127 if root certificate from COMODO is provided.

Log file says this:

[connid = 20, 12/28 11:57:39.648]
[connid = 20, 12/28 11:57:39.648, REQUEST]
GET /sc/resources/files/consolidated.xml HTTP/1.0
Connection: close
ASA-Id: ed349e147ee948e6968bc918326c4f3a
Accept-Charset: windows-1257, UTF-8, *
Date: Mon, 28 Dec 2015 09:57:39 GMT
Host: www.un.org
User-Agent: SQLAnywhere/11.0.1.3069

[connid = 20, Error: socket closed by peer]
[connid = 20, socket closed]

Please help.

[Former Member]

The versions of 12.0.1 and 16 you are testing with use the OpenSSL security library whereas version 11 only ever used the Certicom libraries. So it is possible the certificate is not compatable with Certicom and you may not be able to get past this with the verson 11.0.x software.

I believe the -988 error is usually related to a failure during the TLS-handshake (V12 and V16 would have shown more details). A wireshark trace would help to confirm that. But if that is the case then the specific TLS error (or sequence of packets) will be important to be identified from the trace.

The certificate itself, it is a newer style one and was created for the whole UN organizations (*.un.org) and not for just 1 server/purpose. If memory serves me such a multi-purpose certificate might not work with the Certicom libraries. But I don't have version 11.0.1 handy should haven't seen what is happening in your case.

[cigaras · Accepted solution]

After some more testing I find out that the only certificate working with SA11 is the one issued for *.un.org:

With SA12 only the AddTrustExternalCARoot from Comodo website works.

And with SA16 You can use either same AddTrustExternalCARoot certificate as SA12 or the root COMODO SECURE™ certificate found on United Nations website: