I was supposed to do something else that involved using the package UTL_HTTP from the database accessing an URL over HTTPS. Instead I lost several hours troubleshooting ORA-29024: Certificate validation failure and ORA-28750: unknown error (Nice error the last one).
This is not the first time I’ve been through this. It is getting quite common that sites insist on using HTTPS, even if you access them using HTTP, the client will typically be redirected to HTTPS (port 443).
But first, a simple test case. If you want to access a URL from the package UTL_HTTP you first need to create an ACL. For this demo I’m using the database user OUGN. Run this as SYSTEM:
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'www.xml',
description => 'WWW ACL',
principal => 'OUGN',
is_grant => true,
privilege => 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'www.xml',
principal => 'OUGN',
is_grant => true,
privilege => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'www.xml',
host => '*');
commit;
END;
/
It may not be a good habit to open for the whole world, but this is a demo.
Then you can test it with:
select utl_http.request('ifconfig.co/ip') from dual;
That works nice on 11g and 12c and returns the IP address.
But if you try to access an URL starting with HTTPS you will get ORA-29024: Certificate validation failure, which is expected. The Oracle database needs to have the SSL certificate in its wallet to verify it.
By following this recipe from Oracle Base aka Tim Hall, you should be up and going in a few minutes.
I downloaded the certificate using the browser and transferred it to the database server, the file is called COMODO_RSA.cer in the code below.
This procedure works great on 11g and 12c, from the commandline where Oracle Home lies (put the cursor in the box below and scroll with arrow keys):
orapki wallet create -wallet $ORACLE_BASE/admin/$ORACLE_SID/wallet -pwd Secret42 -auto_login
orapki wallet add -wallet $ORACLE_BASE/admin/$ORACLE_SID/wallet -trusted_cert -cert /tmp/COMODO_RSA.cer -pwd Secret42
orapki wallet display -wallet $ORACLE_BASE/admin/$ORACLE_SID/wallet
The last command will obviously show the certificates; you know the feeling so far so good:
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject: OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject: CN=COMODO RSA Certification Authority,O=COMODO CA Limited,L=Salford,ST=Greater Manchester,C=GB
Subject: OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US
On 11g this actually works:
select utl_http.request('https://www.vegvesen.no/',NULL,'file:/u01/app/oracle/admin/RIO/wallet',NULL)
from dual;
But on 12c the same procedure results in ORA-28750: unknown error. And yes, there are changes in 12c. I’ve been through lots of documents on MOS without success.
I got so frustrated that I started a trial in the Oracle Cloud to test it, assuming that if this was caused by a missing patch the database in the sky would be duly patched. Same error.
I was thinking I should list some documents from MOS I had waded through, and entered again the terms utl_http ORA-28750 12c. Not sure why I had not seen it before, but I found Doc ID 2238096.1 How To Investigate And Troubleshoot SSL/TLS Issues on the Database And Client SQL Net Layer and stumbled upon Patch 26040483.
This patch actually solves the problem!
Heck, I even patched my cloud database, and it works too!
The moral of this post: If you are really upset with something, blog about it, you may find the solution before you get to the end.
And if you love patching the cloud won’t necessarily put an end to that.