Accessing SMTP.GMAIL.COM from Apex

Pericles

Hello

I'm trying to setup the outgoing mailing for Apex 22.1

As I read, there a could of steps to accomplish to get this done:

1.- Get the certificates in DER format.

DONE: Got the google.com smtp.google.com and smtp-relay.google.com

2.- Create a directory where to create the wallet

DONE: /opt/oracle/homes/OraDBHome21cXE/admin/XE/apex_wallet

3.- Create the wallet with orapki wallet create -wallet /opt/oracle/homes/OraDBHome21cXE/admin/XE/apex_wallet -pwd <SECURE_PASSWORD> -auto_login

DONE

4.- Add the certificates to the wallet with orapki wallet add -wallet /opt/oracle/homes/OraDBHome21cXE/admin/XE/apex_wallet -trusted_cert -cert "gmail.cer" -pwd <SECURE_PASSWORD>

DONE

5.- Modify the ACL to allow connections to smtp.google.com

DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(

 host => 'smtp.gmail.com',

 lower_port => 587,

 upper_port => 587,

 ace => xs$ace_type(privilege_list => xs$name_list('connect'),

                      principal_name => 'APEX_220100',

                      principal_type => xs_acl.ptype_db));

dbms_network_acl_admin.append_wallet_ace(

 wallet_path => 'file:/opt/oracle/homes/OraDBHome21cXE/admin/XE/apex_wallet',

 ace        => xs$ace_type(privilege_list => xs$name_list('use_client_certificates', 'use_passwords'),

                             principal_name => 'APEX_220100',

                             principal_type => xs_acl.ptype_db));

DONE

After all this I'm still having problems with the SMTP connection.

Checked the ACL with SELECT * FROM DBA_NETWORK_ACLS and it seems to be ok

I do verify the wallet with "orapki wallet display -wallet /opt/oracle/homes/OraDBHome21cXE/admin/XE/apex_wallet" and it seems to be ok

Requested Certificates:

User Certificates:

Trusted Certificates:

Subject:       CN=smtp-relay.gmail.com

Subject:       CN=smtp.gmail.com

Subject:       CN=*.google.com

Also configured a mail client and checked that the account is open to external connection.

Any suggestion?

Pericles

Finally, I successfully complete the configuration.

I delete all the certificates on the Wallet and instead of trying to get the certificate from smtp.gmail.com I get the certificate chain of mail.google.com and in this way the connection finally was successful.

Regards

Pericles

Hello

After using OWM to generate a new wallet, and modify the instance parameter 'Use tls" to "After the connection is established" finally I have some improvement.

Now, if I try to send an email to smtp.gmail.com through port 587 I receive this error on the Mail Queue

Error sending mail.

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

Checking the DBA_SCHEDULER_JOB_RUN_DETAILS view I'm getting the following error:

Additional info:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

ORA-06512: at "SYS.UTL_SMTP", line 110

ORA-06512: at "SYS.UTL_SMTP", line 235

ORA-06512: at "SYS.UTL_SMTP", line 215

ORA-06512: at "SYS.UTL_SMTP", line 630

ORA-06512: at "SYS.UTL_SMTP", line 641

ORA-06512: at "APEX_220100.WWV_FLOW_MAIL", line 163

ORA-06512: at "APEX_220100.WWV_FLOW_MAIL", line 1894

ORA-06512: at line 1

ERROR:

# APEX|WRN|Exception in "":                                                                        %MAIL.push_queue_immediate:1881<-:1

# APEX|  |Error Stack: ORA-29024: Certificate validation failure

# APEX|  |ORA-06512: at "SYS.UTL_TCP", line 63

# APEX|  |ORA-06512: at "SYS.UTL_TCP", line 314

# APEX|  |ORA-06512: at "SYS.UTL_SMTP", line 290

# APEX|  |ORA-06512: at "SYS.UTL_SMTP", line 296

# APEX|  |ORA-06512: at "APEX_220100.WWV_FLOW_MAIL", line 1243

# APEX|  |Backtrace: ORA-06512: at "SYS.UTL_TCP", line 63

# APEX|  |ORA-06512: at "SYS.UTL_TCP", line 314

# APEX|  |ORA-06512: at "SYS.UTL_SMTP", line 290

# APEX|  |ORA-06512: at "SYS.UTL_SMTP", line 296

# APEX|  |ORA-06512: at "APEX_220100.WWV_FLOW_MAIL", line 1243

# APEX|  |ORA-06512: at "APEX_220100.WWV_FLOW_MAIL", line 1866

# APEX|  |

I've downloaded again the certificate for smtp.gmail.com using

echo | openssl s_client -connect smtp.gmail.com:587 -starttls smtp | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > smtp.gmail.com.crt

but still getting the same certificate error.

Also included in the wallet all the certificates for the rest of the chain but that didn't fix the issue

Thanks

Pericles

Finally, I successfully complete the configuration.

I delete all the certificates on the Wallet and instead of trying to get the certificate from smtp.gmail.com I get the certificate chain of mail.google.com and in this way the connection finally was successful.

Regards