oracle - ORA-29024: Certificate validation failure - Apex and HTTPS

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

ORDS standalone is 19.1.0.r0921545

I did the tasks to configure an Apex Social Sign In to Microsoft AAD without almost any issue:

I created the authentication method in Apex.

I register my application and get the web credentials in Azure.

I created a wallet in my database with the root CA Microsoft certificates and configured the instance settings to usee that wallet.

My wallet in the database server contains the property auto_login to avoid using passwords.

I created the ACEs entries to allow connection to the login.microsoftonline.com in the port 443

Although it is not important for the purpose of the question itself and the error that is producing, just comment that I configured the wallet settings in the internal workspace in order to provide access to the wallet to the apex applications.

For some weeks the process was working fine, I was having a perfect Single Sing on mechanism for all my apex applications in the different workspaces. However, since some days ago, I am getting always the same error:

ORA-29024: Certificate validation failure

After some digging I realise that someone has configured a PROXY for outgoing traffic. Before even trying in Apex, I tried in SQL using APEX_WEB_SERVICE

Request with proxy settings to login.microsoftonline.com

select apex_web_service.make_rest_request(
    p_url         => 'https://login.microsoftonline.com',
    p_http_method => 'GET',
    p_wallet_path => 'file:/home/oracle/wallet',
    p_wallet_pwd => 'MyPassword' ,
    p_proxy_override => 'http://myproxy:myport'
  7  ) from dual;
ERROR:
ORA-29273: HTTP request failed
ORA-06512: at "APEX_190100.WWV_FLOW_WEB_SERVICES", line 1035
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1148
ORA-06512: at "APEX_190100.WWV_FLOW_WEB_SERVICES", line 934
ORA-06512: at "APEX_190100.WWV_FLOW_WEB_SERVICES", line 1580
ORA-06512: at "APEX_190100.WWV_FLOW_WEBSERVICES_API", line 408
ORA-06512: at line 1
Request without proxy settings, just to see if I can get there
SQL> select apex_web_service.make_rest_request(
  2      p_url         => 'https://login.microsoftonline.com',
  3      p_http_method => 'GET',
  4      p_wallet_path => 'file:/home/oracle/wallet'
  5* ) from dual
SQL> /
ERROR:
ORA-29273: HTTP request failed
ORA-06512: at "APEX_190100.WWV_FLOW_WEB_SERVICES", line 1035
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1148
ORA-06512: at "APEX_190100.WWV_FLOW_WEB_SERVICES", line 934
ORA-06512: at "APEX_190100.WWV_FLOW_WEB_SERVICES", line 1580
ORA-06512: at "APEX_190100.WWV_FLOW_WEBSERVICES_API", line 408
ORA-06512: at line 1
Request to google using Proxy settings
select apex_web_service.make_rest_request(
    p_url         => 'https://google.com',
    p_http_method => 'GET',
    p_wallet_path => 'file:/home/oracle/wallet',
    p_wallet_pwd => 'MyPassword' ,
  6      p_proxy_override => 'http://myproxy:myport'
  7  ) from dual ;
ERROR:
ORA-29273: HTTP request failed
ORA-06512: at "APEX_190100.WWV_FLOW_WEB_SERVICES", line 1035
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1148
ORA-06512: at "APEX_190100.WWV_FLOW_WEB_SERVICES", line 934
ORA-06512: at "APEX_190100.WWV_FLOW_WEB_SERVICES", line 1580
ORA-06512: at "APEX_190100.WWV_FLOW_WEBSERVICES_API", line 408
ORA-06512: at line 1
Request to google without proxy settings
SQL> select apex_web_service.make_rest_request(
  2      p_url         => 'https://google.com',
  3      p_http_method => 'GET',
  4      p_wallet_path => 'file:/home/oracle/wallet'
  5* ) from dual
SQL> /
ERROR:
ORA-29273: HTTP request failed
ORA-06512: at "APEX_190100.WWV_FLOW_WEB_SERVICES", line 1035
ORA-12535: TNS:operation timed out
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1148
ORA-06512: at "APEX_190100.WWV_FLOW_WEB_SERVICES", line 934
ORA-06512: at "APEX_190100.WWV_FLOW_WEB_SERVICES", line 1580
ORA-06512: at "APEX_190100.WWV_FLOW_WEBSERVICES_API", line 408
ORA-06512: at line 1
My questions are the following:
It is a network problem or a proxy issue regarding inbound/outbound
traffic ? I can reach Microsoft but not Google in the port 443 when I don't specify proxy.
Why am I getting invalid certificate when it has nothing to do with
the certificates ?
How can I setup my APEX to use authentication on Azure or any other
provider for that matter when I have a proxy in the middle ?
As I use ORDS standalone, am I allow to keep using it or I need a
reverse proxy with Tomcat ?
I tried to configure the ACE to use HTTP_PROXY in the ports by running
begin
  sys.dbms_network_acl_admin.append_host_ace(
    host        => 'myproxyserver'
   ,lower_port  => 8080
   ,upper_port  => 8080
   ,ace         => xs$ace_type(
      privilege_list     => xs$name_list('http_proxy')
     ,granted            => true
     ,principal_name     => 'MY_PRINCIPAL'
     ,principal_type     => XS_ACL.PTYPE_DB
Even I grant to the ACE privileges over the wallet
SET SERVEROUTPUT ON
BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_WALLET_ACE
    WALLET_PATH => 'file:/home/oracle/wallet',
    ACE => XS$ACE_TYPE(
                        PRIVILEGE_LIST => XS$NAME_LIST('use_passwords','use_client_certificates'),
                        PRINCIPAL_NAME => 'MY_PRINCIPAL',
                        PRINCIPAL_TYPE => XS_ACL.PTYPE_DB
EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Error while configuring ACL for wallet: '|| SQLERRM);
but I am still getting the same error all over.
Any help would be appreciated!
Thank you
                how that java code will know where the certificates are ? the purpose of using APEX_WEB_SERVICE is that I can tell Oracle where my certificates are stored.
– Roberto Hernandez
                Jul 13, 2020 at 12:29
                Java has default keystore. But you can add your own.   System.setProperty("javax.net.ssl.trustStore", "/tmp/cacerts.jks");  System.setProperty("javax.net.ssl.trustStorePassword", "changeit");  Also can be set using DBMS_JAVA package
– Ivan Dubashinskii
                Jul 14, 2020 at 7:12
Thank you to all who post answers, but finally, after struggling for a while, I found the root cause. Actually Oracle was right after all, as Microsoft has changed the way the authentication is handled, either you are using Oauth2 or OpenID, when you use Office365 and Azure Active Directory.
In this case, my organisation is using Office 365 and at the beginning was enough with importing the PKI certificates from :
https://www.microsoft.com/pki/mscorp/cps/default.htm
After a change done in Azure Active Directory (AAD), you now need also the Global Sign certificates from office.com
I hope it clarifies to other users who got in the same problem trying to authenticate with Azure Active Directory using Apex Social sign in.
You can download the certificates directly from office365.com
After adding the new two certificates to the wallet, you can now enter without issues:
select apex_web_service.make_rest_request(
    p_url         => 'https://login.microsoftonline.com',
    p_http_method => 'GET',
  4      p_wallet_path => 'file:/home/oracle/wallet' ) from dual ;
APEX_WEB_SERVICE.MAKE_REST_REQUEST(P_URL=>'HTTPS://LOGIN.MICROSOFTONLINE.COM',P_
--------------------------------------------------------------------------------
<!DOCTYP
In my understanding,you will need to do following(in addition to what you did) :
login to Apex as administrator
From settings, go to 'Wallet'
Add Wallet path(absolute path with prefix 'file://' and password you used for creating wallet
Now, your problem should be solved.
                @RobertoHernandez Thanks for clarification, Since you have not mentioned about wallet setting, I assumed it wasnt done. :-)
– Shashi
                Jul 22, 2020 at 15:08
        Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.