OraFAQ Forum: SQL & PL/SQL » ORA-29005: The certificate is invalid

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-29005: The certificate is invalid (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.12.0.0.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-29005: The certificate is invalid [message #686612]

Mon, 24 October 2022 04:57

fixxxer
Messages: 46
Registered: August 2014

Member

Hi,

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.12.0.0.0

I am trying to make a UTL_HTTP request to an application server. I have assigned the ACL to the user in question, with connect and resolve permissions. The certificate has been added to the oracle wallet.

If I run the below select (to validate the connection) it works for SYS user:

SQL> SELECT utl_http.request('https://HIDDENSERVER.com:8443/healthcheck',NULL,'file:/u01/app/oracle/wallet') from dual;

UTL_HTTP.REQUEST('HTTPS://HIDDENSERVER.COM:8443/HEALTHCHECK',NULL,'FILE:/
--------------------------------------------------------------------------------
{"status":"UP"}

If I run the same select as the user I want to issue it under, I get an error:

SQL> SELECT utl_http.request('https://HIDDENSERVER.com:8443/healthcheck',NULL,'file:/u01/app/oracle/wallet') from dual;
SELECT utl_http.request('https://HIDDENSERVER.com:8443/healthcheck',NULL,'file:/u01/app/oracle/wallet') from dual
       *
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1530
ORA-29005: The certificate is invalid.
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1470
ORA-06512: at line 1

Since it is working for SYS user, but not for my own user, I'm obviously missing some configuration/setup for the user in question. Any advice on what/where I should check?

Thanks in advance.

Report message to a moderator

Re: ORA-29005: The certificate is invalid [message #690021 is a reply to message #686612]

Mon, 16 September 2024 13:11

PPOraFaq
Messages: 3
Registered: September 2024

Junior Member

Hi,
did you find any solution?

We have the same behavior after migrating the oracle database from 18.3 to 19.19.

Originally working certificate for communication with the webservice (under oracle 18.3), works only under the SYS account now, we also get an ORA-29005 error under the user account.
We tried re-generating the certificate (it is issued by a third party) but this did not help. The certificate itself seems to be valid (verified by orapki and wallet manager).
When tracing communication (ALTER SESSION SET EVENTS = '10937 TRACE NAME CONTEXT FOREVER, LEVEL 4'), the log shows that when running under the user, an error is raised during the handshake after receiving the server certificate chain..
SSL_Alert:
read - fatal - bad certificate
But when running under the SYS account, the client certificate is sent to the server (after receiving the server certificate chain), the handshake is completed, and communication takes place.

If you have any new information, please shared it..

Thank you
Pavel

Report message to a moderator

Re: ORA-29005: The certificate is invalid [message #690022 is a reply to message #690021]

Tue, 17 September 2024 03:51

fixxxer
Messages: 46
Registered: August 2014

Member

Hey Pavel,

I am going to do my best to recollect this lol. So we were trying to make a UTL_HTTP request to an API endpoint. There were already a set of certificates stored in the wallet on Oracle server. I added another certificate for the API application. The request was failing with the certificate I added. I removed that certificate and just tried it again with the certificates that were already in place. If you already have certs in your wallet, I would try the request using that certificate, rather than adding new ones. Sorry if this is no help, but this is just the scenario for me. SYS user was using the already installed certificates, my user was trying to use the cert I installed. I removed that cert and used the same one SYS was using.

Report message to a moderator

Re: ORA-29005: The certificate is invalid [message #690023 is a reply to message #690022]

Tue, 17 September 2024 06:19

Solomon Yakobson
Messages: 3301
Registered: January 2010
Location: Connecticut, USA

Senior Member

Did you grant:

begin
  dbms_network_acl_admin.append_host_ace (
    host       => '<your-website>', 
    lower_port => 443,
    upper_port => 443,
    ace        => xs$ace_type(privilege_list => xs$name_list('http'),
                              principal_name => '<your-username>',
                              principal_type => xs_acl.ptype_db)); 
end;
/

SY.

Report message to a moderator

Re: ORA-29005: The certificate is invalid [message #690024 is a reply to message #690023]

Tue, 17 September 2024 14:08

PPOraFaq
Messages: 3
Registered: September 2024

Junior Member

Thank you for your hints.. every notes are welcome..

@fixxxer - thank you, probably is our situation slighly other - we use client certificate for authorization to webservice (imported into wallet with complete chain and trusted for webservice cert) - communication to webservice on oracle 18.3 is running without problems.. With Ora19.19 we run into problem..

@Solomon thank you too - I used configuration ACL with
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE
DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL
(host set to all (*) port to all (null)

SELECT * FROM DBA_HOST_ACES WHERE PRINCIPAL = 'MYUSER';

HOST,LOWER_PORT,UPPER_PORT,ACE_ORDER,START_DATE,END_DATE,GRANT_TYPE,INVERTED_PRINCIPAL,PRINCIPAL,PRINCIPAL_TYPE,PRIVILEGE
*,,,2,,,GRANT,NO,MYUSER,DATABASE,RESOLVE
*,,,2,,,GRANT,NO,MYUSER,DATABASE,CONNECT
*,,,2,,,GRANT,NO,MYUSER,DATABASE,USE_CLIENT_CERTIFICATES
*,,,2,,,GRANT,NO,MYUSER,DATABASE,USE_PASSWORDS

Pavel

Report message to a moderator