UTL_HTTP Without Using Certificate | sql1

Ever tried to run UTL_HTTP.BEGIN_REQUEST procedure passing an URL that points to a HTTPS (SSL) server?
That’s what happend to me recently as some procedures that have been working for nearly two years without a hitch suddenly stopped working. After reviewing my app logs it turned out that around mid august Oracle changed something in the way you access the OTN forums and now the connection is encrypted. As a result, my log was full of the following error messages:

ORA-29024: Certificate validation failure

This implies that the good old

UTL_HTTP.BEGIN_REQUEST

doesn’t work anymore unless you create a so-called

oracle wallet

containing the required SSL certificates (I beg you pardon should I say something technically questionable regarding SSL but the last thing I wished to be was a security protocol expert…).

How do we create this wallet? Piece of cake, just run owm (Oracle Wallet Manager) from the command line, may be it’s the same on Windows, I tried this only on Unix.

There is a little catch however: I am running on XE and XE doesn’t come with the Oracle Wallet Manager. Oops!

Fortunately things were not so bad as they looked. I could create a wallet on a local Oracle Server and move the two resulting files (ewallet.p12 and cwallet.sso) in the desired folders by means of FTP, changing the permissions so that the OS database owner (in my case the classic “oracle” user) could read the files.
Thereafter I just had to add a procedure call before invoking UTL_HTTP.BEGIN_REQUEST, as follows:

UTL_HTTP.SET_WALLET('file:/etc/oracle/owm'); -- path to the wallet in the db server

Now, one may ask why it works.

Well, it looks like that when you create this wallet, OWM stuffs some common use certificates in it for our convenience (you can see these default certificates when you are inside the OWM).

Note that I didn’t provide the second parameter to the procedure, that is the password, my understanding is that Oracle will be reading the read-only version of the wallet called cwallet.sso.  This gives me the advantage of not hard-cording the password in the procedure in clear text.

It is also worth noticing that file: prefix. For some reason my brain decided to ignore altogether this piece of information for at least 15 minutes, with the results described in a subsequent posting. So, to make it short, be sure to specify it in the string, otherwise it won’t work.

Another interesting fact is in that there are no restrictions as to where to put the wallet, the path can point anywhere as long as the database owner can read the file, so the path specified above does not represent anything special.

So, after completing these steps my procedures resumed working normally.

I can imagine however that in some cases this might not be sufficient but I ignore if you’ll get the same error or a new one. The default files created by OWM may contain valid certificates for some sites I am currently accessing but not for others. If that is the case, I imagine that you’ll need to import the correct certificates into the wallet and thereafter copy the files again across the servers. 

And finally the acknowledgements: special thanks to Marcelle Kratochvil who posted the relevant information and the valuable comments of the readers who completed the picture and allowed me to fix this problem really quickly.
It really looked scary initially.

See message translations for ORA-29024 and search additional resources.