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

[cigaras]

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 if I use function proposed by Dmitri and used by Thomas Duemesnil:

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

I get the following error:

The secure connection to the remote host failed: A certificate file must be specified
SQLCODE=-990, ODBC 3 State="HY000"

And if 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, I get the following error on SQL Anywhere version 12.0.1.4231:

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

Log file says this:

[connid = 14, 10/29 11:00:11.724]
[connid = 14, 10/29 11:00:11.724, REQUEST]
GET /sc/resources/files/consolidated.xml HTTP/1.0
Connection: close
ASA-Id: ac217a266e4c44f683ed035ab1b4b623
Accept-Charset: windows-1257, UTF-8, *
Date: Thu, 29 Oct 2015 09:00:10 GMT
Host: www.un.org
User-Agent: SQLAnywhere/12.0.1.4231

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

In version 16.0.0.2127 I get this error:

The secure connection to the remote host failed: The TLS handshake failed, error code 19
SQLCODE=-990, ODBC 3 State="HY000"

And the log file looks almost same:

[connid = 6, 10/29 11:04:30.923]
[connid = 6, 10/29 11:04:30.923, REQUEST]
GET /sc/resources/files/consolidated.xml HTTP/1.0
Connection: close
ASA-Id: d76ff37a98ce414cb2973404d2e7aedb
Accept-Charset: UTF-8, *
Date: Thu, 29 Oct 2015 09:04:30 GMT
Host: www.un.org
User-Agent: SQLAnywhere/16.0.0.2127

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

I also tried to provide certificate as a file as suggested by Jeff Albion like:

CREATE OR REPLACE FUNCTION "DBA"."UNBlacklist"()
RETURNS LONG VARCHAR
URL 'https://www.un.org/sc/resources/files/consolidated.xml'
TYPE 'HTTP:GET'
CERTIFICATE 'file=c:\\\\unorg\\\\3rd.cer';

Same result in both SA 12 and SA 16, with all 3 certificates I found on UN page.

I Also installed OpenSSL client, downloaded certificate using Jeff Albions command, got same certificate as using Google Chrome just with .pem extension, same result.

Please help.

[VolkerBarth]

Just a very wild guess:

As TLS error code 19 seems to mean "self signed certificate in certificate chain" - is there such a certificate in use? Do you supply all three certificates in one file?

[former_SQLA_member326303]

I also have unsolvable problems with COMODO-certificates & certificates chain (12.0.1.3537, 12.0.1.4314), like:
Common Name: COMODO RSA Organization Validation Secure Server CA
Signature Algorithm: RSA, SHA384

Possibly, an error occurs as described in the 16th version:
If a certificate used one of a number of algorithms (including SHA256, SHA384, and SHA512) for signing, SQL Anywhere would not have been able to use it for TLS or HTTPS. An error code of 12357 or 12394 may have been displayed. This has been fixed.

[former_SQLA_member326303]

In 16.0.0.2178 for certificates chain:

X.509 Certificate
Common Name: *.un.org
Signature Algorithm: RSA, SHA256
Key Type: RSA
Key Size: 2048 bits
Basic Constraints: Is not a certificate authority
Key Usage: Digital Signature, Key Encipherment

X.509 Certificate
Common Name: COMODO RSA Organization Validation Secure Signature Algorithm: RSA, SHA384
Key Type: RSA
Key Size: 2048 bits
Basic Constraints: Is a certificate authority, path length limit: 0
Key Usage: Digital Signature, Certificate Signing, CRL Signing

X.509 Certificate
Common Name: COMODO RSA Certification Authority
Signature Algorithm: RSA, SHA384
Key Type: RSA
Key Size: 4096 bits
Basic Constraints: Is a certificate authority, path length limit: 0
Key Usage: Certificate Signing, CRL Signing

The secure connection to the remote host failed: The TLS handshake failed, error code 19

[cigaras · Accepted solution]

I feel ashamed, I had almost exact same problem a while ago while trying to solve another problem - SOAP Error, Response truncated to 1024 characters. That was a SQL Anywhere bug, if was fixed, but there I found out that since version 12.0.1.3994 they switched from Certicom to OpenSSL (What impact will the switch to OpenSSL have on SQL Anywhere strong encryption?) and now I need a root certificate for TLS handshake to succeeded, so after some googling I found the root certificate of COMODO (there is a list of them available at their knowledge base) and now everything works!

[former_SQLA_member326303 · Accepted solution]

Therefore, I cannot just download the certificate from www.un.org-server :^( ...
Note Algorithm "right" certificate:
Common Name: AddTrust External CA Root
Signature Algorithm: RSA, SHA1 (<>SHA256, SHA384, and SHA512)

[cigaras · Accepted solution]

Works on SA12 and SA16 but not SA11, made a new post for that, if anyone has any ideas please share.

[cigaras · Accepted solution]

Solved this issue on SA11, solution available in separate post.