wallet-certificate addition

[satish]

Dear Erman,

Need your help.

We are on R12.2.5 application tier and 12.1.0.2 database tier.

We have a requirement to access external site https.

We have given access to ACL.While trying to add the certificates which we downloaded using brower from external site giving error.

Steps followed to create wallet and certificate addition

[orasupp@stagedb ~]$ mkdir -p /home/orasupp/wallet
[orasupp@stagedb ~]$ orapki wallet create -wallet /home/orasupp/wallet -pwd WalletPasswd123 -auto_login
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
[orasupp@stagedb ~]$


[orasupp@stagedb NICSMS_CERTS]$ cd /home/orasupp/NICSMS_CERTS/
[orasupp@stagedb NICSMS_CERTS]$ ls -lrt
total 12
-rwxrwxrwx 1 orasupp dba 1946 Oct 15 10:25 NICSMS_root.cer
-rwxrwxrwx 1 orasupp dba 1946 Oct 15 10:26 NICSMS_Intermediate.cer
-rwxrwxrwx 1 orasupp dba 1946 Oct 15 10:26 NICSMS-Third.cer

Added root certificate:

[orasupp@stagedb NICSMS_CERTS]$ orapki wallet add -wallet /home/orasupp/sample/wallet -trusted_cert -cert /home/orasupp/NICSMS_CERTS/NICSMS_root.cer -pwd WalletPasswd123
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
[orasupp@stagedb NICSMS_CERTS]$


Adding intermediate certificate failed with below error:

[orasupp@stagedb NICSMS_CERTS]$  orapki wallet add -wallet /home/orasupp/sample/wallet -trusted_cert -cert /home/orasupp/NICSMS_CERTS/NICSMS_Intermediate.cer -pwd WalletPasswd123
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
Could not install trusted cert at/home/orasupp/NICSMS_CERTS/NICSMS_Intermediate.cer
PKI-04003: The trusted certificate is already present in the wallet.


Display:

[orasupp@erpstagedb NICSMS_CERTS]$ orapki wallet display -wallet /home/orasupp/sample/wallet -pwd WalletPasswd123
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=smsgg.sms.gg.in
[orasupp@erpstagedb NICSMS_CERTS]$

Error:

SQL> select utl_http.request('https://smsgg.sms.gg.in',null,'file:/home/orasupp/wallet','WalletPasswd123') from dual;
select utl_http.request('https://smsgg.sms.gg.in',null,'file:/home/orasupp/wallet','WalletPasswd123') from dual
       *
ERROR at line 1
ORA-29273: HTTP request failed
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.UTL_HTTP", line 1491
ORA-06512: at line 1


Please suggest

Thank You

[ErmanArslansOracleBlog]

It was obvious that the certificates in the wallet could not be validated..

Still , you may wanted to diagnose the issue; by getting a 10937 trace.

Actually you need to analyze your wallet and the certificates inside of it..

The wallet should include only the signing certificates, because during the SSL handshake Oracle checks whether the signing authority is known to it (i.e. whether the certificates of the signing authority was imported into the wallet).

Also check this blog post -> Read the last paragraph of it ->

https://ermanarslan.blogspot.com/2018/12/rdbms-tls-12-support-and-issues-ora.html

[satish]

Hi erman,

This is inside my wallet.

Display:

[orasupp@erpstagedb NICSMS_CERTS]$ orapki wallet display -wallet /home/orasupp/sample/wallet -pwd WalletPasswd123
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=smsgg.sms.gg.in

Will try trace as well as u suggested

Thank you

[satish]

Dear Erman,

This is the trace

 1168: 674b866a dba0d365 d89d51e7 9d4f2f54       |gK.j.SSL_Info: SSLv3 read server certificate A (TLSv12 protocol)
nzosv_CertVerifyCb:  certverify - CN=smsgw.sms.gov.in
nzxVCC_Validate_Cert_Chain: entry
nzxVCA_Validate_CA: entry
nzxVCA_Validate_CA: Basic Constraints Extensions: CA flag is OFF
nzxVCA_Validate_CA: exit
nzxVCC_Validate_Cert_Chain: exit
nzospWrite: [Raw write] length = 7
nhp: 2020-10-15 16:05:30.298074 send enter 0x7f10f4e30300 7
nhp:  > send 7 bytes
^U^C^C^B^B*[end]
nhp: 2020-10-15 16:05:30.298175 send return 0 0 elapsed +0 00:00:00.000101
nhp: 2020-10-15 16:05:30.298206 flush enter 0x7f10f4e30300
nhp: 2020-10-15 16:05:30.298220 flush return 0 0 elapsed +0 00:00:00.000014
nzosp_bio_write: processed=7, ret=0
nzospLog_bio:  biowrite:  buf=0x125ae1e0, requested=7, actual=7
     0: 15030300 02022a-- -------- --------       |......*         |
SSL_Alert: write - fatal - bad certificate
SSL_Alert: write - fatal - bad certificate
SSL_Info: error in SSL3 certificate verify A
SSL_Info: error in SSL3 certificate verify A
nzos_Handshake: Handshake returned failure code -1
nzos_Handshake:  Handshake error(1,336134278)- error:14090086:SSL routines:SSL3_GET_SERVER_CERTIFICATE:certificate verify failed
nzos_Handshake: exit
nzos_DestroyCtx: entry
nzos_DestroyCtx: exit
nhp: 2020-10-15 16:05:30.298429 disconnect enter 0x7f10f4e30300
nhp: 2020-10-15 16:05:30.298543 disconnect return 0 29024 elapsed +0 00:00:00.000114
    64: 43ad15bf 35009c00 0005ff01 000100--       |C...5.......... |

Thank You

[satish]

Dear Erman,

Please find the trace file attached

Thank YouSUPPDB_ora_5771_SRDC_10937.trc

Thank You

[ErmanArslansOracleBlog]

SSL_Alert: write - fatal - bad certificate
Handshake error(1,336134278)- error:14090086:SSL routines:SSL3_GET_SERVER_CERTIFICATE:certificate verify failed
SSL_Info: SSLv3 read server certificate A (TLSv12 protocol)

Most probably, you have a wrong certificate in place. wrong certificate or a certificate in the wrong position.
Probably, you imported a user certificate as a trusted one..

For insance ; you have a user certificate in the certificate chain, imported as a trusted certificate and the Issue is probably due to that is imported as trusted certificate in proxy wallet.

I already addressed a similar issue in the following blog post;

https://ermanarslan.blogspot.com/2018/12/rdbms-tls-12-support-and-issues-ora.html

So, correct your wallet/keystore etc accordingly and retry..
Or, create a new wallet/keystore etc and import the certificates accordingly and properly.

[satish]

Dear Erman,

We tried adding the certificates again and we have added root and intermediate certificates successfully.Now we are not seeing any certificate validation failures but a different error.Can you please suggest

SQL> select utl_http.request('https://smsgg.sms.gg.in',null,'file:/home/orasupp/certificate/wallet','password') from dual;

UTL_HTTP.REQUEST('HTTPS://SMSGG.SMS.Gg.IN',NULL,'FILE:/HOME/ORASUPP/CERTIFICATE
--------------------------------------------------------------------------------
<html><head><title>Error</title></head><body>Forbidden</body></html>

SQL>

DB version 12.1.0.2
Apps version 12.2.5

Thank You

[satish]

when i give complete url,it is returning as below

SQL> select utl_http.request('https://smsgg.sms.gg.in/failsafe/HttpLink?username=san.sms&pin=gf@123&message=NIC-SMS-SAMPLE-TEST2&mnumber=918106532882&signature=SSTPE',null,'file:/home/orasupp/certificate/wallet','WalletPasswd123') from dual;
Enter value for pin: gf@123
Enter value for message: hi
Enter value for mnumber: 918106532882
Enter value for signature: SSTPE

UTL_HTTP.REQUEST('HTTPS://SMSGG.SMS.GG.IN/FAILSAFE/HTTPLINK?USERNAME=SSS.S
--------------------------------------------------------------------------------
null

SQL>

Thanks

[ErmanArslansOracleBlog]

This is a different question Satish.
Please create a seperate thread for this.

Thanks for your understanding.