Home » SQL & PL/SQL » SQL & PL/SQL » ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1720 ORA-29024: Certificate valida (Oracle Database 12c Release 12.1.0.1.0 - 64bit Production, windows server 2008)
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 Go to next message
9a5e
Messages: 7
Registered: March 2019
Junior Member
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 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
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 #675202 is a reply to message #675200] Thu, 14 March 2019 09:47 Go to previous messageGo to next message
9a5e
Messages: 7
Registered: March 2019
Junior Member
/foru/forum/fa/14062/0/Hi John,

upgrade to 12.2 is not a option since customer still using forms 6i.
Behind example I show with www.oracle.com I have really business problem with some other site. I take "oracle" because it is easy for all to reproduce problem
Since steps I write are steps which I found in many forums (even I thins that oracle itself say same) I out of idea what to do

/foru/forum/fa/14062/0/


  • Attachment: oracle.png
    (Size: 11.56KB, Downloaded 1893 times)
Re: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1720 ORA-29024: Certificate valida [message #675203 is a reply to message #675202] Thu, 14 March 2019 13:51 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
So the problem is that you are using Forms 6i. THat uses the 8i OCI libraries which, if I remember correctly, was released in 1999. There may be no solution for this environment.
Re: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1720 ORA-29024: Certificate valida [message #675204 is a reply to message #675199] Thu, 14 March 2019 19:34 Go to previous messageGo to next message
dwatkins
Messages: 6
Registered: June 2011
Location: Australia
Junior Member
Hi,
I've encountered this before.
Put the certificate chain in the wallet, not the destination site certificate.
the utl_http will download it when it needs it, and validate it against the remainder of the chain.

ie, delete the .oracle.com cert and try again.

cheers,
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 Go to previous messageGo to next message
9a5e
Messages: 7
Registered: March 2019
Junior Member
/foru/forum/fa/14063/0/@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 1657 times)
Re: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1720 ORA-29024: Certificate valida [message #675210 is a reply to message #675208] Fri, 15 March 2019 02:43 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
This works for me:
C:\Users\john>
C:\Users\john>orapki wallet display -wallet c:\tmp\wallet
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        CN=DigiCert Global Root CA,OU=www.digicert.com,O=DigiCert Inc,C=US
C:\Users\john>
C:\Users\john>sqlplus scott/tiger

SQL*Plus: Release 12.1.0.2.0 Production on Fri Mar 15 07:39:48 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Last Successful login time: Fri Mar 15 2019 07:39:12 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

orclz>
orclz>
orclz>  select utl_http.request('https://www.oracle.com/index.html',NULL,'file:c:\tmp\wallet') from dual ;

UTL_HTTP.REQUEST('HTTPS://WWW.ORACLE.COM/INDEX.HTML',NULL,'FILE:C:\TMP\WALLET')
--------------------------------------------------------------------------------------------------------------
<!DOCTYPE html>
<html lang="en-US" class="no-js">
<!-- start : Framework/HomePage -->
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 Go to previous messageGo to next message
dwatkins
Messages: 6
Registered: June 2011
Location: Australia
Junior Member
...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
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

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));
end;
/

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)); 
END;
/

SQL*Plus: Release 12.1.0.2.0 Production on Fri Mar 15 18:42:07 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

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 -->
<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 Go to previous messageGo to next message
9a5e
Messages: 7
Registered: March 2019
Junior Member
Aleluja Very Happy

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 !!!

/foru/forum/fa/14065/0/
  • Attachment: capture.PNG
    (Size: 63.99KB, Downloaded 1658 times)
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 Go to previous messageGo to next message
9a5e
Messages: 7
Registered: March 2019
Junior Member
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 ?
Re: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1720 ORA-29024: Certificate valida [message #675224 is a reply to message #675223] Fri, 15 March 2019 05:01 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Why do you refuse to show what you have actually done? I did, so did dwatkins.
Re: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1720 ORA-29024: Certificate valida [message #675226 is a reply to message #675224] Fri, 15 March 2019 05:09 Go to previous messageGo to next message
9a5e
Messages: 7
Registered: March 2019
Junior Member
I am sorry but I don't refuse.
I show screenshot executing same statement from SQLDeveloper and from SQLPlus (both were on client)
Or you ask me for accessing site https://cis.porezna-uprava.hr:8449/FiskalizacijaService ?
Re: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1720 ORA-29024: Certificate valida [message #675227 is a reply to message #675226] Fri, 15 March 2019 05:20 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I give up. Perhaps someone else can assist.
Re: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1720 ORA-29024: Certificate valida [message #675228 is a reply to message #675227] Fri, 15 March 2019 05:23 Go to previous message
9a5e
Messages: 7
Registered: March 2019
Junior Member
d:\appl\Fiskalizacija\Arhiva>orapki wallet display -wallet d:\appl\fiskalizacija\arhiva
Oracle PKI Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

Requested Certificates:
User Certificates:
Trusted Certificates:
Subject: CN=Fina RDC 2015,O=Financijska agencija,C=HR
Subject: CN=DigiCert Global Root CA,OU=www.digicert.com,O=DigiCert Inc,C=US
Subject: CN=Fina Root CA,O=Financijska agencija,C=HR
Subject: CN=Fina RDC-TDU 2015,O=Financijska agencija,C=HR
Subject: SERIAL_NUM=VATHR-18683136487.92,CN=cis.porezna-uprava.hr,L=ZAGREB,O=MINISTARSTVO FINANCIJA,C=HR
d:\appl\Fiskalizacija\Arhiva>sqlplus soft

SQL*Plus: Release 12.1.0.1.0 Production on Fri Mar 15 11:22:14 2019

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Enter password:
Last Successful login time: Fri Mar 15 2019 11:22:08 +01:00

Connected to:
Oracle Database 12c Release 12.1.0.1.0 - 64bit Production

SQL> select utl_http.request ('https://cis.porezna-uprava.hr:8449/FiskalizacijaService',NULL,'file:d:\appl\fiskalizacija\arhi
select utl_http.request ('https://cis.porezna-uprava.hr:8449/FiskalizacijaService',NULL,'file:d:\appl\fiskalizacija\arhiva','
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1720
ORA-29024: Certificate validation failure
ORA-06512: at line 1

Previous Topic: SQL Data Extraction for CLOB
Next Topic: Performance of Virtual column referencing another table!
Goto Forum:
  


Current Time: Fri Mar 29 08:06:18 CDT 2024