Recently we had the pleasure of a last minute emergency replacement of a SSL certificate. I think most IT shops have been there, done that. But the problem is that the replacement renewal certificate didn’t work – now how does one proceed?
1. Something is wrong after replacing SSL certificate on the hardware load balancer. Symptoms – Page not found errors, ORA-29024: Certificate validation failure in the Oracle Application Server logs. Revert to older certificate all is well again. But no time to waste since it expires tomorrow at 18:59 MST. Yeeegads!
2. This is an external CAS ticket server authenticating to an Oracle Database using UTL_HTTP calls. Doing the following select statement as a user with execute privileges on UTL_HTTP also shows the same issue, no matter the URL -
select utl_http.request ('https://hostname.domain/cas/login'
,null,null,null) from dual;
ERROR at line 1: ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1577
ORA-29024: Certificate validation failure
ORA-06512: at line 1
3. Ok, this is Oracle so I know the Oracle Wallet is involved. It resides on the database server since we apply certificates on the load balancer, your situation may involve an Oracle Wallet on OAS as well. The following select statement checks if the wallet is valid, not corrupt and has the correct password.
select utl_http.request ('https://www.verisign.com/',null,
'file:/etc/ORACLE/WALLETS/oracle','password') from dual;
ERROR at line 1: ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1577
ORA-29024: Certificate validation failure ORA-06512: at line 1
4. Yeegads again! There is something wrong with the wallet, now how can that be? DBA didn’t change anything, how does replacing a certificate invalidate the wallet? So this is where the Hari Kari starts – I backup the old wallet directory, create a new one wallet in the same location, same password. Still doesn’t work, same error.
Now the document on ‘My Oracle Support” -
Troubleshooting ORA-29024:Certificate Validation Failure
Doc ID: 756978.1
gets me started but I can’t find anything wrong with the wallet. I use both orapki (wallet command line utility) and OWM (gui). There are no problems opening the wallet viewing the chain, etc.
> orapki wallet display -wallet .
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject: CN=GTE CyberTrust Root,O=GTE Corporation,C=US
Subject: OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject: CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US
Subject: CN=Entrust.net Secure Server Certification Authority,OU=(c) 2000 Entrust.net Limited,OU=www.entrust.net/SSL_CPS incorp. by ref. (limits liab.),O=Entrust.net
Subject: CN=Entrust.net Certification Authority (2048),OU=(c) 1999 Entrust.net Limited,OU=www.entrust.net/CPS_2048 incorp. by ref. (limits liab.),O=Entrust.net
Subject: CN=Entrust.net Secure Server Certification Authority,OU=(c) 1999 Entrust.net Limited,OU=www.entrust.net/CPS incorp. by ref. (limits liab.),O=Entrust.net,C=US
5. The knife goes deeper into the flesh, I am really hurting now. It is 10:00 pm been on the cell phone with other IT personnel involved over 60 minutes (they are supposed to be in my “circle of friends” so hopefully the cell phone bill won’t kill me either). Continue on with the troubleshooting document – ”Both the user and trusted certificates are valid and not expired or revoked” – I don’t have any user certificates…the others seem fine. I have recreated the wallet several times, bounced the database/load balancer/OAS/CAS server each time (can you sense the ”desperation”) because I find in another document
How To Replace An Expired Or Expiring Certificate in Wallet Manager
Doc ID: 303299.1
(why wasn’t this step in the troubleshooting ORA-29024 document?) that you have to “Restart the component that uses the Wallet i.e Webcache, HTTP Server, or Database, as the Wallet is stored in memory and will not be re-read until the component is restarted” . After several hours of testing at least at this point I have determined that the wallet is basically valid because it worked yesterday but not today with the new certificate. But Why?
6. Last step in the document -
"If this error is seen while using with UTL_HTTPS set up than , check :
---> Whether all the certificates of the secure website are there
in the wallet and the certificate chain is complete."
Well that may be the issue, checking back with the network guy who ordered the replacement cert, he seemed to think the certificate was slightly different than last time. We had double-checked it’s validity by viewing it with several browsers. At this point everyone went home, I entered an Oracle SR (since the database wasn’t down I knew there wouldn’t be a quick response), programmer guy emailed his CAS colleagues, network guy entered an emergency support request through Verisign.
7. The next morning I started check ing Verisign’s website, programmer guy mentioned he thought it might be a chaining issue…possibly a new or updated intermediate certificate was needed. The knife was still in deep which had made it hard to sleep. Next morning network guy says definitely a new intermediate certificate was needed according to Verisign…checking Verisign’s website there are several. You will need your certificate order number to get the right one. Verisign support had attached it in the support request.
https://knowledge.verisign.com/support/ssl-certificates-support/index?page=content&id=AR657&actp=LIST (Verisign Intermediate CA Certificates)
8. How to install this intermediate certificate? Easiest way is to use the orapki utility. Previous hari kari work with intermediate certificates gave me the knowledge that Oracle was “picky” with certificates and that the intermediate certificate needed to be named ca.crt to work. I renamed the file (transferred in binary format) to ca.crt put it in the /etc/ORACLE/WALLETS/oracle location. But we aren’t finished yet. It won’t import, it just gives me an error unable to open wallet . I give it the full path, still same error.
Yeegads! OOmph! Ok…I had just imported the intermediate certificate into a non-production instance and got it working. Why is production giving me grief?
9. I backed up (moved) the old wallet, more slice and dice. There are several directories with old wallets…none of them worked for the new certificate during the previous night’s testing but they all worked with the old certificate but none of them would let me import the intermediate certificate. AAgh! Created a new wallet, I used a different command-line utility (Reflection) instead of Putty because in another document from My Oracle Support it mentioned that the keyboard might not be typing the wallet password correctly. What! I have used this console utility forever, first I have heard of this. So I create the new wallet using Reflection and put a single quote around the password. So take that! and that! Back you evildoer! Back away! This is all out war! Still problems.
cd /etc/ORACLE/WALLETS/oracle
orapki wallet create -wallet . -auto_login -pwd 'password'
orapki wallet add -wallet . -trusted_cert -cert ca.crt -pwd 'password'
(don't expect anything to tell you this was successful you are just looking
to see if any errors occur)
select utl_http.request ('https://www.verisign.com/',null,
'file:/etc/ORACLE/WALLETS/oracle','password') from dual;
select utl_http.request ('https://www.verisign.com/',null,
*ERROR at line 1:ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1577
ORA-28759: failure to open file ORA-06512: at line 1
10. At least the ERROR MESSAGE changed! Sorry but at this point my desperation was running full tilt, anything makes me giddy. I restared the production database, I knew that was probably going to happen. I discreetly change the status of my IM as busy to reduce the numbers of attacking messages. They start fast and furious.
select utl_http.request ('https://domainname/cas/login',null,
'file:/etc/ORACLE/WALLETS/oracle,'password') from dual;
select utl_http.request ('https://domainname/cas/login',
null,'file:/etc/ORACLE/WALLETS/oracle','password') from dual
* ERROR at line 1: ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1577
ORA-29106: Cannot import PKCS #12 wallet. ORA-06512: at line 1
11. I realize that I left out the closing quote after /etc/ORACLE/WALLETS/oracle in the SQL statement. Retyped it correctly and it returns a valid web page, it is finally over. I remove the knife, it might heal with some time off.
12. If you need to migrate an existing 10.2.x wallet to 11.2.x -open it with Oracle Wallet Manager in 11.2.x and resave it. Will have to bounce the database for the change to take affect.
