vrijdag 7 november 2014

"ORA-29024: Certificate validation failure" when calling https-site with utl_http

Problem when using UTL_HTTP for HTTPS-sites

When using the UTL_HTTP-package for accessing https-sites you might get the error

ORA-29024: Certificate validation failure – message

SQL> select utl_http.request ('https://www.ssllabs.com/ssltest');
select utl_http.request ('https://www.ssllabs.com/ssltest') from dual
       *
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-29024: Certificate validation failure
ORA-06512: at line 1

To avoid this you need to configure an Oracle Wallet :

Retrieve the certificate from the site. In Chrome click on the lock-icon :


A popup-screen will appear. Click on the Connection-tab and then on ‘Certificate information’


On the certificate-screen select the Details-tab and click on the ‘Copy to File…’ button


The ‘Certificate Export Wizard’ will start up. Click ‘Next’


Select ‘Cryptographic Message Syntax .. – PKCS #7’. The format might depend on the site you are accessing. 


Select where to store the certificate and click ‘Next’. 




Copy the certificate-file to the server hosting the Oracle database.
Next we need to create a wallet :

orapki wallet create -wallet /u01/app/oracle/admin/DB1/wallet -pwd Password -auto_login

and add the certificate to the wallet :

oracle [ /u01/app/oracle/admin/DB1/wallet ]$ orapki wallet add -wallet /u01/app/oracle/admin/DB1/wallet -trusted_cert -cert /tmp/ssllabs.p7b -pwd Password
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

oracle [ /u01/app/oracle/admin/DB1/wallet ]$

Now we can test if the we can access the site :

SQL>  select utl_http.request ('https://www.ssllabs.com/ssltest',NULL,'file:/u01/app/oracle/admin/DB1/wallet','Password') from dual;

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
        <title>Qualys SSL Labs - Projects / SSL Server Test</title>
 …
SQL>

And it works :-)


1 opmerking:

  1. I applied the same on all transactions.
    I get the following error.

    ORA-29273: HTTP isteği başarısız oldu
    ORA-06512: konum "SYS.UTL_HTTP", satır 1722
    ORA-29248: wallet açmak tanınmayan bir WRL kullanıldı
    ORA-06512: konum satır 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.

    BeantwoordenVerwijderen