I'm using 16.0.0.2798 with web service functions (with OTRS FWIW). This works well with HTTP. Now, when switching to HTTPS, I get the following error:

The secure connection to the remote host failed: TLS-Handshake failed, Error code 18 – SQLCODE=-990, ODBC 3 State="HY000"

However, the same function with the same certificate does work with 17.0.10.5923. Are there known differences w.r.t. to TLS between these versions?

The supplied certificate includes a self-signed root CA, it includes the whole chain from web server certificate to CA to root certificate, and the CA and root certificates both contain the "Certificate Signing" attribute. According to the following FAQ, that should be sufficient IMHO. (It does also work with v17 when the web server certificate is omitted.)

Is there something more I can test with v16? (The new certificate v17 options like "skip_certificate_name_check" are apparently not allowed with v16.)

(Unfortunately I cannot upgrade to v17 currently.)

asked 05 Nov '19, 12:02

Volker%20Barth's gravatar image

Volker Barth
40.0k361549819
accept rate: 34%

edited 06 Nov '19, 03:22

FWIW, it also fails with 17.0.8.4003 with the same error code with v16...

(05 Nov '19, 12:46) Volker Barth
Replies hidden

Just a further question:

According to the docs, v17.0.10.5745 has introduced the following change:

Encryption now uses the SAP Cryptographic Library (CommonCryptoLib)
In previous releases, SQL Anywhere provided a 64-bit FIPS compliant module for encryption from OpenSSL. The software now uses SAP CommonCryptoLib to provide this functionality.

Does that relate to FIPS-compliant encryption only, or to encryption in general? From the mentioned files, only sapcrypto.dll has been added to my install...

In other words: Does "non-FIPS" TLS encryption still use OpenSSL, or does it also use CommonCryptoLib?

(06 Nov '19, 03:15) Volker Barth

It's all CommonCryptoLib as of 17.0.10.

(Except for the MobiLink sync clients, where OpenSSL is still used, and this is related to supporting devices.)

(06 Nov '19, 10:07) Tim McClements

@Tim, as my tests fail with the OpenSSL based versions and succeed with the CommonCryptoLib based version, is there a known issue with OpenSSL and certain certificates you are aware of?

(06 Nov '19, 12:29) Volker Barth
1

I can't think of any known issues with certs and OpenSSL...

Before "skip_certificate_name_check" came along, the name wasn't checked unless you specified what to check against.

We should try to figure out what "error code 18" means.

One thing that has changed recently is Server Name Indication is now used for the handshake. This sends the expected hostname to the server so it can return the matching certificate. Maybe some servers require SNI now, or return the wrong cert without SNI? That could explain why only 17.0.10 works.

(06 Nov '19, 16:42) Tim McClements
Replies hidden

@Tim: A late reply: See my last comment under Graeme's answer, your hint to SNI was very helpful in the end...

or return the wrong cert without SNI

That nailed it pretty much, it was just difficult for me to find out that this was happending...:(

(19 Nov '19, 05:12) Volker Barth
1

I'm glad you got it! Good idea to use s_client...

Hm... enhancement request for our error to include some certificate info maybe? ;-)

(19 Nov '19, 14:20) Tim McClements

Don't know - on our part, we learnt to prevent Apache from using the default Ubuntu "snake-oil certificate" incidentally...:)

(19 Nov '19, 16:27) Volker Barth
showing 2 of 8 show all flat view

Error code 18 indicates that the server's certificate is self-signed but doesn't appear in the list of trusted certificates. I'm not sure that's actually what's happening but you did say that if you only specified the trusted root, then things work. That's the typical use case - server supplies the entire chain including the server's certificate and private key, and the client supplies only the root certificate. I don't know why OpenSSL is not accepting the connection but you have the solution - only specify the trusted root.

permanent link

answered 06 Nov '19, 19:28

Graeme%20Perrow's gravatar image

Graeme Perrow
9.6k379124
accept rate: 54%

you did say that if you only specified the trusted root, then things work

No, that's a misunderstanding. With v16 and v17.0.8.4003, it does not work no matter whether I include the web server's certificate (WS) or not. In all test cases I included the certificate of the intermediate CA (ICA) and the root CA (RCA), and the latter is self-signed.

In other words:

  • Certificate chain including WS, ICA, RCA - fails with 16 and 17.0.8, succeds with v17.0.10
  • Certificate chain including WS, ICA - fails with 16 and 17.0.8, succeeds with v17.0.10
  • Certificate chain including ICA, RCA - fails with 16 and 17.0.8, succeeds with v17.0.10
  • Certificate chain including WS, RCA - fails with 16 and 17.0.8, succeeds with v17.0.10
  • Certificate chain only including WS - fails with 16 and 17.0.8, succeeds with v17.0.10
  • Certificate chain only including ICA - fails with 16 and 17.0.8, succeeds with v17.0.10
  • Certificate chain only including RCA - fails with 16 and 17.0.8 and also fails with v17.0.10 ("The secure connection to the remote host failed: The connection was closed from the other side", no TLS error code)

Here's the (modified) viewcert output for the supplied certifite file (for the first entry), and the first certificate's common name is exactly the one used in the web client's URL clause:

SQL Anywhere X.509 Certificate Viewer Version 17.0.10.5923

X.509 Certificate
-----------------
Common Name:             www.[...]
Country Code:            ...
State/Province:          ...
Locality:                ...
Organization:            ...
Organizational Unit:     ...
Issuer:                  MY-CA
Serial Number:           ...
Issued:                  Nov 30, 2015  16:27:37
Expires:                 Nov 28, 2020  16:27:37
Signature Algorithm:     RSA, SHA256
Key Type:                RSA
Key Size:                4096 bits
Key Usage:               Digital Signature, Key Encipherment

X.509 Certificate
-----------------
Common Name:             MY-CA
Issuer:                  MY-ROOT-CA
Serial Number:           ...
Issued:                  Jul 4, 2015  11:42:53
Expires:                 Jul 4, 2030  11:52:53
Signature Algorithm:     RSA, SHA256
Key Type:                RSA
Key Size:                4096 bits
Basic Constraints:       Is a certificate authority, path length limit: 0
Key Usage:               Digital Signature, Certificate Signing, CRL Signing

X.509 Certificate
-----------------
Common Name:             MY-ROOT-CA
Issuer:                  MY-ROOT-CA
Serial Number:           ...
Issued:                  Jul 4, 2015  10:59:00
Expires:                 Jul 4, 2045  11:08:59
Signature Algorithm:     RSA, SHA256
Key Type:                RSA
Key Size:                4096 bits
Basic Constraints:       Is a certificate authority, path length limit: 0
Key Usage:               Digital Signature, Certificate Signing, CRL Signing
(07 Nov '19, 04:11) Volker Barth

Did you try just supplying the root certificate and not the ICA?

(07 Nov '19, 06:45) Graeme Perrow
Replies hidden

If I specify only the root certificate, the call fails in both versions:

  • 16.0.0.2798: same error as mentioned in the question
  • 17.0.10.5923: "The secure connection to the remote host failed: The connection was closed from the other side", SQLCODE=-990, ODBC 3 State="HY000"

I add more combinations in my first comment.

(07 Nov '19, 09:44) Volker Barth

FWIW, for all test cases, I used CREATE CERTIFICATE FROM FILE and supplied the cert via a clause like

   certificate 'cert_name=CERT_MY_ROOT_CA' 

For v17.0.10.5923, it also works with the 'FILE=*' clause because the ICA and RCA are stored in the machine's certificate store. Of course, v16 does not accept that and raises "The specified certificate is not valid - SQLCODE=-1588, ODBC 3 State="HY000""

(07 Nov '19, 10:09) Volker Barth

And one more test result:

I tried to be clever and thought of turning my web client functions into proxy functions (i.e. CREATE FUNCTION ... AT) that would call a SA17.0.10 "proxy database" that would just do the real web client function calls (because they do work). But of course, I missed that subtle detail:

A proxy function can return any data type except DECIMAL, NUMERIC, LONG VARCHAR, LONG NVARCHAR, LONG BINARY, XML, or any spatial data type.

As the web client function must handle XML datatypes, that is currently a no-go. Sigh.

(07 Nov '19, 10:45) Volker Barth

Ah, I should have thought about @Graeme's and @Tim's answers more thorough... Further tests via OpenSSL's s_client have revealed that this is not related to ciphersuites (as I had thought up-to-now) but to SNI:

When checking the connection with browsers, I always do get the expected certificate, so I was sure the certificate is configured correctly. (Of course modern browsers do support SNI.)

However, when testing with s_client against the same URL and varying server names, the web server in question does return different certificates, and one of them is a self-signed one (i.e. not signed by our usual root CA), and that is the one returned by default, i.e. without using SNI. (Apparently, that is a webserver misconfiguration.)

Now, when I use that particular certificate within the web client call, all is well...

Error code 18 indicates that the server's certificate is self-signed but doesn't appear in the list of trusted certificates.

So in the end, that error is truly correct, as I had supplied the root certificate of a different certificate chain.

In my understanding, 17.0.10 does cope with that as it uses SNI and therefore the web server returns the expeceted certificate.

(19 Nov '19, 05:09) Volker Barth
showing 5 of 6 show all flat view

@Graeme, could this be a restriction due to RSA ciphers?

I checked with another HTTPS web server certified by the same root CA, and I can connect to that one via v16 (as long as the supplied certificate chain does contain the root CA). According to the web browser, it uses TLS via RSA with AES 256-bit CBC, while the web server in question uses RSA with AES 128-bit GCM. So is there a chance the latter cipher suite is not supported in SQL Anywhere's OpenSSL feature?

permanent link

answered 08 Nov '19, 12:10

Volker%20Barth's gravatar image

Volker Barth
40.0k361549819
accept rate: 34%

edited 08 Nov '19, 12:39

2

AHA there's your answer. No, SQLA v16 does not support GCM, only CBC. Good catch!

(08 Nov '19, 12:33) Graeme Perrow
Replies hidden

Ah, thanks for the clarification! (Aside: I was really irritated because I had tested v16 some months ago against another web servers certified by the same root CA, and that worked as expected... The according web server seems to be configured differently, so I have to find out whether this can be adapted...)

(08 Nov '19, 12:38) Volker Barth
Comment Text Removed

Hm, the case is not yet solved, unfortunately, as the according Apache 2.4 web server certainly does support AES 256-bit CBC mode (particularly OpenSSL's ciphersuites ECDHE-RSA-AES256-SHA384 and ECDHE-RSA-AES256-SHA), as tested with openssl s_client. It just prefers GCM ciphersuites like ECDHE-RSA-AES256-GCM-SHA384.

While I can easily specify the preferred ciphersuites during an openssl s_connect call, I guess there's no chance to do so for the SQL Anywhere web client, right?

(13 Nov '19, 10:32) Volker Barth
Replies hidden

SQLA16 didn't support ECHDE ciphers either. The only ciphers supported in that version are TLS_RSA_WITH_AES_256_CBC_SHA, TLS_RSA_WITH_AES_128_CBC_SHA, and RC4-MD5 (except in FIPS mode). v17 increased the number and strength of the supported ciphers but you're right, there's no way to adjust the list.

(13 Nov '19, 10:51) Graeme Perrow

Ah, I see. But TLS 1.2 is supported, right?

(So I guess we have to adapt the web server's preferred CS ordering...)

(13 Nov '19, 10:57) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×260
×240
×48
×24

question asked: 05 Nov '19, 12:02

question was seen: 3,443 times

last updated: 19 Nov '19, 16:27