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.

Checking the MIME type of an URL (with Apex)

This posting is to announce that a new MIME type checking page is available at my company's site:

http://www.yocoya.com/pls/apex/f?p=yocoya:mime:0:en

The need for this tool arose after discovering that a site i had bookmarked for doing the same thing had been discontinued or moved to another domain, so i thought it could be a good exercise to create a page with the help of Oracle Application Express and the built-in UTL_HTTP packaged procedures.
At the same time, i wanted to understand better the Oracle Apex HTTP redirect behavior so i came up with this tool addressing both issues.
In the end it was quite entertaining to write the code and i finally got a clear picture of the redirect problem.

But why on earth should one be concerned about the MIME type after all?

Knowing the MIME type of a document retrieved from the web may be necessary when diagnosing problems like a browser that fails to display a document inline, but attempts to download it as a binary file. This kind of situations is often caused by an improper setting of the MIME type in the web server configuration.
A typical example is with certain sites returning SVG documents with an invalid MIME type like application/svg-xml whereas the correct MIME type should be application/svg+xml (note the plus sign instead of the minus sign).
Another frequent hit is a wrong WSDL (Web Service Definition Language) MIME type, so instead of getting application/wsdl+xml, you get text/plain.

Coming to the underlying implementation, if you need to check for the MIME type of an URL programmatically, the functionality will be a refinement of the following basic expression:

HttpUriType.createUri('http://wap.priceblog.it').getContentType();

text/vnd.wap.wml; charset=UTF-8

caveat:
due to the http redirects, if you specify an URL pointing to an Oracle Application Express application using the PL/SQL expression above, you may get:

HttpUriType.createUri('http://www.yocoya.com').getContentType();

ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1231
ORA-12545: Connect failed because target host or object does not exist
ORA-06512: at "SYS.HTTPURITYPE", line 108

The quick workaround consists in supplying a complete apex URL including a valid session id (the big number after the second colon), that is something like:

begin
dbms_output.put_line(
HttpUriType.createUri(
'http://www.yocoya.com/pls/apex/f?p=yocoya:oerr:1547750763260356').getContentType());
end;
/

text/html; charset=UTF-8

Note that apex sessions expire over time, so you may get an error if you execute the program above as it is, you must use a "fresh" session ID.

Finding out the actual session id and take you to the final page or document is the primary task of the tool i made available on-line.
Clearly there is something more going on behind the scenes, because of the Apex redirect problem mentioned earlier and incidentally i found out that the built-in method called UTL_HTTP.SET_FOLLOW_REDIRECT(n) doesn't work correctly for n greater than zero when talking to an Apex application, it seems to me that it doesn't cope with relative redirects.
That's why i had to come up with this custom procedure.

So, in case you need to check out the MIME type of a document available online or watch what goes on behind the scenes when opening a public page served by Apex, this tool can help.

Or so i guess :-)

The page is a beta version, there could be situations that i didn't think of, so i strongly encourage to report any issues you may encounter or submit enhancement requests.
The errors returned by the UTL_HTTP package are automatically converted into web links to the yocoya's oracle error translation online tool that i introduced a while ago, providing error message translations in 30 languages and quick links to the online Oracle documentation of different releases and, last but not least, oraclequirks.com itself, just in case, however i haven't had the time to publish anything on these specific errors yet, so may be i'll post something in the next weeks.