Troubleshooting Oracle Wallet – Hari Kari style

Update on Feb 14, 2017 

Using Oracle 12.1.0.2 version of orapki …..don’t import the enduser certificate…only the root and intermediate certificates. 

Rest of the article is how it was originally published…

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.

Advertisements

About April C Sims

Oracle DBA for over a decade...enough said.
This entry was posted in Uncategorized and tagged , , , , . Bookmark the permalink.

13 Responses to Troubleshooting Oracle Wallet – Hari Kari style

  1. S Singh says:

    That was sensational!

  2. Luigi Fulk says:

    Im glad that I found your post! It helped me solve some of the problems Ive been having. I hope to be a regular visitor. Thanks!!

  3. Pingback: 2010 in review « Oracle High Availability

  4. boeroboy says:

    Amen. I feel your pain. I’m so sick of Oracle/mod_ossl/owm. Glad we’re finally going mainstream Apache. Right now I’ve just been staring at OWM as it declines my valid cert importing on its valid CSR with valid chain. Pathetic.

  5. Peter says:

    Maybe some of you can help me solve my (looks identical to above) problem with the renewal of the ceritifcate.

    I am using my original wallet to import the newly created certificated for my server. When trying to import it says that the import failed (suggestions are
    – input not a valid certificate (it is as can be loaded with proper chain certs in browser)
    – no matching cert request (I also created from this wallet a csr which is signed, but import of the .cer file gives same error)
    – CA cert needed for chain not found. The ca certs which signed are present in the wallet and all seem to match the chain – also in browser this is visible).

    I do not seem to succeed in getting the new certificate loaded.
    Unfortunately time starts ticking, so please help.

    • April C Sims says:

      Have you tried using the instructions in the blog post? The problems you are experiencing is why I started using the command-line for creating wallets.

  6. SamO says:

    I am glad you found a fix. FYI… For the DB’s wallet, I use a self-signed certificate but only by using OWM, because I have not been able to figure out the cmd syntax. Sam.

  7. Dima G says:

    Thanks a lot. This material helped me to proceed with UTL_HTTP and HTTPS calls from Oracle PL/SQL procedure. It was error ORA-28759 and simple restarting of database make it run correctly.

  8. Hareesh says:

    We renewed our existing SSL certs and we imported the new cert into the Wallet but still getting the Certificate validation failure err. Any helps would be appreciated.

    • April C Sims says:

      Have you verified the chain is complete? Is the wallet auto-login? Have you tried opening up the wallet with owm gui utility and resaving it. View the certificate using a browser to see if it is valid and not missing any part of the chain.

  9. Kamen says:

    You saved my day (and week). I was trying to setup SSL connection to our DB and I was constantly getting all kinds of errors ranging from adapter errors to “ORA-29106: Cannot import PKCS #12 wallet.”.

    The last step fixed all this – I opened the wallet manager and resaved :) Simple and working

  10. April C Sims says:

    Update to this entry…..on our latest version of Oracle 12.1.0.2 RDBMS
    http://stackoverflow.com/questions/19380116/using-utl-http-wallets-on-12c-certificate-validation-failure
    Instructions to update a certificate are now completely different, these are the steps we finally got to work, yours may be different.

    1. Create a new wallet, rename the root and intermediate certificate as *.cer
    2. Import the root CA certificate
    2. Import the Intermediate Certification
    3. DO NOT IMPORT THE END-USER CERTIFICATE
    4. Reboot the database

    The following instructions are correct to a certain point…..these were from the certificate vendor.
    https://knowledge.symantec.com/support/ssl-certificates-support/index?page=content&id=SO4083

    But had issues where the certificate wasn’t working….so searching google helped….found the following: http://stackoverflow.com/questions/19380116/using-utl-http-wallets-on-12c-certificate-validation-failure
    Which suggested that it was the end-user certificate causing the issue.

  11. Erik says:

    (edited for clarity- disregard previous attempt; funny how you only spot the errors after pressing submit)

    The wallet that is used by UTL_HTTP on the database server is a truststore.
    That means that it should only ever contain root CA certificates – never intermediate CA certificates or “User” (server) certificates.
    If adding the intermediate CA certificate makes a difference it is because the HTTPS server side is not configured to send the required certificate chain (server cert + intermediate CA certs).
    Browsers can sometimes work around a server with an incomplete certificate chain by using cached copies of intermediate CA certs it has seen before or by looking up the the missing intermediate CA cert from AIA information in the server cert. (UTL_HTTP cannot)

    The above assumes that UTL_HTTP is not required by the server to present a “client” certificate, if that is the case the wallet used by UTL_HTTP must contain a keypair + server certificate (called “User Certificate” by owm and orapki) and the intermediate CA certificates leading up to the root CA certificate – just like when configuring a server. Client certificates are rarely required.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s