Question Re: Unable to logon to ASE with Interactive SQL using SSL in Technology Q&A

Unable to logon to ASE with Interactive SQL using SSL

elovelo — Thu, 30 Jan 2025 18:21:21 GMT

Hello everyone. I am struggling to connect to my ASE instance via Interactive SQL using SSL encryption.

For the client application I use Sybase SDK 20_0 on Win Server 2012. The version of the Interactive SQL is 16.0.0 build 2909.

My ASE instance is of 16.0 SP02 PL07 running on AIX 7.2.

I have everything set up. That is:

1. sp_configure 'enable ssl',1

2. the master and query lines in interfaces file contain the filter ssl="CN='$DSQUERY'"

3. Have self-signed client certficate in $SYBASE/$SYBASE_ASE/certficates/$DSQUERY.txt

4. Have the root.crt certificate appended to $SYBASE/config/trusted.txt

5. Have the self-signed server certificate in Have the required self-signed certficate in $SYBASE/$SYBASE_ASE/certficates/$DSQUERY.crt

6. Have the $DSQUERY.crt certificate added with sp_ssladmin 'addcert'

7. have the protocol set with sp_ssladmin 'setprotocol' to 'TLS1.2'

8. Have the ciphersuite set with sp_ssladmin 'setcipher' to 'Strong'

9. Overwritten the Interactive SQL trusted file in %SYBASE%\ini\trusted.txt with $SYBASE/config/trusted.txt from ASE's host machine

10. Imported the client $DSQUERY.txt certficate to java's keystore located in SDK's folder %SYBASE%\Shared\SAPJRE-8_1_076_64BIT\lib\security using keytool as below:

keytool -importcert -alias "<asename>" -trustcacerts -noprompt -file %SYBASE%\ini\<asename>.txt -keystore %SYBASE%\Shared\SAPJRE-8_1_076_64BIT\lib\security\cacerts -storepass changeit

11. Removed all certificates with elliptic curves encryption algorithms from the keystore like SHA256withECDSA and SHA384withECDSA:

"thawteprimaryrootcag2"
"geotrustprimarycag2"
"verisignclass3_g4"
"usertrusteccca [jdk]"
"comodoeccca [jdk]"
"globalsigneccrootcar4"
"digicertglobalrootg3 [jdk]"
"digicertassuredidg3 [jdk]"
"globalsigneccrootcar5"
"entrustrootcaec1 [jdk]"
"affirmtrustpremiumeccca [jdk]"
"sslrooteccca [jdk]"
"amazonrootca4 [jdk]"
"amazonrootca3 [jdk]"
"haricaeccrootca2015 [jdk]"

Basically for the certifacate generation I followed these SAP Notes:

https://me.sap.com/notes/2593925
https://me.sap.com/notes/1899365

The result is that I am able to logon from the ASE host machine via SSL using isql command line client targeting the hostmachine with its network name (not localhost), e.g.

isql -U <user> -P <password> -S '<hostname> <port> ssl="CN=$DSQUERY"'

the @@ssl_cihersuire variable gives me back ' TLS_RSA_WITH_AES_256_GCM_SHA384' which means that the connection is SSL-encrypted.

On the remote Windows server, where I have the Interactive SQL installed , I am also able to logon with SSL via isql command line client by simply ASE name in the -S flag, not the host name followed by port and ssl filter as in above isql example from AIX.

On the Interactive SQL logon page I have the ASE name, host name and port selected correctly, the SSL checkbox next to port number field is also checked (the master and query lines in sql.ini file aslo have the proper ssl filter. But upon logon I get :

Connection refused. Ensure that the name in the 'Server name' file is correct and that the server has been started.

Here is the error:

JZ006: Caught IOException: java.io.IOException: JZ0T3 use getCause() to see the error chain
SQLCODE=0
SQLSTATE=JZ006
JZ0T3: Read operation timed out.
SQLCODE=0
SQLSTATE=JZ0T3

Connection parameters:
User=<user>
Password=<pasword>
ENG=ASEname

Here is the connection string:

Attempted to connect using:
HOSTNAME=<hostname>;JCONNECT_VERSION=0;USER=<user>;SERVICEFILTER=ssl;ENABLE_SSL=true;RETRY_WITH_NO_ENCRYPTION=true;ENCRYPT_PASSWORD=true;SERVERNAME=<ASEname>;PWD=***;<HOST>=<host_name>;<PORT>=<port_number>
JZ006: Caught IOException: java.io.IOException: JZ0T3 use getCause() to see the error chain

Note that I have masked the hostname, user, host and port fields obviously.

What am I doing wrong, what am I missing. Thank you in advance for any help !

Re: Unable to logon to ASE with Interactive SQL using SSL

ryan_hansen — Fri, 31 Jan 2025 14:37:42 GMT

Hi,

Java applications like DBISQL / Interactive SQL use the keystore to login with ssl.

isql uses the trusted.txt file as you pointed out.

If isql works then the trusted.txt file has the correct ssl certificates.

For java you import the ssl certificate into the keystore, but this command only imports the top certificate with keytool.
I'm guessing that the ssl certificate may not be at the top or only part of the ssl chain was imported into the keystore.
Try to seperate the ssl certificates into files and the import them into the keystore.

This is easier to debug with ASE 16.0 SP03+ as it uses the SAP Common Crypto Libraries (CCL).
KBA# 2743343
https://me.sap.com/notes/2743343

You may be able to turn on the ASE traceflags for more information though.

7825	Verbose SSL activity, including packet dumps.
7826	Trace memory alloc/free for SSL. Verbose.
7827	Trace entry/exit for SSL.
7828	Verbose HBC.
7829	Trace certificate authentication for SSL.

1> dbcc traceon(7829)
These output to a cmd prompt/log file

3604	output traceprint to network
3605	output traceprint to errorlog

Also, this syntax works on windows with backslashes
isql -U <user> -P <password> -S "<hostname> <port> ssl=\"CN=ASECommonName\""

Regards,
Ryan

Re: Unable to logon to ASE with Interactive SQL using SSL

ChrisBaker — Fri, 31 Jan 2025 14:58:43 GMT

ASE 16.0 SP02PL07 is over 7 years old. It used a cryptographic library that was replaced in SP03 and higher. This may account for the fact that you could connect with the native isql, but not with Windows.

There are also many security fixes.

The other issue is that SDK 20 should not be used. It is actually older than current SDK 16.0 releases, despite the version numbering and uses DBISQL 16.0, not the current DBISQL 17. Despite the difference in release number, I would recommend the following:

Upgrade your ASE instance to a newer version - SP03 PL15 or SP04PL06 HF1.
Upgrade your Windows SDK installation to a newer version of 16.0 SDK.

Try your connectivity tests again.

Chris

Re: Unable to logon to ASE with Interactive SQL using SSL

elovelo — Fri, 31 Jan 2025 16:30:16 GMT

Hello Ryan and thank you ! You were right about the ssl client certifcate ($DSQUERY.txt) designated for the import to the java keystore. It not only contained the client certificate that I created but also other certificates that my ASE uses to authenticate users via AD/LDAP. And indeed, that client certificate was appended to the bottom of the file (obviously). I extracted it into a separate file and imported only this file to the keystore. No I am able to logon with SSL through Interactive SQL 🙂

I was able to check which certificate in the chain was the client certificate by running this command on the $DSQUERY.txt file

f=$SYBASE/$SYBASE_ASE/certificates/$DSQUERY.txt i=`grep -c 'BEGIN CERTIFICATE' $f` while [ $i -gt 0 ]; do awk "BEGIN{nr=$i;i=0}"'/BEGIN CERTIFICATE/{nr--;i=1}nr==0&&i==1{print $0}/END CERTIFICATE/{i=0}' $f | \ $SAP_JRE8/bin/keytool -printcert | head -n 10 i=$((i - 1)) # Correct way to decrement in ksh done

I sourced this command from the SSL troubleshooting note 3152010 - https://me.sap.com/notes/3152010/E

Note: you may need to replace the 'let i--' with '$((i - 1))' for it to work