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 - 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"
        <title>Qualys SSL Labs - Projects / SSL Server Test</title>

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.