Forum Stats

  • 3,769,814 Users
  • 2,253,026 Discussions
  • 7,875,214 Comments

Discussions

UTL_HTTP ORA-29024: Certificate validation failure

Christyxo
Christyxo Member Posts: 146 Silver Badge

I'm trying to make a call to a rest service using PLSQL from an Oracle 12c DB (locally installed), but I'm hitting the above certification error. I've spent a couple of days trying to resolve this through searches but so far I've not come up with an answer that works.

The call is to HTTPS so I require a certificate. I downloaded the certificate from the website and used Oracle Wallet Manager to create a new wallet in the location mentioned below with the password "Oracle123". I can reopen the wallet with this password so I know it's correct and wasn't a typo.

I have tried all multiple variations to downloading and inserting the certificates into the wallet. This includes exporting the cert in the 3 standard formats DER, Base64, and PCKS, and I have tried with only the Root, only the Intermediate, both Root and Intermediate, and the chain. I have the cert validation failure with each attempt.

Originally I used the 11g approach to creating ACLs at first, but after noting that many of the packages are deprecated, I have tried to stick with the new packages as below:

BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE
 (
   HOST => 'api.mavenlink.com'
 , ACE  => XS$ACE_TYPE
  (
    PRIVILEGE_LIST  => XS$NAME_LIST ( 'CONNECT' , 'RESOLVE' )
  , PRINCIPAL_NAME  => 'ODI_USER'
  , PRINCIPAL_TYPE  => XS_ACL.PTYPE_DB
  )
 ) ;
DBMS_NETWORK_ACL_ADMIN.APPEND_WALLET_ACE
(
  WALLET_PATH => 'file:D:\app\database\product\12.1.0\dbhome_1\BIN\wallets'
, ACE         => XS$ACE_TYPE
  (
    PRIVILEGE_LIST  => XS$NAME_LIST ( 'USE_CLIENT_CERTIFICATES' , 'USE_PASSWORDS' )
  , PRINCIPAL_NAME  => 'ODI_USER'
  , PRINCIPAL_TYPE  => XS_ACL.PTYPE_DB
  )
) ;
END ;
/

-- PL/SQL procedure successfully completed.

I do not know if I have supplied the correct value for host, but I have also tried variations on this including *.xxxxxx.com , *.com and *

USER_HOST_ACES and USER_HOST_WALLETS both look as I expect.

The PLSQL block used to make the call is:

DECLARE

REQ_CONTEXT  UTL_HTTP.REQUEST_CONTEXT_KEY ;
REQ          UTL_HTTP.REQ ;
RESP         UTL_HTTP.RESP ;
VAL          VARCHAR2 ( 2000 ) ;

BEGIN

REQ_CONTEXT := UTL_HTTP.CREATE_REQUEST_CONTEXT 
 (
   WALLET_PATH      => 'file:D:\app\database\product\12.1.0\dbhome_1\BIN\wallets'
 , WALLET_PASSWORD  => 'Oracle123'
 ) ;

REQ := UTL_HTTP.BEGIN_REQUEST
 (
   URL              => 'https://api.mavenlink.com/api/v1/custom_field_values?per_page=$1&page=$1'
 , METHOD           => 'GET'
 , HTTP_VERSION     => NULL
 , REQUEST_CONTEXT  => REQ_CONTEXT 
 ) ;

UTL_HTTP.SET_HEADER ( REQ , 'Authorization' , 'Bearer ?????????????????' ) ;
UTL_HTTP.SET_HEADER ( REQ , 'content-type' , 'application/json' ) ;

RESP := UTL_HTTP.GET_RESPONSE ( REQ ) ;

LOOP
 UTL_HTTP.READ_LINE ( RESP , VAL , TRUE ) ;
 DBMS_OUTPUT.PUT_LINE ( VAL ) ;
END LOOP ;

UTL_HTTP.END_RESPONSE ( RESP ) ;
UTL_HTTP.DESTROY_REQUEST_CONTEXT ( REQ_CONTEXT ) ;

EXCEPTION
 WHEN UTL_HTTP.END_OF_BODY
 THEN UTL_HTTP.END_RESPONSE ( RESP ) ;
END ;

Error report -
ORA-29273: HTTP request failed
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.UTL_HTTP", line 368
ORA-06512: at "SYS.UTL_HTTP", line 1118
ORA-06512: at line 15
29273. 00000 -  "HTTP request failed"
*Cause:    The UTL_HTTP package failed to execute the HTTP request.
*Action:   Use get_detailed_sqlerrm to check the detailed error message.
           Fix the error and retry the HTTP request.

The OS User has access to the wallet, and the password does not appear to be causing a problem.

I have previously been using PowerShell to connect to this API so I know service is correct.

Does anyone have any thoughts?

Answers

  • mpatzwahl
    mpatzwahl Member Posts: 269 Bronze Badge

    i had also a lot of Problems with this "Feature".

    My "Best Solution" so far. Use the Chrome Browser / Root Certificate / Default Format

    Delete "old" certificates in the wallet an Import this new one.

    I also use apex_web_service for requests:


    create or replace FUNCTION get_stock_quote

    (isin IN VARCHAR2,provider IN VARCHAR2 DEFAULT 'tradegate')

    RETURN CLOB IS 

    v_url VARCHAR2(512);

    BEGIN 

    IF upper(provider)='TRADEGATE' THEN v_url:='https://www.tradegate.de/refresh.php?isin=';

    ELSE

    RAISE_APPLICATION_ERROR(-20000,'Provider unknown. Try "Tradegate"');

    END IF;

    RETURN apex_web_service.make_rest_request(   

    p_url => v_url||isin,

    p_http_method => 'GET');

    END;

    /

    Good luck !

    Marco

  • User_TXCCP
    User_TXCCP Member Posts: 1 Green Ribbon

    My engineering team at https://valueinvesting.io/ and I have also faced the same issue before. mpatzwahl's answer was spot on. We did something very similar to what he mentioned to fix the problem. Hope Oracle will do something about this soon