How can I use an SSL connection to connect to my Amazon RDS for Oracle DB instance?

8 minute read
0

I want to use a Secure Socket Layer (SSL) connection to connect to my Amazon Relational Database Service (Amazon RDS) for Oracle DB instance.

Short description

When you provision a DB instance, Amazon RDS creates an SSL certificate, and then installs the certificate on the instance. These certificates are signed by a certificate authority. The SSL certificate includes the DB instance endpoint as the Common Name for the SSL certificate. This protects the instance against spoofing attacks. The SSL certificate created by Amazon RDS is the trusted root entity, and works for most common use cases.

Amazon RDS for Oracle supports Transport Layer Security (TLS) versions 1.0 and 1.2. To use the Oracle SSL option, use the SQLNET.SSL_VERSION option setting in your option group. The following values are allowed for this option setting:

  • 1.0 - Clients can use TLS 1.0 only to connect to the DB instance.
  • 1.2 - Clients can use TLS 1.2 only to connect to the DB instance.
  • 1.2 or 1.0 - Clients can use either TLS 1.0 or 1.2 to connect to the DB instance.

For existing Oracle SSL options, SQLNET.SSL_VERSION is set to 1.0" by default. You can change this setting, if necessary.

Resolution

Limitations

You can use either SSL or Native Network Encryption (NNE) on the same RDS for Oracle DB instance, but not both. If you use SSL encryption, then make sure to turn off any other connection encryption. For more information, see Oracle native network encryption.

Add the SSL option to an option group

Add the SSL option in your customer option group to turn on SSL mode for your RDS for Oracle DB instances. Use the following steps to turn on SSL mode:

  1. Create a new option group or choose an existing option group to add the SSL option to.
  2. Add the SSL option to the option group. If you want to use FIPS-verified cipher suites for SSL connections only, then set the option FIPS.SSLFIPS_140 to TRUE.
  3. Create a new RDS for Oracle DB instance and associate the option group with it. Or modify an RDS for Oracle DB instance to associate the option group with it.

Use orapki to set up a wallet in your client

On your client, set the ORACLE_HOME environment variable to the location of your Oracle home directory.

  1. Navigate to the client's directory:

    $ cd app/client/product/19.0.0/client_1
  2. Confirm the path:

    [client_1]$ pwd
    /home/ec2-user/app/client/product/19.0.0/client_1
  3. Set the path for Oracle:

    [client_1]$ export ORACLE_HOME=/home/ec2-user/app/client/product/19.0.0/client_1
  4. Append &ORACLE_HOME/lib to the LD_LIBRARY_PATH environment variable:

    [client_1]$ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
  5. Create the wallet directory:

    [client_1]$ mkdir $ORACLE_HOME/ssl_wallet
  6. Navigate to the newly created directory:

    [client_1]$ cd ssl_wallet
  7. Download the certificate bundle:

    [ssl_wallet]$ wget https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem
    --2023-08-15 15:20:05-- https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem
    Resolving http://truststore.pki.rds.amazonaws.com (http://truststore.pki.rds.amazonaws.com)... 18.165.98.84, 18.165.98.93, 18.165.98.125, ...
    Connecting to http://truststore.pki.rds.amazonaws.com (http://truststore.pki.rds.amazonaws.com)|18.165.98.84|:443... connected.
    HTTP request sent, awaiting response... 200 OK
    Length: 178780 (175K) [application/octet-stream]
    Saving to: 'global-bundle.pem'
    
    100%[==============================================================================>] 178,780 --.-K/s in 0.002s 
    
    2023-08-15 15:20:05 (80.1 MB/s) - 'global-bundle.pem' saved [178780/178780]
  8. Create the wallet:

    [ssl_wallet]$ orapki wallet create -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.
  9. Add the certificates that you downloaded to the wallet file.

Download a global or Region-specific certificate bundle PEM file

Next, you must download a certificate bundle to use when you connect to the RDS for Oracle database. For more information, see Certificate bundles for specific AWS Regions.

Oracle's wallet orapki function can only import the initial certificates from a PEM bundle file. So, you can't import a bundle that contains multiple PEM files to the wallet. To activate SSL connections for either of the certificates (rds-ca-rsa2048-g1, rds-ca-rsa4096-g1), you must separate individual roots from AWS Region-specific PEM files.

To split the PEM file, use the following method:

cat us-east-1-bundle.pem |awk 'split_after==1{n++;split_after=0} /-----END CERTIFICATE-----/ {split_after=1} {print > "cert" n ".pem"}'

[oracle@ip-***-**-**-*** wallet]$ ls -lrt
total 28
-rw-r--r--. 1 oracle oinstall 7488 May 25  2021 us-east-1-bundle.pem
-rw-r--r--. 1 oracle oinstall 1456 Sep 16 05:41 cert.pem
-rw-r--r--. 1 oracle oinstall  989 Sep 16 05:41 cert4.pem
-rw-r--r--. 1 oracle oinstall 2139 Sep 16 05:41 cert3.pem
-rw-r--r--. 1 oracle oinstall 1448 Sep 16 05:41 cert2.pem
-rw-r--r--. 1 oracle oinstall 1456 Sep 16 05:41 cert1.pem

Next, add all PEM files to the wallet. You don't know which PEM file belongs to each certificate, so add all PEM files to the wallet.

orapki wallet add -wallet /data/wallet -trusted_cert -cert cert.pem -auto_login_only
orapki wallet add -wallet /data/wallet -trusted_cert -cert cert1.pem -auto_login_only
orapki wallet add -wallet /data/wallet -trusted_cert -cert cert2.pem -auto_login_only
orapki wallet add -wallet /data/wallet -trusted_cert -cert cert3.pem -auto_login_only
orapki wallet add -wallet /data/wallet -trusted_cert -cert cert4.pem -auto_login_only

Use openssl to download only the root certificate file

You can also use openssl to download only the root certificate file. Run the following command in the Red Hat Linux server:

openssl s_client -connect user.ckyx0wdxr13x.us-east-1.rds.amazonaws.com:2484 -showcerts </dev/null 2>/dev/null | sed -n '/s:C = US.*Root/,$p' | sed -n '/BEGIN CERT/,/END CERT/p'> root.pem

After you run this command, you can add the file that's created (root.pem) to your Oracle wallet. See the following example:

-----BEGIN CERTIFICATE-----
MIID/zCCAuegAwIBAgIRAPVSMfFitmM5PhmbaOFoGfUwDQYJKoZIhvcNAQELBQAw
gZcxCzAJBgNVBAYTAlVTMSIwIAYDVQQKDBlBbWF6b24gV2ViIFNlcnZpY2VzLCBJ
bmMuMRMwEQYDVQQLDApBbWF6b24gUkRTMQswCQYDVQQIDAJXQTEwMC4GA1UEAwwn
QW1hem9uIFJEUyB1cy1lYXN0LTEgUm9vdCBDQSBSU0EyMDQ4IEcxMRAwDgYDVQQH
DAdTZWF0dGxlMCAXDTIxMDUyNTIyMzQ1N1oYDzIwNjEwNTI1MjMzNDU3WjCBlzEL
MAkGA1UEBhMCVVMxIjAgBgNVBAoMGUFtYXpvbiBXZWIgU2VydmljZXMsIEluYy4x
EzARBgNVBAsMCkFtYXpvbiBSRFMxCzAJBgNVBAgMAldBMTAwLgYDVQQDDCdBbWF6
b24gUkRTIHVzLWVhc3QtMSBSb290IENBIFJTQTIwNDggRzExEDAOBgNVBAcMB1Nl
YXR0bGUwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQDu9H7TBeGoDzMr
dxN6H8COntJX4IR6dbyhnj5qMD4xl/IWvp50lt0VpmMd+z2PNZzx8RazeGC5IniV
5nrLg0AKWRQ2A/lGGXbUrGXCSe09brMQCxWBSIYe1WZZ1iU1IJ/6Bp4D2YEHpXrW
bPkOq5x3YPcsoitgm1Xh8ygz6vb7PsvJvPbvRMnkDg5IqEThapPjmKb8ZJWyEFEE
QRrkCIRueB1EqQtJw0fvP4PKDlCJAKBEs/y049FoOqYpT3pRy0WKqPhWve+hScMd
6obq8kxTFy1IHACjHc51nrGII5Bt76/MpTWhnJIJrCnq1/Uc3Qs8IVeb+sLaFC8K
DI69Sw6bAgMBAAGjQjBAMA8GA1UdEwEB/wQFMAMBAf8wHQYDVR0OBBYEFE7PCopt
lyOgtXX0Y1lObBUxuKaCMA4GA1UdDwEB/wQEAwIBhjANBgkqhkiG9w0BAQsFAAOC
AQEAFj+bX8gLmMNefr5jRJfHjrL3iuZCjf7YEZgn89pS4z8408mjj9z6Q5D1H7yS
jNETVV8QaJip1qyhh5gRzRaArgGAYvi2/r0zPsy+Tgf7v1KGL5Lh8NT8iCEGGXwF
g3Ir+Nl3e+9XUp0eyyzBIjHtjLBm6yy8rGk9p6OtFDQnKF5OxwbAgip42CD75r/q
p421maEDDvvRFR4D+99JZxgAYDBGqRRceUoe16qDzbMvlz0A9paCZFclxeftAxv6
QlR5rItMz/XdzpBJUpYhdzM0gCzAzdQuVO5tjJxmXhkSMcDP+8Q+Uv6FA9k2VpUV
E/O5jgpqUJJ2Hc/5rs9VkAPXeA==
-----END CERTIFICATE-----
orapki wallet add -wallet /data/wallet -trusted_cert -cert test.pem -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.
[oracle@ip-***-**-**-***  pem]$ orapki wallet display -wallet /data/wallet
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.

Requested Certificates: 
User Certificates:
Trusted Certificates: 
Subject:        L=Seattle,CN=Amazon RDS us-east-1 Root CA RSA2048 G1,ST=WA,OU=Amazon RDS,O=Amazon Web Services\, Inc.,C=US
[oracle@ip-172-31-19-245 pem]$ orapki cert display -cert test.pem 
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.

Subject:        L=Seattle,CN=Amazon RDS us-east-1 Root CA RSA2048 G1,ST=WA,OU=Amazon RDS,O=Amazon Web Services\, Inc.,C=US
Issuer:         L=Seattle,CN=Amazon RDS us-east-1 Root CA RSA2048 G1,ST=WA,OU=Amazon RDS,O=Amazon Web Services\, Inc.,C=US
Valid Until:    Wed May 25 23:34:57 UTC 2061

[oracle@ip-***-**-**-***  admin]$ sqlplus admin/*******@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=user.ckyx0wdxr13x.us-east-1.rds.amazonaws.com)(PORT=2484))(CONNECT_DATA=(SID=USER)))'

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 17 07:12:34 2023
Version 19.3.0.0.0

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

Last Successful login time: Sun Sep 17 2023 06:07:26 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0

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

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

Update the sqlnet.ora and tnsnames.ora files

Next, navigate to the network or admin directory. Locate the sqlnet.ora and tnsnames.ora files and update them accordingly. If the tnsnames.ora isn't present, then use the following method to create it:

[~]$ cd app/client/product/19.0.0/client_1/network/admin
[admin]$ cat > tnsnames.ora
net_service_name = 
   (DESCRIPTION = 
     (ADDRESS_LIST = 
       (ADDRESS = 
         (PROTOCOL = TCPS) 
         (HOST = user.ckyx0wdxr13x.us-east-1.rds.amazonaws.com) 
         (PORT = 2484)
       )
    )
     (CONNECT_DATA = 
      (SID = ORCL)
    )
    (SECURITY = 
      (SSL_SERVER_CERT_DN = "C=US,ST=Washington,L=Seattle,O=Amazon.com,OU=RDS,CN=endpoint")
    )
  )
:wq!

Update the sqlnet.ora file to include updated details for your SLL connections:

[admin]$ vi sqlnet.ora
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /data/wallet)))
SSL_CLIENT_AUTHENTICATION = TRUE
SSL_VERSION = 1.0
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA)
SSL_SERVER_DN_MATCH = ON
:wq!

Connect to your RDS for Oracle DB instance with the SSL option

After you configure SQL*Plus to use SSL, you can connect to your RDS for Oracle DB instance with the SSL option. Optionally, before you connect you can export the TNS_ADMIN value that points to the directory that contains the tnsnames.ora and sqlnet.ora files. This means that SQL*Plus can find the files consistently. See the following example command that exports the TNS_ADMIN value:

[client_1]$export TNS_ADMIN = ${ORACLE_HOME}/network/admin

Note: Before you connect to your DB instance with SSL, check the following:

  • The RDS root certificate is downloaded and added to a wallet file.
  • You have the correct SSL port number in your TNS entry.
  • Your Amazon RDS security group is configured to allow inbound connections from your machines over the SSL port.
  • The firewall or security policies are configured appropriately to allow traffic on the SSL port from Amazon RDS.

Related information

Setting up an SSL connection over JDBC

Updating applications to connect to Oracle DB instances using new SSL/TLS certificates

Troubleshooting SSL connections

AWS OFFICIAL
AWS OFFICIALUpdated 2 months ago
2 Comments

in tnsnsames.ora, should not CN=endpoint be CN=user.ckyx0wdxr13x.us-east-1.rds.amazonaws.com

spur230
replied 2 months ago

Thank you for your comment. We'll review and update the Knowledge Center article as needed.

profile pictureAWS
MODERATOR
replied 2 months ago