Using Oracle wallet/TLS for UTL_SMTP connections in RDS to smtp.office365.com

This guide outlines a simplified method for creating and uploading an Oracle Wallet to an Oracle RDS instance. Unlike the AWS approach, which involves using an object store and UTL_HTTP, this method uses SQL Developer to upload the wallet as a BLOB to a table and write it directly to the Oracle directory.

Step 1:

I could not have solved this issue without the help of Ilmar Kerm. See the link he shared with me here. The script below pulls each cert from the tls-ca-bundle that is provided on most Linux systems and adds them to a new wallet. This wallet can then be uploaded to RDS.

#!/usr/bin/env bash

# Input file containing multiple certificates
input_file="${1:-/etc/pki/ca-trust/extracted/pem/tls-ca-bundle.pem}"
cert_num=1
# The first time the code below writes cert_file is not defined and would throw an error.
cert_file=/dev/null

mv ./cwallet.sso ./cwallet.sso.$(date +%Y%m%d%H%M%S) 2>/dev/null
# Create a new cwallet.sso file.
orapki wallet create -wallet . -auto_login_only

# Read the input file line by line
while IFS= read -r line; do
    if [[ "$line" =~ ^-----BEGIN[[:space:]]+CERTIFICATE----- ]]; then
        # Start a new certificate file
        cert_file="./cert_${cert_num}.pem"
        echo "$line" > "$cert_file"
        cert_num=$((cert_num + 1))
    elif [[ "$line" =~ ^-----END[[:space:]]+CERTIFICATE----- ]]; then
        # End the current certificate file
        echo "$line" >> "$cert_file"
        # Add the cert to the wallet.
        orapki wallet add -wallet . -trusted_cert -cert $cert_file -auto_login_only
        rm $cert_file
    else
        # Add the line to the current certificate file
        echo "$line" >> "$cert_file"
    fi
done < "$input_file"

# List the certs in the wallet.
orapki wallet display -wallet .

exit 0

Step 2: Retrieve the SSL Certificates

This step is no longer necessary but I am leaving it here for reference. You can also review the certificate authority and make sure the tls-ca-bundle.pem file contains a cert for that authority.

echo | openssl s_client -connect smtp.office365.com:587 -starttls smtp -showcerts

Also leaving this here for reference if you need to remove a cert from a wallet.

orapki wallet remove -wallet . -trusted_cert -alias "CN=DigiCert Global Root CA" -auto_login_only

Step 3: Copy the Wallet to a Server with SQL Developer Access

Once the wallet is configured, copy the cwallet.sso file to a server that has SQL Developer installed and can access the RDS instance. This ensures that you can use SQL Developer to interact with the RDS database while utilizing the wallet for secure connections.

Step 4: Create a Table to Store the Wallet File

Create a table in SQL Developer to upload the wallet file into a directory:

CREATE TABLE my_files (
    id NUMBER PRIMARY KEY,
    file_name VARCHAR2(255),
    file_data BLOB);

Use native SQL Developer interface to add a row (id=1) with the file name of cwallet.sso and the file data being the contents of the file. SQL Developer will allow you to upload the file as a blob by selecting it.

Step 5: Create a Directory for the Wallet

Next, create a directory on the RDS instance to store the wallet file:

exec rdsadmin.rdsadmin_util.create_directory('WALLET');

Step 6: Write the Wallet File to the directory

Use the following PL/SQL code to write the wallet file to the directory:

DECLARE    
    v_file UTL_FILE.FILE_TYPE;
    v_buffer RAW(32767);
    v_amount BINARY_INTEGER := 32767;
    v_pos INTEGER := 1;
    v_blob BLOB;
    v_blob_len INTEGER;
    v_filename VARCHAR2(255);
BEGIN    
    -- Retrieve the BLOB and filename from the table    
    SELECT file_data, file_name
    INTO v_blob, v_filename
    FROM my_files
    WHERE id = 1; -- Adjust this as necessary to select the correct row    
    
    v_blob_len := DBMS_LOB.getlength(v_blob);
    
    -- Open the file for writing in binary mode, this will zero out the file    
    v_file := UTL_FILE.fopen('WALLET', v_filename, 'wb', 32767);
    
    -- Close the file immediately to ensure it's zeroed out    
    UTL_FILE.fclose(v_file);
    
    -- Reopen the file for appending    
    v_file := UTL_FILE.fopen('WALLET', v_filename, 'ab', 32767);
    
    -- Write the BLOB to the file in chunks    
    WHILE v_pos <= v_blob_len LOOP        
        DBMS_LOB.read(v_blob, v_amount, v_pos, v_buffer);
        UTL_FILE.put_raw(v_file, v_buffer, TRUE);
        v_pos := v_pos + v_amount;
    END LOOP;
    
    -- Close the file    
    UTL_FILE.fclose(v_file);

EXCEPTION    
    WHEN OTHERS THEN        
        IF UTL_FILE.is_open(v_file) THEN            
            UTL_FILE.fclose(v_file);
        END IF;
        RAISE;
END;
/

Step 7: Configure and Test the SMTP Connection

After opening outbound access to the SMTP server, use the following PL/SQL code to test sending an email:

DECLARE
    l_smtp_server   VARCHAR2(1024) := 'smtp.office365.com';
    l_smtp_port     NUMBER := 25;
    l_wallet_dir    VARCHAR2(128) := 'WALLET';
    l_from          VARCHAR2(128) := 'NoReply@somecompany.com';
    l_to            VARCHAR2(128) := 'ethan.post@somecompany.com';
    l_user          VARCHAR2(128) := 'NoReply@somecompany.com';
    l_password      VARCHAR2(128) := 'BobTheBuilder123NotARealPassword';
    l_subject       VARCHAR2(128) := 'Test subject';
    l_wallet_path   VARCHAR2(4000);
    l_conn          UTL_SMTP.CONNECTION;
    l_reply         UTL_SMTP.REPLY;
    l_replies       UTL_SMTP.REPLIES;
BEGIN
    SELECT 'file:/' || directory_path INTO l_wallet_path
    FROM dba_directories
    WHERE directory_name = l_wallet_dir;

    -- Open a connection    
    l_reply := UTL_SMTP.OPEN_CONNECTION(
        host => l_smtp_server,
        port => l_smtp_port,
        c => l_conn,
        wallet_path => l_wallet_path,
        secure_connection_before_smtp => FALSE
    );
    DBMS_OUTPUT.PUT_LINE('Opened connection, received reply ' || l_reply.code || '/' || l_reply.text);

    -- Get supported configs from the server    
    l_replies := UTL_SMTP.EHLO(l_conn, 'smtp.office365.com');
    FOR r IN 1..l_replies.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('EHLO (server config): ' || l_replies(r).code || '/' || l_replies(r).text);
    END LOOP;

    -- STARTTLS    
    l_reply := UTL_SMTP.STARTTLS(l_conn);
    DBMS_OUTPUT.PUT_LINE('STARTTLS, received reply ' || l_reply.code || '/' || l_reply.text);

    -- EHLO again after STARTTLS    
    l_replies := UTL_SMTP.EHLO(l_conn, 'smtp.office365.com');
    FOR r IN 1..l_replies.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('EHLO (server config): ' || l_replies(r).code || '/' || l_replies(r).text);
    END LOOP;

    -- Authenticate and send the email    
    UTL_SMTP.AUTH(l_conn, l_user, l_password, UTL_SMTP.ALL_SCHEMES);
    UTL_SMTP.MAIL(l_conn, l_from);
    UTL_SMTP.RCPT(l_conn, l_to);
    UTL_SMTP.OPEN_DATA(l_conn);
    UTL_SMTP.WRITE_DATA(l_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(l_conn, 'From: ' || l_from || UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(l_conn, 'To: ' || l_to || UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(l_conn, 'Subject: ' || l_subject || UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(l_conn, '' || UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(l_conn, 'Test message.' || UTL_TCP.CRLF);
    UTL_SMTP.CLOSE_DATA(l_conn);

    -- Quit the connection    
    l_reply := UTL_SMTP.QUIT(l_conn);

EXCEPTION
    WHEN OTHERS THEN
        UTL_SMTP.QUIT(l_conn);
        RAISE;
END;
/