- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 73 Insurance
- 537.3K On-Premises Infrastructure
- 138.6K Analytics Software
- 38.6K Application Development Software
- 6K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.2K Infrastructure Software
- 105.3K Integration
- 41.6K Security Software
UTL_HTTP ORA-29024: Certificate validation failure
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?