Thanks for the question, Lavdim.

Asked: January 30, 2018 - 11:19 am UTC

Last updated: February 23, 2018 - 12:41 am UTC

Version: 12.2

Viewed 10K+ times! This question is

You Asked

Dear Ask TOM Team,

We have upgrade our DEV Environment to the Oracle 12.2 Release and we are about to finish our upgrade tests. Now one test that we thought should be a simple one is the SEND Mail over the UTL_SMTP package.
In the release 12.1 we already use the WALLET feature and this was working fine. Now with 12.2 it seems that a new parameter is out there (secure_host). Which is not in the documentation of Oracle yet: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/UTL_SMTP.html#GUID-D6826448-1407-4476-AFB8-D1EB14E38F67

I thought this can not be so difficult so I tried to test it on myself. Now after three days of try and error. I've no idea what do.

We use office365 from Microsoft for our Mailserver. May you can help me what to do, so that this test code would send mails again like on the release 12.1:

We really tried a lot of values for the parameter "secure_host" but we get always the ORA-24263 Error.

Here is our test code:

DECLARE
  c utl_smtp.connection;
  l_mailhost    VARCHAR2 (64) := 'smtp.office365.com';
  l_from        VARCHAR2 (64) := 'testfrom@hepart.ch';
  l_to          VARCHAR2 (64) := 'testto@hepart.ch';
  l_subject     VARCHAR2 (64) := 'Test message from Oracle DB 12.2';
  crlf varchar2(2) := UTL_TCP.CRLF;
BEGIN

  c := utl_smtp.open_connection (
            host => l_mailhost,
            port => 587,
            wallet_path => 'file:C:\oracle\app\wallets\',
            wallet_password => 'walletpw',
            secure_connection_before_smtp => FALSE
            ,secure_host => 'outlook.com'
        );

  utl_smtp.helo(c, l_mailhost) ;

  UTL_SMTP.STARTTLS(c);
  UTL_SMTP.EHLO(c, l_mailhost);

  utl_smtp.command( c, 'AUTH LOGIN');
  utl_smtp.command( c, 'bm9fcmVwbHlAaGVwYzb2Z0LmNvbQ==');
  utl_smtp.command( c, 'SHVmdTxLSE=');  

  UTL_SMTP.mail (c, l_from);
  UTL_SMTP.rcpt (c, l_to);
  UTL_SMTP.open_data (c);
  UTL_SMTP.write_data (c, 'Date: ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || crlf);
  UTL_SMTP.write_data (c, 'From: ' || l_from || crlf);
  UTL_SMTP.write_data (c, 'Subject: ' || l_subject || crlf);
  UTL_SMTP.write_data (c, 'To: ' || l_to || crlf);
  UTL_SMTP.write_data (c, 'asdf' || crlf);
  
  UTL_SMTP.close_data (c);
  UTL_SMTP.quit (c); 
EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    utl_smtp.close_connection(c);
END;
/

and Connor said...

I'll raise a doc bug that the parameter has not been documented.

The description for that parameter is:

   *   secure_host  the host name to be matched against the common name (CN) of
   *                the SMTP server's certificate when a secure connection is
   *                used. It can also be a domain mame like "*.example.com".
   *                If NULL, the SMTP host name to connect to will be used.

with additional details being

When making a secured connection or request, UTL_TCP, UTL_SMTP and UTL_HTTP now verify the digital certificate of the remote server and will raise the following error if the server address in the common name (CN) attribute of the certificate does not match the requested connection or URL.

ORA-24263: Certificate of the remote server does not match the target address.

In case when the address in the certificate is expected to be different (for example, when accessing the server by IP address), the caller can provide the expected address or domain name to match via an additional parameter when making the connection or request.

But ultimately, I suspect you need to find the precise CN name for outlook.com

Hope this helps.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Still issues with this

Lavdim Djaferi, January 31, 2018 - 1:48 pm UTC

Thanks AskTOM Team.

I mean this makes all sense but we really tried every possible CN Name we can find anywhere around the office365.

What is really strange: If you have a wallet with the Certs and you get the error ORA-24263, then you change the Certs with others, we get the error: ORA-29024. When we remove all certs from the wallet and click save, we expect ORA-28845 (No certificate) but we still get the ORA-29024. Only when you then restart the Oracle Service, you get the "No certificate" error. That's why we think there is something going wrongt with the SMTP and TLS function.

February 01, 2018 - 1:12 am UTC

OK, just making sure your wallet is OK as well.

When I run:

[oracle@lin122 ~]$ openssl s_client -connect smtp.office365.com:587 -starttls smtp
CONNECTED(00000003)
depth=2 C = US, O = DigiCert Inc, OU = www.digicert.com, 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
 1 s:/C=US/O=DigiCert Inc/CN=DigiCert Cloud Services CA-1
   i:/C=US/O=DigiCert Inc/OU=www.digicert.com/CN=DigiCert Global Root CA
...

----BEGIN CERTIFICATE-----
MIIG/jCCBeagAwIBAgIQDs2Q7J6KkeHe1d6ecU8P9DANBgkqhkiG9w0BAQsFADBL
MQswCQYDVQQGEwJVUzEVMBMGA1UEChMMRGlnaUNlcnQgSW5jMSUwIwYDVQQDExxE
aWdpQ2VydCBDbG91ZCBTZXJ2aWNlcyBDQS0xMB4XDTE3MDkxMzAwMDAwMFoXDTE4
...
lRDx4vTHGQiUzHY6EwgJ5iGRqgxUX/NmO4dJnC7IpKbdY/v1b9KKGzFpw27OkXqk
nGhseM2tJfwa2HMwUpuuo5029u4Dd40qvD0cMz33cOvBLRGkTPbXCFw24ZBdQrkt
SC5TAWzHFyT2tLC17LeSb7d0g+fuj41L6y4a9och8cPiv9IAP4sftzYupO99h4qg
7UXP7o3AOOGqrPS3INhO4068Z63indstanIHYM0IUHa3A2xrcz7ZbEuw1HiGH/Ba
HMz/gTSd2c0BXNiPeM7gdOK3
-----END CERTIFICATE-----

So you need to make sure that you

a) get/import the 2 certificates in the certificate chain into your wallet
b) import the base64 certificate as well.

If you're already done that, and you're still having dramas, it might be time to log a call with Support.

wallet issue

Lavdim Djaferi, February 02, 2018 - 9:11 am UTC

Thanks that's what I did. I used this openssl command to get the certs:

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

I did import them to the wallet:

C:\oracle\app\wallets>orapki wallet display -wallet ./
Oracle PKI Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
Requested Certificates:
Subject:        C=CH,ST=test,L=test,O=test,OU=test,CN=test
User Certificates:
Trusted Certificates:
Subject:        CN=DigiCert Cloud Services CA-1,O=DigiCert Inc,C=US
Subject:        CN=outlook.com,O=Microsoft Corporation,L=Redmond,ST=Washington,C=US

But now I get the error ORA-29024 (certificate validation error). Even after a restart of the Oracle Service.
I'll contact the support.
Many thanks for your tipps.

Regards

February 03, 2018 - 4:28 am UTC

Sorry we didnt get to a resolution for you :-(

I got it working with 365 on 12.2

Apex user, February 22, 2018 - 11:09 pm UTC

besides connection statement, the startSSL needs secure_host too, more like
UTL_SMTP.STARTTLS(c, secure_host=>'outlook.com');

also please remove outlook.com certificate, 12.2 doesn't like server certificate,

Best regards

February 23, 2018 - 12:41 am UTC

Awesome - thanks for getting back to us and sharing the information. It will no doubt help others.

Breadcrumb

Question and Answer