Forum Stats

  • 3,837,497 Users
  • 2,262,265 Discussions
  • 7,900,305 Comments

Discussions

Categories

Accessing SMTP.GMAIL.COM from Apex

Pericles
Pericles Member Posts: 61 Blue Ribbon
in General Database Discussions

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

image.png

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?

· Share on TwitterShare on Facebook

Best Answer

  • Pericles
    Pericles Member Posts: 61 Blue Ribbon
    Answer ✓

    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

    · Share on TwitterShare on Facebook

Answers

  • Pericles
    Pericles Member Posts: 61 Blue Ribbon

    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

    · Share on TwitterShare on Facebook
  • Pericles
    Pericles Member Posts: 61 Blue Ribbon
    Answer ✓

    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

    · Share on TwitterShare on Facebook