Calling an external web service via https using the system certificate store

[Stalker4]

Hi,

SAP SQL Anywhere 17.0.10.5866 for WIndows

There is an external web service
Url: https://MyService.com/token
Request method: POST
Content-Type: x-www-form-urlencoded

Created a web procedure to call it:

CREATE PROCEDURE "dba"."TestAccessToken2"( in "client_id" long varchar,in "client_secret" long varchar,in "grant_type" long varchar ) 
result( "cAttribute" long varchar,"cValue" long varchar,"npp" integer ) 
url 'https://MyService.com/token'
set 'HTTP(VERSION=1.1)'
type 'HTTP:POST'
CERTIFICATE 'file=*'

Please note that in the body of this procedure, I specified “CERTIFICATE ‘file=*’”, which means that I want SQL Anywhere to use a certificate from the operating system's certificate store when calling an external web service via https.

 Example of calling this web procedure:

select * from dba.TestAccessToken2('Myclient' ,'MySecret', 'credentials')

When executing this code (calling the web procedure), I get an error message:

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

Question: Am I calling this external web service correctly ?
Why is the error occurring—did I specify something incorrectly, or is the required certificate missing from the system store ?

[VolkerBarth]

Well, without knowing the external API, it's hard to tell but your call seems fine to me. You can of course also simply use CALL to call the procedure without using a SELECT statement...

Nevertheless, it should be easy to find out whether certificates from the system store do match the web server's certificate... And you can also provide a fitting vertificate (chain) explicitely.

[Stalker4]

But as I understand it, in order to find out whether the certificates from the system repository match the web server certificate, and even more so to explicitly specify the appropriate certificate, I first need to ask the owner of this web server what certificate they are using there, am I right ?

I would also like to clarify one point: Could this problem arise due to incompatibility between the security and transport protocol versions (SSL, TLS, HTTP) of the web server and SQL Anywhere 17.0.10.5866? Perhaps newer versions of SQL Anywhere support newer versions of security and transport protocols (SSL, TLS, HTTP) ?

[VolkerBarth]

Well, can you call the according web server via browser, too? If so, you can usually easily display the web server's certificate and its chain in the browser. - There have been changes to the underlying crypto libraries in SA17 (initially OpenSSL, later SAP Common Crypto Lib) but I guess your version already uses the latter. AFAIK, the web client does not need to provide the web server's certificate itself but one that does match the issuer of the web server's certificate.

[Stalker4]

We are talking about the website https://sbx-id-service.globalpoll.com.ua FireFox 144.0.2, when I tried to go there by entering this address in the address bar, the certificate information says “Google Trust Services.”

However, the owners of this website say that it is protected by “CloudFlare”. Could this somehow affect access to the web services of this website ?

If you specify the “-zoc” parameter when starting the server, only debug information via HTTP is displayed, and information via HTPS (including certificates and security protocol versions) is not shown.
Does SQL Anywhere have a parameter that allows debug information via HTTPS to be displayed ?

[Stalker4]

I also noticed that sometimes the error HTTP request failed. Status code '<NONE>' SQLCODE=-983, ODBC 3 State="HY000" occurs, rather than "TLS handshake".

I forgot to mention that this web service itself is fully functional; I can call it without any problems from "Postman" and from a program I wrote in "Delphi".
In other words, the problem lies specifically in calling this web service from SQL Anywhere 17.0.10.5866.

[VolkerBarth]

Hm, the error message seems to mix up SQLCODEs and ODBC 3 states? (HY000 would be "Database server already running")... - That being said, have you tried to use web client logging (via sa_server_option('WebClientLoggingusing'...) and/or used the procedure's HTTP(EXCEPTIONS=OFF) option to get more diagnostics?

If you are able to call the web service via other web clients, are you not able to find out how they accept the web server's certificate? Do they use SNI (which you could make SQL Anywhere use via the CERTIFICATE clause....)?

Otherwise, I'm out of my wits.

[Stalker4]

The server is running with the “-zoc” option. Here is the result of the server log for this Web service call.

[connid = 8, [30/10/2025:15:59:01.796 0200]]
[connid = 8, [30/10/2025:15:59:01.796 0200], REQUEST]
POST /realms/dnipro/protocol/openid-connect/token HTTP/1.1
Content-Type: application/x-www-form-urlencoded; charset=windows-1251
Connection: close
ASA-Id: bcb757c755f4420581a02a295a32ae91
Content-Length: 99
Accept-Charset: windows-1251, UTF-8, *
Date: Thu, 30 Oct 2025 13:59:01 GMT
Host: sbx-id-service.globalpoll.com.ua
User-Agent: SQLAnywhere/17.0.10.5866

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

If necessary, I can show you the log of this web service call from "Postman". Would you like to see it ?

I made a small change to the procedure itself:

set 'HTTP(VERSION=1.1;EXCEPTIONS=OFF)'

What is “SNI” ?

[VolkerBarth]

SNI is Server Name Indication. As to your POST request, is it expected that is has no body? (At least, that's quite unusual IMHO.) Generally, I would compare the logged requests and responses of your SA web client with that of other working clients and try to find relevant differences. Aside: I'm just another SAP customer so you might ask the official SAP support for more.

[Stalker4]

I provided the text of my web service and an example of its call above.

Since it is called "POST and x-www-form-urlencoded", it has a body, which is supposed to transfer parameters to the web service. For some reason, it is not in the log of this body (maybe SQL Anywhere does not write it to the log?), but the log contains the field “Content-Length: 99” — as I understand it, this shows that everything is still being transferred to the web service.

[Stalker4]

It turns out that the server where I am trying to call the web service only has the following encryption protocols enabled:

TLSv1.2:
| ciphers:
| TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384 (secp256r1) - A
| TLS_ECDHE_ECDSA_WITH_CHACHA20_POLY1305_SHA256 (secp256r1) - A
| TLS_ECDHE_ECDSA_WITH_AES_256_CCM_8 (secp256r1) - A
| TLS_ECDHE_ECDSA_WITH_AES_256_CCM (secp256r1) - A
| TLS_ECDHE_ECDSA_WITH_ARIA_256_GCM_SHA384 (secp256r1) - A
| TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256 (secp256r1) - A
| TLS_ECDHE_ECDSA_WITH_AES_128_CCM_8 (secp256r1) - A
| TLS_ECDHE_ECDSA_WITH_AES_128_CCM (secp256r1) - A
| TLS_ECDHE_ECDSA_WITH_ARIA_128_GCM_SHA256 (secp256r1) - A

According to the SAP SQL Anywhere 17.0.10.5866 documentation, these encryption protocols are not supported.

Or is it still possible to use them in SAP SQL Anywhere 17.0.10.5866 ?