https request in Oracle 18c : "ORA-29024: Certificate validation failure"

Question

I use Oracle 18C pl sql script.

I would like to make https requests.

So, i begin with http requests and it works. But, with https requests, i have an error : "Certificate validation failure".

I will explain below how I proceed in order to reach "https://www.ibm.com" for example.

I create a wallet using this powershell script :

Get-ChildItem -Path Z:\Documents\dev\plsql_http_request\wallets -Include *.* -File -Recurse | foreach { $_.Delete()}
orapki wallet create -wallet Z:\Documents\dev\plsql_http_request\wallets -pwd iciC29000 -auto_login
orapki wallet add -wallet Z:\Documents\dev\plsql_http_request\wallets -pwd iciC29000 -trusted_cert -cert 'Z:\Documents\dev\plsql_http_request\certificates\ibm-1-root.pem'
orapki wallet add -wallet Z:\Documents\dev\plsql_http_request\wallets -pwd iciC29000 -trusted_cert -cert 'Z:\Documents\dev\plsql_http_request\certificates\ibm-2-intermediate.pem'
orapki wallet add -wallet Z:\Documents\dev\plsql_http_request\wallets -pwd iciC29000 -trusted_cert -cert 'Z:\Documents\dev\plsql_http_request\certificates\ibm-3-user.pem'
orapki wallet display -wallet Z:\Documents\dev\plsql_http_request\wallets -pwd iciC29000

Certificates are in PEM base64 format.

I have this output from this command orapki wallet display -wallet Z:\Documents\dev\plsql_http_request\wallets -pwd iciC29000 :

Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        CN=www.ibm.com,O=International Business Machines Corporation,L=Armonk,ST=NEW YORK,C=US
Subject:        CN=DigiCert Global Root CA,OU=www.digicert.com,O=DigiCert Inc,C=US
Subject:        CN=DigiCert TLS RSA SHA256 2020 CA1,O=DigiCert Inc,C=US

Then, I modify rights of the wallet file : file permissions

Then, i execute this pl/sql script :

set serveroutput on size 30000;
declare
    v_req       utl_http.req;
    v_res       utl_http.resp;
    v_buffer    varchar2(4000); 
begin
    UTL_HTTP.set_detailed_excp_support ( TRUE );  
    utl_http.set_wallet('file:Z:\Documents\dev\plsql_http_request\wallets\','iciC29000');
    v_req := utl_http.begin_request('https://www.ibm.com');
    v_res := utl_http.get_response(v_req);
    utl_http.read_text(v_res, v_buffer, NULL);
    utl_http.end_response(v_res);
    dbms_output.put_line(v_res.status_code);
    dbms_output.put_line(v_res.reason_phrase);
    dbms_output.put_line(v_buffer);
end;

And now, i'm stucked with these errors :

Error report -
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1148
ORA-06512: at line 15
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.

Thanks a lot for your help.

score 0 · Answer 1 · answered Jun 21 '23 at 08:00

I recommend to use tcpdump, sniff the whole TCP session, open it in Wireshark and you will see. Oracle 18c is relatively old, there might be a problem with some unsupported flag in certificate, unsupported cipher in TLS negotiation or unsupported TLS version.

Also you mention, you are on Oracle 18c, but you do not mention patch level. Previous Oracle databases had dedicated patchsets to address these issues.

https request in Oracle 18c : "ORA-29024: Certificate validation failure"

1 Answers1