ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1720 ORA-29024: Certificate valida
[
message #675199
]
Thu, 14 March 2019 08:49
select utl_http.request ('https:\\www.oracle.com\index.html',NULL,'file:d:\appl\fiskalizacija\arhiva','xxx') FROM DUAL;
return
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1720
ORA-29024: Certificate validation failure
What I done.
On server using Oracle Wallet 12.1.0.1 I import three certifikate using chrome browser
First : DigiCert Global Root CA
Second : DigiCert SHA2 secure Servers CA
Third :www-cs-01.oracle.com
I am sure that path for ewallet in select is OK.
I am sure that password for ewallet is OK.
What am I doing wrong ?
Re: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1720 ORA-29024: Certificate valida
[
message #675200
is a reply to
message #675199
]
Thu, 14 March 2019 09:08
Could be many things, but one clear problem is that the hostname in the URL (www.oracle.com) doesn't match the name in the certificate (www-cs-01.oracle.com). When you upgrade to 12.2, you'll find the utl_http.request and many other procedures have another argument:
FUNCTION REQUEST RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
URL VARCHAR2 IN
PROXY VARCHAR2 IN DEFAULT
WALLET_PATH VARCHAR2 IN DEFAULT
WALLET_PASSWORD VARCHAR2 IN DEFAULT
HTTPS_HOST VARCHAR2 IN DEFAULT
See that last one? Setting that to the name in the certificate fixes the problem.
Re: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1720 ORA-29024: Certificate valida
[
message #675208
is a reply to
message #675204
]
Fri, 15 March 2019 01:45
@John .. forms are not a problem since this is pure pl/sql from database (there are another user without forms and on same database version)
@dwatkins
I am really beginer in part of security but used wallet before on previous database version (and od course different kind of certificates) and never have such problem.
Would you be more precision which certificate should I left in wallet ?
On www.oracle.com are three certifacate in chain :
DigiCert Global Root CA
Digicert SHA2 Secure Server CA
www-cs-01.oracle.com
I try all combination in wallet but all lead to same error
Wallet with only DigiCert Global Root CA --> ORA-29024: Certificate validation failure
Wallet with DigiCert Global Root CA and Digicert SHA2 Secure Server CA --> ORA-29024: Certificate validation failure
Wallet with all three --> ORA-29024: Certificate validation failure
Wallet with only www-cs-01.oracle.com --> ORA-29024: Certificate validation failure
Attachment:
untitled.png
(Size: 18.68KB, Downloaded 1190 times)
Re: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1720 ORA-29024: Certificate valida
[
message #675211
is a reply to
message #675210
]
Fri, 15 March 2019 02:52
...following on from John, but with access control
$ orapki wallet create -wallet test -pwd nothing_critical -auto_login
$ orapki wallet add -wallet test -cert digicert-root.cer -trusted_cert -pwd nothing_critical
$ orapki wallet add -wallet test -cert digicert-intermediate.cer -trusted_cert -pwd nothing_critical
$ orapki wallet display -wallet test
Oracle PKI Tool : Version 12.1.0.2
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject: CN=DigiCert SHA2 Secure Server CA,O=DigiCert Inc,C=US
Subject: CN=DigiCert Global Root CA,OU=www.digicert.com,O=DigiCert Inc,C=US
begin
DBMS_NETWORK_ACL_ADMIN.APPEND_WALLET_ACE(
wallet_path => 'file:///apps/oracle/test',
ace => xs$ace_type(privilege_list => xs$name_list('use_client_certificates'),
principal_name => 'daniel',
principal_type => xs_acl.ptype_db));
BEGIN
DBMS_NETWORK_ACL_ADMIN.append_host_ace (
host => '*.oracle.com',
ace => xs$ace_type(privilege_list => xs$name_list('resolve'),
principal_name => 'daniel',
principal_type => xs_acl.ptype_db));
DBMS_NETWORK_ACL_ADMIN.append_host_ace (
host => '*.oracle.com',
lower_port => 443,
ace => xs$ace_type(privilege_list => xs$name_list('connect'),
principal_name => 'daniel',
principal_type => xs_acl.ptype_db));
SQL*Plus: Release 12.1.0.2.0 Production on Fri Mar 15 18:42:07 2019
Last Successful login time: Fri Mar 15 2019 18:34:58 +11:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> select utl_http.request('https://www.oracle.com/index.html',NULL,'file:/apps/oracle/test','nothing_critical') from dual;
UTL_HTTP.REQUEST('HTTPS://WWW.ORACLE.COM/INDEX.HTML',NULL,'FILE:/APPS/ORACLE/TES
--------------------------------------------------------------------------------
<!DOCTYPE html>
<html lang="en-US" class="no-js">
<!-- start : Framework/HomePage -->
<!-- start : ocom/common/global/components/framework/head -->
<!-- start : ocom/common/global/components/head -->
<title>Oracle | Integrated Cloud Applications and Platform Services</title>
Re: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1720 ORA-29024: Certificate valida
[
message #675212
is a reply to
message #675210
]
Fri, 15 March 2019 03:04
It seems that SQLDEVELOPER is source of problem !
Everything works perferts with sqlplus on both side, database and client
But running through SQL Developer version 18.3.0.277 (Build 277.2354) show errors !!!
Re: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1720 ORA-29024: Certificate valida
[
message #675223
is a reply to
message #675212
]
Fri, 15 March 2019 04:58
Thanks all for help ..
But this example was because I have same problem with different web site (since oracle.com behave same I show that example)
Initially I have problem accessing this site
https://cis.porezna-uprava.hr:8449/FiskalizacijaService
but I am not sure is this Oracle problem or certificate problem
For that site I also get same error ORA-29024: Certificate validation failure (now I test using SQLplus ). I got all (I think) certificate imported in wallet. What is confusing me is if I try to access throw browser (Mozila for example) I got message that conection is not secure. Is it maybe "bad" certificate on host site ?
