Best practices for successful SSL connections to Amazon RDS for Oracle

In this post, we show you how to successfully set up SSL connectivity with Amazon Relational Database Service (Amazon RDS) for Oracle. For the purpose of this post, we have considered scenarios of SSL connectivity with the source as a SQL Plus client over a Linux platform and also a Java application client.

SSL connectivity with Amazon RDS for Oracle

Secure Sockets Layer (SSL) is an industry-standard protocol for securing network connections between client and server. After SSL version 3.0, the name was changed to Transport Layer Security (TLS), but the protocol is still often called SSL. Amazon RDS supports SSL encryption for Oracle DB instances in all AWS Regions.

To enable SSL encryption for an Oracle DB instance, add the Oracle SSL option to the option group associated with the DB instance. Amazon RDS uses an additional port, as required by Oracle, for SSL connections. Doing this allows clear text and SSL-encrypted communication to occur at the same time between a DB instance and an Oracle client. For example, you can use the port with clear text communication to communicate with other resources inside a VPC while using the port with SSL-encrypted communication to communicate with resources outside the VPC. To lean more, refer to Oracle Secure Sockets Layer.

Implementing SSL in a database environment typically involves configuring the database server to support SSL connections and configuring client applications to use SSL when connecting to the database. SSL certificates, which contain the cryptographic keys and identity information, are used to facilitate secure communication between client and server.

Before deploying the new certificate on your DB instances, it’s important to update the client’s trust store or wallet of any clients using SSL/TLS and the server certificate for connections. There isn’t a straightforward method available from your DB instances to ascertain if your applications require certificate verification before connecting. Your only recourse is to examine your applications’ source code or configuration files.

For more information about what to search for in most common database connectivity interfaces, refer to Updating your CA certificate by modifying your DB instance or cluster. It’s highly recommended to collaborate with your application developers to ascertain whether certificate verification is employed and the appropriate method to update the SSL/TLS certificates for your particular applications.

You can download the certificates from Certificate bundles for all AWS Regions to provision a smooth application upgrade and uninterrupted connectivity during the transition period.

AWS offers two types of certificate bundles:

• Global bundle – This will have existing roots and intermediates, plus the new root certificates for Regions in the partition. Global bundle certificates are ideal for applications and services that require broad compatibility and trust.
• Regional bundle – This is a bundle per Region, and contains the existing root certificate authority (CA) for the Region, existing intermediate certificate, and new root certificates. Regional bundle certificates are commonly employed in scenarios prioritizing localized trust or mandated by regulatory frameworks.

Create the wallet and add a certificate for Linux clients

You need to create an Oracle wallet when connecting to an Oracle instance using SSL because it allows for secure authentication and encryption of data exchanged between the client and the server. The wallet contains the digital certificates and keys necessary for establishing a secure connection, verifying that sensitive information remains protected from unauthorized access during transmission.

To use Amazon RDS for Oracle SSL connectivity, you need to create the Oracle wallet using the orapki utility, and then load the SSL/TLS endpoint root and intermediate certificates.

The orapki utility is available when you install the Oracle client software. You must create the wallet in the source database environment.

For this example, we use auto_login_only to create an auto login wallet (cwallet.sso) that doesn’t need a password to open. Complete the following steps:

1. Set the WALLET location you can use for export:

   export WALLET=<YOUR-ORACLE-WALLET-LOCATION-HERE>

2. Create the Oracle SSL wallet:

   orapki wallet create -wallet $WALLET -auto_login_only
   
   Oracle PKI Tool Release 19.0.0.0.0 - Production
   Version 19.3.0.0.0
   Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.
   Operation is successfully completed

3. Use the following curl command to download the global-bundle.pem file:

   curl -sS "https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem" > global-bundle.pem

The orapki function can only import the initial certificate from a PEM bundle file. Consequently, a bundle consisting of multiple PEM files won’t import properly into the wallet. To enable SSL connections for either of the certificates (rds-ca-rsa2048-g1 or rds-ca-rsa4096-g1), you must separate individual roots from the Region-specific PEM files. For more information, refer to How can I use an SSL connection to connect to my Amazon RDS for Oracle DB instance?

4. Split the certificates from bundle.pem:

   awk 'split_after == 1 {n++;split_after=0} /-----END CERTIFICATE-----/ {split_after=1}{print > "rds-ca-" n ".pem"}' < global-bundle.pem

5. Add all the rds-ca-* certificates into the wallet, which are created after the split:

   for CERT in rds-ca-*;
   do
   alias=$(openssl x509 -noout -text -in $CERT | perl -ne 'next unless /Subject:/; s/.(CN=|CN = )//; print')
   echo "Importing $alias"
   orapki wallet add -wallet $WALLET -trusted_cert -cert ${CERT} -auto_login_only
   rm $CERT
   done

The preceding procedure adds all the rds-ca-* certificates from the global-bundle.pem file. Alternatively, you can use the following procedure, which doesn’t add all the rds-ca-* certificates, but only fetches and adds certificates to the wallet that is associated with your RDS endpoint:

export RDS_ENDPOINT=<YOUR-RDS-ENDPOINT-HERE>
export SSL_PORT=<SSL-PORT-HERE>

for CERT in rds-ca-*;
do alias=$(openssl x509 -noout -text -in $CERT | perl -ne 'next unless /Subject:/; s/.(CN=|CN = )//; print')
echo "$alias" | grep "$(openssl s_client -connect $RDS_ENDPOINT:$SSL_PORT -showcerts </dev/null 2>/dev/null | grep s:| grep Root| awk -F"CN=|CN[[:space:]]*=[[:space:]]*" '{gsub("/", ", ",$2); print $2}')" > /dev/null
if [ $? -eq 0 ] ;
then
orapki wallet add -wallet $WALLET -trusted_cert -cert $CERT -auto_login_only
else
rm $CERT
fi
done

6. Review the wallet after the certificates are added:

   orapki wallet display -wallet $WALLET
   
   Oracle PKI Tool Release 19.0.0.0.0 - Production
   Version 19.3.0.0.
   Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.
   
   Requested Certificates:
   User Certificates:
   Trusted Certificates:
   Subject: L=Seattle,CN=Amazon RDS us-east-1 Root CA RSA4096 G1,ST=WA,OU=Amazon RDS,O=Amazon Web Services\, Inc.,C=US

Update the trust store and import certificates with a Java application

You need to update your applications to connect to Oracle DB instances using SSL/TLS certificates in order to establish SSL connectivity between the application and an Oracle database. By doing so, it verifies that the application trusts the SSL certificate presented by the Oracle database server. The trust store contains trusted certificates from CAs that the application will use to verify the authenticity of the server’s SSL certificate during the SSL handshake process. By updating the trust store with the CA’s certificate or the server’s SSL certificate, the application can verify the identity of the Oracle database server and establish a secure SSL connection without throwing certificate validation errors.

1. To access the keytool export for JAVA_HOME and PATH, use the following code:

   export JAVA_HOME=/usr/lib/jvm/jdk1.8.0_301
   export PATH=$PATH:$JAVA_HOME/bin
   export storepassword=<KEY-STORE-PASSWORD-HERE>

2. Use the following curl command to download global-bundle.pem file:

   curl -sS "https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem" > global-bundle.pem

3. Split the bundle .pem file and import the certificate into keytool:

   awk 'split_after == 1 {n++;split_after=0} /-----END CERTIFICATE-----/ {split_after=1}{print > "rds-ca-" n+1 ".pem"}' < global-bundle.pem

4. Add all the rds-ca-* certificates created after the preceding operation into keytool:

   for CERT in rds-ca-*;
   do alias=$(openssl x509 -noout -text -in $CERT | perl -ne 'next unless /Subject:/; s/.(CN=|CN = )//; print')
   echo "Importing $alias"
   keytool -import -file ${CERT} -alias "${alias}" -storepass ${storepassword} -keystore clientkeystore.jks -noprompt
   rm $CERT
   done

The preceding procedure adds all the rds-ca-* certificates from the global-bundle.pem file. Alternatively, you can use the following procedure, which doesn’t add all the rds-ca-* certificates, but only fetches and adds certificates to the wallet that is associated with your own RDS endpoint:

export RDS_ENDPOINT=<YOUR-RDS-ENDPOINT-HERE>
export SSL_PORT=<SSL-PORT-HERE>

for CERT in rds-ca-*;
do alias=$(openssl x509 -noout -text -in $CERT | perl -ne 'next unless /Subject:/; s/.(CN=|CN = )//; print')
echo "$alias" | grep "$(openssl s_client -connect $RDS_ENDPOINT:$SSL_PORT -showcerts </dev/null 2>/dev/null | grep s:| grep Root| awk -F"CN=|CN[[:space:]]*=[[:space:]]*" '{gsub("/", ", ",$2); print $2}')" > /dev/null
if [ $? -eq 0 ] ;
then
keytool -import -file ${CERT} -alias "${alias}" -storepass ${storepassword} -keystore clientkeystore.jks -noprompt
else
rm $CERT
fi
done

5. Use the following code to list the keystore:

   keytool -list -v -keystore clientkeystore.jks -storepass $storepassword | grep -E "Alias name|Valid from:|until:"

Compatibility between the JDBC driver and the JDK version

Although we use SSL when making a JDBC connection, the version compatibility of JDK (Java version) and Oracle JDBC drivers matters too. The following table is a compatibility matrix.

Refer to What are the Oracle JDBC releases Vs JDK versions for the latest compatibility matrix.

Incompatibility examples

• OJDBC7.jar with JDK7 is incompatible with 19.x. If you try to use this combination to connect with Amazon RDS for Oracle 19c DB, you may see the following error:

“IO Error: The Network Adapter could not establish the connection"

This error will be followed by the cause:

Caused by: java.io.IOException: Invalid keystore format

Although OJDBC8.jar is compatible with JDK8, you may see the same error due to an Oracle issue documented in Doc ID 2847060.1.

To correct this, generate the keystore in Java 8u301 and above.

• Additionally, using OJDBC7.jar is incompatible with JDK11 and may result in the following error:

Caused by: javax.net.ssl.SSLHandshakeException: No appropriate protocol (protocol is disabled or cipher suites are inappropriate)

Best practices to avoid connections between unrelated certificates

We recommend using the latest Oracle client versions due to their enhancements. These enhancements provide proper certificate validations and require the client to have a valid signing certificate. This will rule out the possibility of successful SSL connection with incorrect certificates in the source Oracle client wallet.

One such scenario is also discussed in the Oracle documentation Doc ID 2708154.1.

In summary, it is recommended to always use the latest database client version available and the latest patch over your RDS for Oracle instance.

Set client SQLNET.ora parameters and TNSNAMES.ora

The sqlnet.ora file is the profile configuration file. It resides on the client machines and the database server. Profiles are stored and implemented using this file. The client can be configured with access control parameters in the sqlnet.ora file. These parameters specify whether clients are allowed or denied access based on the protocol.

By default, the sqlnet.ora file is located in the ORACLE_HOME/network/admin directory. The sqlnet.ora file can also be stored in the directory specified by the TNS_ADMIN environment variable.

The following is a sample SQLNET.ora file for SSL client authentication:

WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = <WALLET_LOCATION>)))
SSL_CLIENT_AUTHENTICATION = TRUE
SSL_VERSION = 1.2
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA)
SSL_SERVER_DN_MATCH = ON

The file uses the following parameters:

• The SSL_CLIENT_AUTHENTICATION parameter controls whether the client is authenticated using TLS.
• The SSL_VERSION parameter defines the version of TLS that must run on the systems with which the server communicates.
• When you install Oracle Database, the TLS cipher suites are set for you by default. This table lists them in the order they are tried when two entities are negotiating a connection. You can override the default by setting the SSL_CIPHER_SUITES parameter.
• The SSL_SERVER_DN_MATCH parameter is used to enforce server-side certificate validation through distinguished name (DN) matching.

SSL_VERSION and SSL_CIPHER_SUITES should be the same as the RDS SSL option setting in the option group. For more information, refer to Oracle Secure Sockets Layer.

The following is a sample TNSNAMES.ora file when SSL_SERVER_DN_MATCH is set to ON:

net_service_name =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCPS)
(HOST = <RDS_ENDPOINT>)
(PORT = SSL_PORT)
)
)
(CONNECT_DATA =
(SID = ORCL)
)
(SECURITY =
(SSL_SERVER_CERT_DN = "C=US,ST=Washington,L=Seattle,O=Amazon.com,OU=RDS,CN=endpoint")
)
)

You can use the following query to validate if the connection is using TCPS. Connect via SQL Plus using TCPS protocol:

sqlplus username/password@net_service_name

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Mar 17 08:33:06 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Sun Mar 17 2024 08:15:06 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0

SQL> SELECT SYS_CONTEXT('USERENV', 'network_protocol') FROM DUAL;

SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
---
tcps

Conclusion

In this post, we showed you how to make a successful SSL connection to an RDS for Oracle instance with SSL enabled, and discussed best practices and suggestions. We also covered the most common things to consider in real-world use cases when using a client as a Java application or using a SQL Plus client on Linux/Windows platforms. We also explained the sqlnet.ora parameters required for successful SSL connectivity and provided sample file content.

Your feedback is greatly appreciated. Should you have any questions or recommendations, share them in the comments section.

About the Authors

Chandan Maheshwari is a Cloud Support Engineer at AWS, with 8.5 years’ experience in various RDBMS engines, mainly Oracle. Chandan focuses on Amazon RDS, assisting customers with migrations, upgrades, and simplifying their cloud database journey. He’s also adept in database design, performance tuning, and automations, enriching AWS articles and blog posts with his expertise.

Tushar Rajput is a Cloud Support Engineer at AWS. Tushar brings 6 years of invaluable experience working with Oracle and various RDBMS engines. In his role, he collaborates closely with Amazon RDS customers, empowering them to establish and maintain resilient, reliable, and secure database operations on Amazon RDS. Beyond his professional pursuits, Tushar finds joy in trekking, traveling, and indulging in the thrill of go-karting.