Forum Stats

  • 3,769,482 Users
  • 2,252,969 Discussions
  • 7,875,047 Comments

Discussions

Using utl_http & wallets on 12c: certificate validation failure

-Tony-Reed-
-Tony-Reed- Member Posts: 3
edited Oct 23, 2013 12:49AM in SQL & PL/SQL

Hi all,

Hope someone can spot what I'm doing wrong as I'm going bald from this.

I have used utl_http & wallets to call https on 11gR1 without much trouble, but our new 12.1 installation is causing me a lot of grief.


I have tried importing the trusted certificate using both oracle wallet manager, and command line, without any success.
I know that oracle can be picky as to caching the wallet, so I have tried multiple new sessions without any luck.


I have downloaded the three neccessary certificates for *.presstogo.com, Geotrust SSL CA & Geotrust Global CA.

The command-line version of my building the wallet is  as follows:

orapki wallet create -wallet /oracle/product/12.0.1/owm/wallets/test1237 -pwd test=1237 -auto_login
orapki wallet add -wallet /oracle/product/12.0.1/owm/wallets/test1237 -trusted_cert -cert "*.presstogo.com" -pwd test=1237
orapki wallet add -wallet /oracle/product/12.0.1/owm/wallets/test1237 -trusted_cert -cert "GeoTrust SSL CA" -pwd test=1237
orapki wallet add -wallet /oracle/product/12.0.1/owm/wallets/test1237 -trusted_cert -cert "Geotrust Global CA" -pwd test=1237

orapki wallet display -wallet /oracle/product/12.0.1/owm/wallets/test1237 Oracle PKI Tool : Version 12.1.0.1 Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved. Requested Certificates: User Certificates: Trusted Certificates: Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US Subject:        CN=GeoTrust SSL CA,O=GeoTrust\, Inc.,C=US Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US Subject:        CN=GeoTrust Global CA,O=GeoTrust Inc.,C=US Subject:        CN=*.presstogo.com,OU=IT,O=Press to go AS,L=Oslo,ST=Norway,C=NO,SERIAL_NUM=SJYpOHrRdCDHE8KZ6dRFGMJthOjs7-v3

Ok, lets test this. Login to sqlplus and run the following:

declare
    lo_req    utl_http.req;
    lo_resp   utl_http.resp;
begin
    utl_http.set_detailed_excp_support ( true );
    utl_http.set_wallet ( 'file:/oracle/product/12.0.1/owm/wallets/test1237', 'test=1237');
    lo_req := utl_http.begin_request ( 'https://production.presstogo.com/mars/hello' );
    lo_resp := utl_http.get_response ( lo_req );
    -- A successfull request would have the status code "200". 
    dbms_output.put_line ( lo_resp.status_code );     utl_http.end_response ( lo_resp ); exception   when others then 
    utl_http.end_response ( lo_resp );     raise;
end;

DECLARE

*

ERROR at line 1:

ORA-29273: HTTP request failed

ORA-06512: at "SYS.UTL_HTTP", line 1130

ORA-29024: Certificate validation failure

ORA-06512: at line 6

For the record, It is worth noting that the following does work:

declare
    lo_req    utl_http.req;
    lo_resp   utl_http.resp;
begin
    utl_http.set_wallet ( 'file:/oracle/product/12.0.1/owm/wallets/test1237', 'test=1237');
    lo_req := utl_http.begin_request ( 'https://www.google.be' );
    lo_resp := utl_http.get_response ( lo_req );
    dbms_output.put_line ( lo_resp.status_code );
    utl_http.end_response ( lo_resp );
end;
/



Help me Obi-Wan, you're my only hope.

Tagged:
949766Gunther Pippèrr

Best Answer

  • -Tony-Reed-
    -Tony-Reed- Member Posts: 3
    Accepted Answer

    Answering my own question for the benefit of others.

    According to Oracle Support only the certificate chain should be imported, not the end site certificate. In the example I used above, only import the following certificates into the wallet:

    Geotrust SSL CA & Geotrust Global CA

    Do not import the *.presstogo.com certificate

    To quote Oracle support:

    The reason that the select is failing in 12c is that 12c does not want to see the user cert in the wallet as a trusted cert.

    This was apparently not an issue in previous versions but removing that cert from the wallet fixed the issue here.

    This contradicts all information I have found online regarding the use of utl_http to connect to Https sites, and confused the hell out of me.

    Hopefully this will help others in my situation.

    949766Gunther Pippèrr

Answers

  • -Tony-Reed-
    -Tony-Reed- Member Posts: 3
    Accepted Answer

    Answering my own question for the benefit of others.

    According to Oracle Support only the certificate chain should be imported, not the end site certificate. In the example I used above, only import the following certificates into the wallet:

    Geotrust SSL CA & Geotrust Global CA

    Do not import the *.presstogo.com certificate

    To quote Oracle support:

    The reason that the select is failing in 12c is that 12c does not want to see the user cert in the wallet as a trusted cert.

    This was apparently not an issue in previous versions but removing that cert from the wallet fixed the issue here.

    This contradicts all information I have found online regarding the use of utl_http to connect to Https sites, and confused the hell out of me.

    Hopefully this will help others in my situation.

    949766Gunther Pippèrr
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,595 Red Diamond

    Appreciate the feedback Tony. It also contradicts what I'm used to for 11g - and that just works fine.

    Pretty sure I will forgot next week all about the solution you've posted and stumble across it via google in the future.

This discussion has been closed.