on 2015 Oct 29 5:11 AM
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.
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)
Works on SA12 and SA16 but not SA11, made a new post for that, if anyone has any ideas please share.
Solved this issue on SA11, solution available in separate post.
User | Count |
---|---|
75 | |
9 | |
9 | |
8 | |
8 | |
7 | |
7 | |
6 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.