ORACLE, UTL_HTTP and SSL

Question

I try to reach a WebService provide by a secured site with a TLS 1.2 certificate encrypted that i exported and add in a wallet.

First i try to reach the site with the package UTL_HTTP.request on a 11.2.0.1.0 ORACLE Database but i have the ORA-28857 SSL error unknown message.

I try the same on a 12.1.0.1.0 ORACLE Database but i have the ORA-29024 message.

So, i searched on the web and find everything and nothing about the subject.....

Here is what i did:

First: I exported the certificate from Internet Explorer with the PKCS #7 (.p7b) format (Chains included)

then, i create a wallet with the orapki utility

 orapki wallet create -wallet e:\wallet -pwd <pwd>

then i add my certificate

 orapki wallet add -wallet e:\wallet -trusted_cert -cert e:\certificats\<cert file> -pwd <pwd>

and i try to reach the secured site

SELECT UTL_HTTP.REQUEST('https://<secured site>.com',null,'file:E:\wallet','<pwd>') 
  FROM dual;

and i have the message:

ORA-29273: échec de demande HTTP ORA-06512: à "SYS.UTL_HTTP",
ligne 1722 ORA-28857: Erreur SSL inconnue ORA-06512: à ligne 1
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. 

I tried to create ACLs:

BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
        acl => 'utl_http.xml',
        description => 'Test ACL',
        principal => '<user>',
        is_grant => TRUE,
        privilege => 'connect',
        start_date => null,
        end_date => null
    );
END;
/

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
        acl         => 'utl_http.xml',
        principal   => '<user>',
        is_grant    =>  TRUE,
        privilege   => 'use-client-certificates',
                start_date => null,
                end_date => null);
END;
/

BEGIN
    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
       acl => 'utl_http.xml',
       host => '<secured site>',
       lower_port => 1,
       upper_port => 9999);
END;
/

BEGIN
    DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL(
       acl => 'utl_http.xml',
       wallet_path => 'file:E:\wallet');
END;
/

(I m not sure about usefulness of all but I'm ready to do everything to make that work ^^)

and i try to reach the secured site

SELECT UTL_HTTP.REQUEST('https://<secured site>.com',null,'file:E:\wallet','<pwd>') 
  FROM dual;

and i have the message:

Rapport d’erreur : ORA-29273: échec de demande HTTP ORA-06512: à
"SYS.UTL_HTTP", ligne 1130 ORA-29024: Echec de validation de
certificat ORA-06512: à ligne 10
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. 

i read that Oracle 11 have problems withe TLS 1.2 encrypted certificate so i tried with an Oracle 12 (Same ways to create Wallet and ACL)

I have the message:

Rapport d’erreur : ORA-29273: échec de demande HTTP ORA-06512: à
"SYS.UTL_HTTP", ligne 1130 ORA-29024: Echec de validation de
certificat ORA-06512: à ligne 10
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.

Hope I was clear in my explanations

I try to know what to do to reach a secure site by a certificate based on the certificate

Thank you for your much needed support

Best regards

Answer 1

May be I am too late, but I caught same issues and found some answers.

Oracle Database earlier than 11.2.0.3 does not support SHA­2 SSL-standard, for example we cannot connect google from 11.2.0.1.

When use 12c - try to remove end certificate of chain from wallet. (I found this answer here: Using utl_http & wallets on 12c: certificate validation failure )

Answer 2

An Oracle wallet is in PKCS12 format. You can't use a PKCS7 formatted certificate inside an Oracle wallet. You want to use the "Base-64 encoded X.509 (.CER)" option instead. You must also get each certificate in the chain for the certificate of the site to which you want to connect. Those will be loaded into the Trusted Certificates section of the wallet.

There are good detailed instructions at this page:

UTL_HTTP and SSL(HTTPS) Using Oracle Wallets