Accessing SSL encrypted websites using UTL_HTTP and Oracle Wallet Manager

Introduction
If you have used the UTL_HTTP package in PL/SQL to call upon external web pages or services, you might have seen following error message come by:
ORA-29273: HTTP request failed
ORA-06512: at “SYS.UTL_HTTP”, line 1130
ORA-29024: Certificate validation failure

It indicates that the web site you are trying to access is in fact SSL encrypted and requires a valid certificate to read.
Most modern browsers download this automatically when visiting any encrypted page, but to do this in a PL/SQL procedure, a couple of manual steps need to be taken.

This is where Oracle Wallet Manager comes in. It is shipped with the DBMS software and can typically be found in the $ORACLE_HOME/bin folder.
What this program does, is to facilitate the process of storing certificates in a single file in PKCS #12 format, called a Wallet.
Next, it suffices to simply add a reference to the wallet in your PL/SQL code to be able to use these certificates when accessing SSL encrypted content.

Sample case
Let’s take the following piece of PL/SQL code:

DECLARE
 lo_req  UTL_HTTP.req;
 lo_resp UTL_HTTP.resp;
BEGIN
 lo_req := UTL_HTTP.begin_request('http://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;
/

This will output the status code “200”. This implies the page returned HTTP 200: OK, indicating the request succeeded.

If you change the URL from http://www.google.be to https://www.google.be, the same command would raise exception “ORA-29024 Certificate validation failure” as mentioned earlier.

Wallet Manager
From the command line, start the wallet manager by simply entering ‘owm’ after having set the correct Oracle environment parameters.
In Windows the program can be found in the start menu under /<oracle version folder>/Integrated Management Tools/Wallet Manager.

Wallet_01
First, let’s create a new wallet.
From the menu, select Wallet, New…

A prompt is shown to enter a password for the Wallet. For this example, we’ll use “test1234”.

Wallet_02

After entering the password, a prompt asks to create a new certificate request, which we will not do for now.

By default, a number of trusted certificates are created within the wallet. These can be seen in the overview screen.

Wallet_03
Now, let’s focus on our google.be website.

First thing to do, is to export the actual certificate from this website, together with the rest of the trust chain. The easiest way to do this is by using a regular browser application. Following screenshots are from Firefox, but a similar approach can be taken with Internet explorer or Chrome.

Go to the website, and click on the padlock in front of the URL.
Wallet_04

Click on “More information” and then “View certificate”. In the Details tab, export each of the certificates in the hierarchy (3 in this case).
Wallet_05

Then go to the Wallet manager, and import all 3 trusted certificates.
Wallet_06

Finally, save the wallet to a desired location. Make sure it’s a folder that can be accessed by the Oracle user, and is preferably not accessible by unauthorized users!
In this example, /home/oracle/Wallet/ is chosen.

Wallet_07

Wallet_08

Now we can return to our SQL*plus session. One extra statement should be added to the script:

UTL_HTTP.SET_WALLET (‘file:<path to the wallet folder, don’t include the filename!>’,<the wallet password>);

This statement should be executed before the begin_request step.

Here’s the full output of the script:

[oracle@myorcl12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Tue Aug 27 14:11:43 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2    lo_req  UTL_HTTP.req;
3    lo_resp UTL_HTTP.resp;
4  BEGIN
5    UTL_HTTP.SET_WALLET ('file:/home/oracle/Wallet/','test1234');
6    lo_req := UTL_HTTP.begin_request('https://www.google.com');
7    lo_resp := UTL_HTTP.get_response(lo_req);
8    dbms_output.put_line(lo_resp.status_code);
9    UTL_HTTP.end_response(lo_resp);
10  END;
11  /

200
PL/SQL procedure successfully completed.
SQL>

In a follow up post, I will explain how to achieve the same result using the orapki command line utility instead of Wallet manager.

About these ads

2 thoughts on “Accessing SSL encrypted websites using UTL_HTTP and Oracle Wallet Manager

  1. Pingback: Accessing SSL encrypted websites using UTL_HTTP and ORAPKI command line utility | iAdvise blog

  2. Pingback: iAdvise | Accessing SSL encrypted websites using UTL_HTTP and ORAPKI command line utility

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s