ORA-29024: Certificate validation failure - again, with a twist

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

I love when a perfectly working application suddenly begins complaining about:

ORA-29024: Certificate validation failure 

Last year it took me a while to fix it as I had to figure out how to cope with the absence of a wallet manager on Oracle XE.
Now things were different.

I began searching OTN forums for other poor souls enduring the same hardships but it looks like I was the only one.
Curious to see how often this happens to me.

Well, I don't want to make this story longer than necessary, because initially I was misled by the fact that one of these default trusted certificates was expired and for some reason I thought it was the cause of the problem, but it wasn't.
It turns out that at Oracle.com yesterday someone must have changed the SSL certification authority or whatever it's called and as a result the trusted certificates installed with Oracle 11gR1 don't work any more when accessing servers in the oracle.com domain via HTTPS from within PL/SQL procedures (using UTL_HTTP).
After a while I realized that if I can still access them using Firefox it means that the trusted certificates installed with Firefox must be sufficient.

Firefox's certificate viewer shows Oracle's certificate chain

After inspecting the trusted certificate chain being used by clicking on the lock symbol in the address bar of Firefox, I exported each and every certificate in the chain in X.509 format (as single certificates, not the chain) and later I imported them in the wallet using the utility orapki (this action must be carried out on a full fledged Oracle Database Server edition, NOT XE where orapki is not included), starting with the certificate that lies on top and working bottomwards.

orapki wallet add -wallet /etc/oracle/owm -trusted_cert -cert CAroot.cer
orapki wallet add -wallet /etc/oracle/owm -trusted_cert -cert COMODO.cer
orapki wallet add -wallet /etc/oracle/owm -trusted_cert -cert oracle.com.cer

After updating cwallet.sso and ewallet.p12 and bouncing the database, my services resumed working correctly.

So far so good, in the end it took me just 4 hours to become an improvised "expert" of trusted certificate chains replacements.
Funnily enough, today it looks like the access to www.oracle.com doesn't need anymore HTTPS (I swear yesterday they did!), while forums still do.

I'd love to know what happened yesterday behind the curtains.

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

ORA-29024: Certificate validation failure

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

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.