Hi all,
Currently we're facing the following problem and are at a point where where running out of clues, so hopefully someone recognizes the following:
In short: We need to send email from the database through another mailserver than the one we're currently using, the new mailserver uses SSL/TLS.
Customer has a route to smtprelay.somecity.nl over the network (via 10.xxx.xxx.xxx).
If we try to connect to smtprelay.somecity.nl we run into a timeout (421-error code), because smtprelay.somecity.nl externally has an IP address (217.xxx.xxx.xxx).
When we email IP-based we get the 'Encryption required' error and we're assuming that using STARTTLS will take care of that:
ORA-29279: Permanente SMTP-fout: 538 #5.7.11 Encryption required
ORA-06512: in "SYS.UTL_SMTP", regel 57
ORA-06512: in "SYS.UTL_SMTP", regel 142
ORA-06512: in "SYS.UTL_SMTP", regel 446
ORA-06512: in "BLA.SENDMAIL_STARTTLS_PCK", regel 177
ORA-06512: in "BLA.SENDMAIL_STARTTLS_PCK", regel 219
ORA-06512: in "BLA.SENDMAIL_STARTTLS_PCK", regel 193
ORA-06512: in regel 2
29279. 00000 - "SMTP permanent error: %s"
*Cause: A SMTP permanent error occurred.
*Action: Correct the error and retry the SMTP operation.
We received the certificate-chain of smtprelay.somecity.nl server from customer, and added them to a wallet (3 trust-certificas)
We adjusted and implemented our SENDMAIL_PCK to use STARTTLS:
FUNCTION begin_session_starttls
(
ps_username IN VARCHAR2,
ps_password IN VARCHAR2,
ps_smtp_host IN VARCHAR2
)
RETURN utl_smtp.connection
IS
conn utl_smtp.connection;
BEGIN
-- open SMTP connection
conn := utl_smtp.open_connection
(
ps_smtp_host,
smtp_port,
wallet_path => 'file:\some\path\',
wallet_password => 'somepassword',
secure_connection_before_smtp => FALSE,
tx_timeout => get_mail_parameter( 'SMTP_TIMEOUT' )
);
utl_smtp.ehlo( conn, smtp_domain );
utl_smtp.starttls( conn );
utl_smtp.ehlo( conn, smtp_domain );
(LINE 177!) utl_smtp.auth( conn, ps_username, ps_password, utl_smtp.ALL_SCHEMES );
RETURN conn;
END;
When we deliberatly use a wrong path to the wallet we get a File-not-found error
When we deliberatly use a wrong password for the wallet we get an error when trying to open the wallet.
When we use the correct values we get:
ORA-29024: Validatie certificaat mislukt
ORA-06512: in "SYS.UTL_TCP", regel 63
ORA-06512: in "SYS.UTL_TCP", regel 314
ORA-06512: in "SYS.UTL_SMTP", regel 290
ORA-06512: in "SYS.UTL_SMTP", regel 296
ORA-06512: in "BLA.SENDMAIL_STARTTLS_PCK", regel 174
ORA-06512: in "BLA.SENDMAIL_STARTTLS_PCK", regel 219
ORA-06512: in "BLA.SENDMAIL_STARTTLS_PCK", regel 193
ORA-06512: in regel 6
29024. 00000 - "Certificate validation failure"
*Cause: The certificate sent by the other side could not be validated. This may occur if
the certificate has expired, has been revoked, or is invalid for another reason.
*Action: Check the certificate to determine whether it is valid. Obtain a new certificate,
alert the sender that the certificate has failed, or resend.
Does anyone has a clue what could be the problem here?
Does Oracle log additional information somewhere else besides the known/standard logging?
Thanks in advance for any pointers/other useful tips/hints...