Hi,
We are trying to access some webservices via Oracle database (19c and 11g) using user certificate which we've got from some CA in .pfx format (containing USER cert, TRUSTED certs & private key).
Fist of all if this certificate is imported into browser I can access webservices successfully - this proves that .pfx certificate is valid & it works.
But I am having problems accessing this same webservice via Oracle database using functions SET_WALLET and then UTL_HTTP.REQUEST.
I've tired creating wallets from this .pfx using at least X different methods which none of them work..
To list a few:
1) rename pfx to p12
mv user_cert.pfx ewallet.p12
-- wallet is displayed correctly (I can see user & trusted certificates)
orapki wallet display -wallet ./
--trying to access the webservice fails:
select UTL_HTTP.REQUEST(url => 'https://webservice.domain/path',wallet_path=> 'file:/wallet_path',wallet_password=> 'wallet_pass') FROM DUAL;
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1530
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1470
ORA-06512: at line 1
2) dissect pfx into separate files: user_cert.cer, trusted_certs.cer, privatekey.cer & create new wallet using openssl
openssl pkcs12 -inkey privatekey.cer -in usercert.cer -export -out USER_CERT.pfx
mv USER_CERT.pfx ewallet.p12
orapki wallet add -wallet ./ -trusted_cert -cert rusted_certs
--I get the same error..ORA-29024: Certificate validation failure
3) CONVERT from PFX --> Wallet:
orapki wallet create -wallet./ -pwd 'wallet_pass'
orapki wallet import_pkcs12 -wallet ./ -pkcs12file user_cert.pfx -pkcs12pwd 'wallet_pass'
--I get the same error..ORA-29024: Certificate validation failure
..in all these cases when wallet created IS displayed (orapki wallet display -wallet ./ -pwd 'wallet_pass') correctly - I can see "User Certificates" & "Trusted Certificates" listed as expected.
I am testing with:
select UTL_HTTP.REQUEST(url => 'https://webservice.domain/path',wallet_path=> 'file:/wallet_path',wallet_password=> 'wallet_pass') FROM DUAL;
OR
EXEC UTL_HTTP.set_wallet('file:/wallet_path', 'wallet_pass');
EXEC show_html_from_url('https://webservice.domain/path');
Where is the problem here? How can I make it work?
Is there a problem with my tests? Is usage of UTL_HTTP correct?
Btw..I have some other wallet for accessing "ordinary" HTTPS pages where I have only trusted certificates and there usage of wallet work without any issues.
There must be some additional wizardry when using USER certificates but I can not figure out which.
Thanks for any help.