To enable Oracle Internet Directory (OID) to use Transport Layer Security (TLS), create a wallet and certificates, and modify tnsnames.ora and sqlnet.ora.

1. Log in to the database client server that has Oracle Internet Directory (OID) installed.
2. Go to the $ORACLE_HOME/ldap/lib directory
3. Run the following command:

   make -f ins_ldap.mk install

4. Go to the directory where the OID tnsnames.ora file is located.
   By default, this directory is $ORACLE_HOME/network/admin.
5. Edit the tnsnames.ora file to include the following OID settings, which will specify the TCPS port.

   For example:

   OIDDB=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)
      (HOST=sales_db.us.example.com)(PORT=5500))
       (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl.us.example.com)))
        (SECURITY=(SSL_SERVER_CERT_DN="CN=Server,O=Example,ST=California,C=US"))

   In this example, SSL_SERVER_CERT_DN points to the DN of the database server certificate.

6. Configure the wallet location in the sqlnet.ora file.

   For example:

   ENCRYPTION_WALLET_LOCATION=
    (SOURCE=
     (METHOD=FILE)
      (METHOD_DATA=
       (DIRECTORY=/etc/ORACLE/WALLETS/$ORACLE_SID/)))

7. Ensure that the sqlnet.ora file has the following settings:

   SSL_CLIENT_AUTHENTICATION = FALSE
   SSL_SERVER_DN_MATCH=OFF

8. Use the orapki utility to create a new wallet and add database certificates to it.
   For example:

   orapki wallet create -wallet /etc/ORACLE/WALLETS/$ORACLE_SID/oid_wallet
   -auto_login -pwd wallet_password
   orapki wallet add -wallet /etc/ORACLE/WALLETS/$ORACLE_SID/oid_wallet
   -trusted_cert -cert /etc/ORACLE/certificates/dbssl/root/b64certificate.txt 
   -pwd wallet_password
   ./orapki wallet add -wallet /etc/ORACLE/WALLETS/$ORACLE_SID/oid_gwallet
   -trusted_cert -cert /etc/ORACLE/certificates/dbssl/netadmin/cert.txt -pwd
   wallet_password

You must perform this type of configuration on the server first, then the client.

• About Configuring TLS for Client Authentication and Encryption with X.509 Certificates
  You can enable Public Key Infrastructure (PKI) authentication between Oracle Database clients and an Oracle database with X.509 certificates.
• Configuring the Server for Authentication and Encryption with X.509 Certificates
  You must configure the server's listener.ora, sqlnet.ora, and initialization files and create a database user account for authentication and encryption with X.509 certificates.
• Configuring the Client for Authentication and Encryption with X.509 Certificates
  You must configure the client's sqlnet.ora, tnsnames.oralistener.ora files, and configure the Microsoft Certificate Store (MCS) for authentication and encryption with X.509 certificates.

You can use an Oracle wallet, PL/SQL packages, and security access control lists (ACLs) to configure email over a Transport Layer Security (TLS) connection.

1. Use openssl to get the URL certificates from the mail server.
   You can perform this step with email server, to dump the certificate chain to a standard output (stdout). Typically, this command dumps the server certificate (cert 0) and the intermediate trusted certificate (cert 1...n). For example:

   $ openssl s_client -showcerts -connect office365.com:443

   Output similar to the following appears:

   depth=2 C = US, O = DigiCert Inc, CN = DigiCert Global Root CA
   verify return:1
   depth=1 C = US, O = DigiCert Inc, CN = DigiCert Cloud Services CA-1
   verify return:1
   depth=0 C = US, ST = Washington, L = Redmond, O = Microsoft Corporation, CN = outlook.com
   verify return:1
   ---
   Certificate chain
   0 s:/C=US/ST=Washington/L=Redmond/O=Microsoft Corporation/CN=outlook.com
   i:/C=US/O=DigiCert Inc/CN=DigiCert Cloud Services CA-1
   -----BEGIN CERTIFICATE-----
   ...
   -----END CERTIFICATE-----
   ...
   DONE

2. Copy and paste the certificates in this output to text files with the extension .cer.
   You must copy the text that appears after -----BEGIN CERTIFICATE ----- and before -----END CERTIFICATE-----. Example files are as follows:

   • file_root.cer
   • file_trusted.cer
   • file_user.cer
3. Check the CA issuer and the CA subject of each certificate that you copied to a certificate file.
   The CA issuer is the company that created the certificate and the subject indicates the information that had been provided when the certificate was created.

   • To check the root certificate:

     openssl x509 -in file_root.cer -text | grep -i issuer
     Issuer: C=US, O=DigiCert Inc, CN=DigiCert Global Root CA
     
     openssl x509 -in file_root.cer -text | grep -i subject
     Subject: C=US, O=DigiCert Inc, CN=DigiCert Global Root CA

   • To check the trusted certificate:

     openssl x509 -in file_trusted.cer -text | grep -i issuer
     Issuer: C=US, O=DigiCert Inc, CN=DigiCert Global Root CA
     
     openssl x509 -in file_trusted.cer -text | grep -i subject
     Subject: C=US, O=DigiCert Inc, CN=DigiCert SHA2 Secure Server CA

   • To check the user certificate:

     openssl x509 -in file_user.cer -text | grep -i issuer
     Issuer: C=US, O=DigiCert Inc, CN=DigiCert Global Root CA
     
     openssl x509 -in file_user.cer -text | grep -i subject
     Subject: C=US, O=DigiCert Inc, CN=DigiCert SHA2 Secure Server CA

4. Create a folder location.
   For example:

   mkdir app/oracle/product/network/admin/email

5. Create the wallet and add its certificates to this wallet.
   1. Create an empty wallet.
      For example:

      orapki wallet create -wallet wallet_file_directory -auto_login [-pwd wallet_password]

      If you omit the pwd prompt, then a password prompt appears. For better security, enter the password at the prompt instead of entering it at the command line.

   2. Put the certificate into the wallet. For example:

      orapki wallet add -wallet wallet_file_directory -trusted_cert -cert trusted.cer 
      [-pwd wallet_password]

6. Prepare the email SQL code.
   For example:

   ##############################################################################
   ##
   DECLARE
   k_host CONSTANT VARCHAR2(100) := 'us.example.com';
   k_port CONSTANT INTEGER := 587;
   k_wallet_path CONSTANT VARCHAR2(100) :=
   'file:app/oracle/product/network/admin/email';
   k_wallet_password CONSTANT VARCHAR2(100) := 'wallet_password';
   k_domain CONSTANT VARCHAR2(100) := 'localhost';
   k_username CONSTANT VARCHAR2(100) := 'email_account';
   k_password CONSTANT VARCHAR2(100) := 'email_account_password';
   k_sender CONSTANT VARCHAR2(100) := 'email_account';
   k_recipient CONSTANT VARCHAR2(100) := 'email_account_sending_too';
   k_subject CONSTANT VARCHAR2(100) := 'Test TLS mail';
   k_body CONSTANT VARCHAR2(100) := 'We Love Database Security';
   
   l_conn utl_smtp.connection;
   l_reply utl_smtp.reply;
   l_replies utl_smtp.replies;
   BEGIN
   dbms_output.put_line('utl_smtp.open_connection');
   
   l_reply := utl_smtp.open_connection
   ( host => k_host
   , port => k_port
   , c => l_conn
   , wallet_path => k_wallet_path
   , wallet_password => k_wallet_password
   , secure_connection_before_smtp => FALSE
   );
   
   IF l_reply.code != 220
   THEN
   raise_application_error(-20000, 'utl_smtp.open_connection: '||l_reply.code||'
   - '||l_reply.text);
   END IF;
   
   dbms_output.put_line('utl_smtp.ehlo');
   
   l_replies := utl_smtp.ehlo(l_conn, k_domain);
   
   FOR ri IN 1..l_replies.COUNT
   LOOP
   dbms_output.put_line(l_replies(ri).code||' - '||l_replies(ri).text);
   END LOOP;
   
   dbms_output.put_line('utl_smtp.starttls');
   
   l_reply := utl_smtp.starttls(l_conn);
   
   IF l_reply.code != 220
   THEN
   raise_application_error(-20000, 'utl_smtp.starttls: '||l_reply.code||' -
   '||l_reply.text);
   END IF;
   
   dbms_output.put_line('utl_smtp.ehlo');
   
   l_replies := utl_smtp.ehlo(l_conn, k_domain);
   
   FOR ri IN 1..l_replies.COUNT
   LOOP
   dbms_output.put_line(l_replies(ri).code||' - '||l_replies(ri).text);
   END LOOP;
   
   dbms_output.put_line('utl_smtp.auth');
   
   l_reply := utl_smtp.auth(l_conn, k_username, k_password,
   utl_smtp.all_schemes);
   
   IF l_reply.code != 235
   THEN
   raise_application_error(-20000, 'utl_smtp.auth: '||l_reply.code||' -
   '||l_reply.text);
   END IF;
   
   dbms_output.put_line('utl_smtp.mail');
   
   l_reply := utl_smtp.mail(l_conn, k_sender);
   
   IF l_reply.code != 250
   THEN
   raise_application_error(-20000, 'utl_smtp.mail: '||l_reply.code||' -
   '||l_reply.text);
   END IF;
   
   dbms_output.put_line('utl_smtp.rcpt');
   
   l_reply := utl_smtp.rcpt(l_conn, k_recipient);
   
   IF l_reply.code NOT IN (250, 251)
   THEN
   raise_application_error(-20000, 'utl_smtp.rcpt: '||l_reply.code||' -
   '||l_reply.text);
   END IF;
   
   dbms_output.put_line('utl_smtp.open_data');
   
   l_reply := utl_smtp.open_data(l_conn);
   
   IF l_reply.code != 354
   THEN
   raise_application_error(-20000, 'utl_smtp.open_data: '||l_reply.code||' -
   '||l_reply.text);
   END IF;
   
   dbms_output.put_line('utl_smtp.write_data');
   
   utl_smtp.write_data(l_conn, 'From: '||k_sender||utl_tcp.crlf);
   utl_smtp.write_data(l_conn, 'To: '||k_recipient||utl_tcp.crlf);
   utl_smtp.write_data(l_conn, 'Subject: '||k_subject||utl_tcp.crlf);
   utl_smtp.write_data(l_conn, utl_tcp.crlf||k_body);
   
   dbms_output.put_line('utl_smtp.close_data');
   
   l_reply := utl_smtp.close_data(l_conn);
   
   IF l_reply.code != 250
   THEN
   raise_application_error(-20000, 'utl_smtp.close_data: '||l_reply.code||' -
   '||l_reply.text);
   END IF;
   
   dbms_output.put_line('utl_smtp.quit');
   
   l_reply := utl_smtp.quit(l_conn);
   
   IF l_reply.code != 221
   THEN
   raise_application_error(-20000, 'utl_smtp.quit: '||l_reply.code||' -
   '||l_reply.text);
   END IF;
   
   EXCEPTION
   WHEN utl_smtp.transient_error
   OR utl_smtp.permanent_error
   THEN
   BEGIN
   utl_smtp.quit(l_conn);
   EXCEPTION
   WHEN utl_smtp.transient_error
   OR utl_smtp.permanent_error
   THEN
   NULL;
   END;
   
   raise_application_error(-20000, 'Failed to send mail due to the following
   error: '||SQLERRM);
   
   END;
   /
   

   Ensure that you set the secure_connection_before_smtp parameter to FALSE. This translates to "do not use TLS before the email is sent". Setting it to TRUE generates the following error if we only want to send the email over TLS:

   ERROR at line 1:
   ORA-29019: The protocol version is incorrect.
   ORA-06512: at "SYS.UTL_TCP", line 63
   ORA-06512: at "SYS.UTL_TCP", line 314
   ORA-06512: at "SYS.UTL_SMTP", line 177
   ORA-06512: at line 20
   

7. Create the user who will send emails.
   For example:

   CREATE USER user_name IDENTIFIED BY password;
   GRANT CREATE SESSION TO user_name;

8. Append the host and wallet access control entries (ACE) to the default access control list (ACL).
   1. Append the host access control entry (ACE).

      BEGIN
      DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE (
      host => 'us.example.com',
      lower_port => 587,
      upper_port => 587,
      ace => xs$ace_type(privilege_list => xs$name_list('http'),
      principal_name => 'user_name',
      principal_type => xs_acl.ptype_db));
      END;
      /
      
      BEGIN
      DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE (
      host => 'us.example.com',
      lower_port => 587,
      upper_port => 587,
      ace => xs$ace_type(privilege_list => xs$name_list('connect'),
      principal_name => 'user_name',
      principal_type => xs_acl.ptype_db));
      END;
      /
      
      BEGIN
      DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE (
      host => 'us.example.com',
      lower_port => null,
      upper_port => null,
      ace => xs$ace_type(privilege_list => xs$name_list('resolve'),
      principal_name => 'user_name',
      principal_type => xs_acl.ptype_db));
      END;
      /

   2. Append the wallet ACE.

      BEGIN
      DBMS_NETWORK_ACL_ADMIN.APPEND_WALLET_ACE(
      wallet_path =>
      'file:/u01/64bit/app/oracle/product/network/admin/email',
      ace => xs$ace_type(privilege_list => xs$name_list('use_client_certificates',
      'use_passwords'),
      principal_name => 'user_name',
      principal_type => xs_acl.ptype_db));
      END;
      /

Oracle provides a utility to help troubleshoot PKI certificate configurations as well as additional guidance below. A utility is available through the support website to review and provide feedback on your PKI certificate authentication client and server configuration.

See DBSecChk Utility 2.0.0.5 (Doc ID 3066006.1).

• Step 1: Check the TLS Connection with the tnsping Utility
  A successful connection using the tnsping utility shows that the database service has been registered to the listener on the TCPS endpoint.
• Step 2: Check the SSL_VERSION Parameter
  An incorrectly set SSL_VERSION parameter can cause Transport Layer Security (TLS) problems.
• Step 3: Check the Wallet File Permissions
  The Transport Layer Security (TLS) connection requires the database and listener to have access to the auto-login wallet file (cwallet.sso).
• Step 4: Check the Wallet Settings in the sqlnet.ora and listener.ora Files
  Transparent Layer Security (TLS) problems can arise from wallet and certificate configuration errors in the sqlnet.ora and listener.ora files.
• Step 5: Enable Tracing for the SQL*Net and Listener Connections
  In the sqlnet.ora file, you can enable tracing for SQL*Net and listener connections.