Troubleshooting Oracle One-way TLS Connection Errors

While working through several iterations of one-way Transport Layer Security (TLS) connection testing between Oracle Database client and server, I captured the troubleshooting techniques and possible reasons and remediation suggestions for the errors that I encountered.  This blog post catalogs those errors for my reference. 

Error: ORA-28759: failure to open file

Oracle Error Help: https://docs.oracle.com/error-help/db/ora-28759

Possible Reasons:

1. The client wallet exist does not exist. Copy client wallet from dbca to local client wallet directory (/u01/app/oracle/tls). See section 2.2.

2, The WALLET_LOCATION path in tnsnames.ora does not match the actual client wallet location. For example, following two paths should match:

Actual client wallet location: /u01/app/oracle/tls

The tnsnames.ora definition for HRDB_SSL:

HRDB_SSL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = hrdb.example.com)(PORT = 2484))
    (SECURITY=(WALLET_LOCATION=/u01/app/oracle/tls))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hrdb)
    )
  )

Error: ORA-12154: Cannot connect to database. Cannot find alias %s in %s.

Oracle Error Help: https://docs.oracle.com/error-help/db/ora-12154

Possible Reasons: Either service is not specified in tnsnames.ora or the wrong service name was used in sqlplus command.

Error: ORA-12547: TNS:lost contact

Oracle Error Help: https://docs.oracle.com/error-help/db/ora-12547

Possible Reasons: The PROTOCOL of tnsnames.ora definition is TCP instead of TCPS for SSL/TLS connection or vice versa for non-SSL/TLS connection.  For example, the PROTOCOL of the following tnsnames.ora should be TCPS rather than TCP:

HRDB_SSL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hrdb.example.com)(PORT = 2484))
    (SECURITY=(WALLET_LOCATION=/u01/app/oracle/tls))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hrdb)
    )
  )

Error: ORA-28862: SSL connection failed

Oracle Error Help: https://docs.oracle.com/error-help/db/ora-28862

Possible Reasons:

1. If your database implementation employs SHARED_SERVERS configuration with a specified number of dispatchers, you need to make sure that the dispatchers configuration includes TCPS connection in addition to the TCP connection.

ALTER SYSTEM SET  dispatchers=’ (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xx.example.com))(ADDRESS=(PROTOCOL=TCPS)(HOST=xx.example.com)))(DISPATCHERS=8)’;

Error: ORA-28864: SSL connection closed gracefully

Oracle Error Help: https://docs.oracle.com/error-help/db/ora-28864

Possible Reasons:

1. The PROTOCOL definition of the database server's listener.ora is set to TCP rather than TCPS. For example, changing the blue TCP to TCPS and restarting the Oracle database listener should resolve this error.

cat $ORACLE_HOME/network/admin/listener.ora
SSL_CLIENT_AUTHENTICATION = FALSE

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hrdb.example.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = hrdb.example.com)(PORT = 2484))
      (SECURITY=(WALLET_LOCATION=/u01/app/oracle/19c/wallet_root/tls))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle/19c

Error: Segmentation fault (core dumped)

Possible Reasons:

1. The TCPS address has the wrong port. Change to the correct port (2484).

cat $ORACLE_HOME/network/admin/listener.ora
SSL_CLIENT_AUTHENTICATION = FALSE

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hrdb.example.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = hrdb.example.com)(PORT = 9999))
      (SECURITY=(WALLET_LOCATION=/u01/app/oracle/19c/wallet_root/tls))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle/19c

2. The TCPS address has the wrong address. Change to the correct port.

cat $ORACLE_HOME/network/admin/listener.ora
SSL_CLIENT_AUTHENTICATION = FALSE

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hrdb.example.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = wronghost.example.com)(PORT = 2484))
      (SECURITY=(WALLET_LOCATION=/u01/app/oracle/19c/wallet_root/tls))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle/19c

Error: ORA-28860: Fatal SSL error

Oracle Error Help: https://docs.oracle.com/error-help/db/ora-28860

Possible Reasons:

1. SSL_CLIENT_AUTHENTICATION setting in the database client or server sqlnet.ora may be set to TRUE instead of FALSE in the Oracle database server listener.ora.   Wireshark/tshark analysis of the secure port (2484) while sqlplus connect is run.  From the following output, we see the failed connection:

sudo yum install -y wireshark

sudo tshark -i any -d "tcp.port==2484,ssl" -V -a duration:20 2> /dev/null | egrep "Cipher Suites \(|Cipher Suite:|^            Version: |SSL Record Layer: Handshake Protocol: Client Hello|Handshake Protocol: Server Hello|Record Layer"| uniq | sed -e "s/(0x.*)//g" -e "s/.*SSL Record Layer: Handshake Protocol: Client Hello/Client requested:/g" -e "s/.*Version:/   Protocol Version:/g" -e "s/.*Cipher Suites /   Cipher Suites Requested:/g" -e "s/.*Handshake Protocol: Server Hello/Server replied with:/g" |egrep -v "Server replied with: Done" &
[1] 91106


sqlplus system/Oracle123@hrdb_ssl

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 6 10:54:52 2024
Version 19.25.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

ERROR:
ORA-28860: Fatal SSL error


Enter user-name: ^C
    TLSv1.2 Record Layer: Handshake Protocol: Client Hello
   Protocol Version: TLS 1.2 
   Cipher Suites Requested:(25 suites)
                Cipher Suite: TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 
                Cipher Suite: TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384 
                Cipher Suite: TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA 
                Cipher Suite: TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 
                Cipher Suite: TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256 
                Cipher Suite: TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA 
                Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384 
                Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384 
                Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA 
                Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256 
                Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256 
                Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA 
                Cipher Suite: TLS_RSA_WITH_AES_256_GCM_SHA384 
                Cipher Suite: TLS_RSA_WITH_AES_256_CBC_SHA256 
                Cipher Suite: TLS_RSA_WITH_AES_256_CBC_SHA 
                Cipher Suite: TLS_RSA_WITH_AES_128_GCM_SHA256 
                Cipher Suite: TLS_RSA_WITH_AES_128_CBC_SHA256 
                Cipher Suite: TLS_RSA_WITH_AES_128_CBC_SHA 
                Cipher Suite: TLS_DHE_RSA_WITH_AES_256_GCM_SHA384 
                Cipher Suite: TLS_DHE_RSA_WITH_AES_128_GCM_SHA256 
                Cipher Suite: TLS_DHE_RSA_WITH_AES_256_CBC_SHA256 
                Cipher Suite: TLS_DHE_RSA_WITH_AES_128_CBC_SHA256 
                Cipher Suite: TLS_DHE_RSA_WITH_AES_256_CBC_SHA 
                Cipher Suite: TLS_DHE_RSA_WITH_AES_128_CBC_SHA 
                Cipher Suite: TLS_EMPTY_RENEGOTIATION_INFO_SCSV 
Server replied with:
Server replied with:
   Protocol Version: TLS 1.2 
            Cipher Suite: TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 
    TLSv1.2 Record Layer: Handshake Protocol: Certificate
    TLSv1.2 Record Layer: Handshake Protocol: Server Key Exchange
    TLSv1.2 Record Layer: Handshake Protocol: Multiple Handshake Messages
    TLSv1.2 Record Layer: Handshake Protocol: Certificate
    TLSv1.2 Record Layer: Handshake Protocol: Client Key Exchange
    TLSv1.2 Record Layer: Change Cipher Spec Protocol: Change Cipher Spec
    TLSv1.2 Record Layer: Handshake Protocol: Encrypted Handshake Message
    TLSv1.2 Record Layer: Alert (Level: Fatal, Description: Handshake Failure)
    TLSv1 Record Layer: Handshake Protocol: Client Hello
   Protocol Version: TLS 1.2 
   Cipher Suites Requested:(6 suites)
                Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256 
                Cipher Suite: TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 
                Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384 
                Cipher Suite: TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 
                Cipher Suite: TLS_RSA_WITH_AES_128_GCM_SHA256 
                Cipher Suite: TLS_RSA_WITH_AES_256_GCM_SHA384 
    TLSv1.2 Record Layer: Handshake Protocol: Multiple Handshake Messages
Server replied with:
   Protocol Version: TLS 1.2 
            Cipher Suite: TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 
    TLSv1.2 Record Layer: Handshake Protocol: Client Key Exchange
    TLSv1.2 Record Layer: Change Cipher Spec Protocol: Change Cipher Spec
    TLSv1.2 Record Layer: Handshake Protocol: Encrypted Handshake Message
    TLSv1.2 Record Layer: Change Cipher Spec Protocol: Change Cipher Spec
    TLSv1.2 Record Layer: Handshake Protocol: Encrypted Handshake Message
    TLSv1.2 Record Layer: Application Data Protocol: http-over-tls

2. The cryptographic cipher suite does not match in the $ORACLE_HOME/network/admin/sqlnet.ora of the database client and server.  For example, the SSL_CIPHER_SUITES in the following slqnet.ora on both client and server should iniclude TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 but one does not:

cat $ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ)
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 1.2
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)

The wireshark/tshark analysis of this connection reveals that the client and server could not agree on the terms of the cryptographic handshake.

sudo tshark -i any -d "tcp.port==2484,ssl" -V -a duration:5 2> /dev/null | egrep "Cipher Suites \(|Cipher Suite:|^            Version: |SSL Record Layer: Handshake Protocol: Client Hello|Handshake Protocol: Server Hello|Record Layer"| uniq | sed -e "s/(0x.*)//g" -e "s/.*SSL Record Layer: Handshake Protocol: Client Hello/Client requested:/g" -e "s/.*Version:/   Protocol Version:/g" -e "s/.*Cipher Suites /   Cipher Suites Requested:/g" -e "s/.*Handshake Protocol: Server Hello/Server replied with:/g" |egrep -v "Server replied with: Done" &
[1] 92079

sqlplus system/Oracle123@hrdb_ssl

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 6 11:16:21 2024
Version 19.25.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

ERROR:
ORA-28860: Fatal SSL error


Enter user-name: ^C
    TLSv1.2 Record Layer: Handshake Protocol: Client Hello
   Protocol Version: TLS 1.2 
   Cipher Suites Requested:(2 suites)
                Cipher Suite: TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 
                Cipher Suite: TLS_EMPTY_RENEGOTIATION_INFO_SCSV 
Server replied with:
Server replied with:
   Protocol Version: TLS 1.2 
            Cipher Suite: TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 
    TLSv1.2 Record Layer: Handshake Protocol: Certificate
    TLSv1.2 Record Layer: Handshake Protocol: Server Key Exchange
    TLSv1.2 Record Layer: Handshake Protocol: Client Key Exchange
    TLSv1.2 Record Layer: Change Cipher Spec Protocol: Change Cipher Spec
    TLSv1.2 Record Layer: Handshake Protocol: Encrypted Handshake Message
    TLSv1.2 Record Layer: Change Cipher Spec Protocol: Change Cipher Spec
    TLSv1.2 Record Layer: Handshake Protocol: Encrypted Handshake Message
    TLSv1.2 Record Layer: Application Data Protocol: Application Data
    TLSv1.2 Record Layer: Handshake Protocol: Encrypted Handshake Message
    TLSv1.2 Record Layer: Alert (Level: Fatal, Description: Handshake Failure)

3. The SSL_VERSION setting in the sqlnet.ora of the database client and server do not match.  For example, if SSL_VERSION is set to 1 rather than 1.2.  You would get very similar wireshark/tshark result as the previous example.

Error: ORA-12560: Database communication protocol error.

Oracle Error Help: https://docs.oracle.com/error-help/db/ora-12560

Possible Reasons: The SSL_VERSION setting in the sqlnet.ora of the database client and server do not match.  For example, if SSL_VERSION of the client sqlnet.ora is set to 1.3 when the server sqlnet.ora is set to 1.2.

Error: ORA-28759: failure to open file

Oracle Error Help: https://docs.oracle.com/error-help/db/ora-28759

Possible Reasons: The WALLET_LOCATION of the connect string or tns entry or the sqlnet.ora does not match the actual location of the wallet.

Error: ORA-28865: SSL connection has closed

Oracle Error Help: https://docs.oracle.com/error-help/db/ora-28865

Possible Reasons: If you see this error when connecting securely to a PDB, this means that the wallet has not yet been copied to PDB wallet_root/tls directory. For example:

mkdir -p $WALLET_ROOT/26471EC7099D0530E0635D00000A3BC3/tls
cp $WALLET_ROOT/tls/ewallet.p12 $WALLET_ROOT/26471EC7099D0530E0635D00000A3BC3/tls
cp $WALLET_ROOT/tls/cwallet.sso $WALLET_ROOT/26471EC7099D0530E0635D00000A3BC3/tls

Additional information on Oracle database support of TLS is available at Oracle Support Document 2980134.1:

FAQ: TCPS / TLS / SSL / UTL_HTTP / UTL_SMTP Configuration and Queries in Oracle Database

https://support.oracle.com/epmos/faces/DocumentDisplay?id=2980134.1

Error: ORA-29024: Certificate validation failure

Oracle Error Help: https://docs.oracle.com/error-help/db/ora-29024

Possible Reasons: 

1. The certificate of the database listener does not match the certificate specified by SSL_SERVER_CERT_DN of the client connect string or tns entry. For example:

Presume that the SSL_SERVER_CERT_DN value is scanvip.dbauthdemo.com. However, the certificate subject is reported to be hrdb.dbauthdemo.com as seen by openssl s_client:

$ echo | openssl s_client -connect hrdb.dbauthdemo.com:2484 2>&1 | openssl x509 -noout -text | egrep -i "DNS|subject"
        Subject: CN=hrdb.dbauthdemo.com
        Subject Public Key Info:
            X509v3 Subject Alternative Name: 
                DNS:hrdb.dbauthdemo.com, DNS:www.hrdb.dbauthdemo.com
            X509v3 Subject Key Identifier:

2. The certificate signing authority does not match certificate chain of the client wallet.