ORA-29024: Certificate validation failure

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.

UTL_HTTP && Network ACL 11g

SELECT host, acl,

DECODE(

DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, 'VOLUME', 'resolve'),

1, 'GRANTED', 0, 'DENIED', NULL) privilege

FROM dba_network_acls;

SELECT host, lower_port, upper_port, acl,

DECODE(

DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, 'VOLUME', 'connect'),

1, 'GRANTED', 0, 'DENIED', null) privilege

FROM dba_network_acls;

SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;

SELECT * FROM DBA_NETWORK_ACLS;

begin

dbms_network_acl_admin.assign_acl( 'volume_utl_http.xml', '*.com');

end;

begin

dbms_network_acl_admin.create_acl (

acl => 'volume_utl_http.xml',

description => 'HTTP Access',

principal => 'VOLUME',

is_grant => TRUE,

privilege => 'connect',

start_date => null,

end_date => null

);

dbms_network_acl_admin.add_privilege (

acl => 'volume_utl_http.xml',

principal => 'VOLUME',

is_grant => TRUE,

privilege => 'resolve',

start_date => null,

end_date => null

);

dbms_network_acl_admin.assign_acl( 'volume_utl_http.xml', '*.com');

commit;

end;

Simple Rollup

SELECT

--to display "Sub Total" column with "Total" at the End

DECODE(grouping(TRUNC(date_created,'MM')),0,NULL,'Total') "Sub Total" ,

TO_CHAR(TRUNC(date_created,'MM'),'MONTH-YYYY') "Months" ,

COUNT(*) "This Month Tweets"

FROM twitter

GROUP BY rollup (TRUNC(date_created,'MM') )

ORDER BY TRUNC(date_created,'MM');

Oracle SQL STRIP HTML Tags

You can strip all HTML Tags from a text string using:

SELECT REGEXP_REPLACE('string or column containing HTML goes here','<[^>]+>','') FROM DUAL

-- CREATE FUNCTION

CREATE OR REPLACE

FUNCTION "STRIPHTML" (strArg IN CLOB)

RETURN CLOB IS

BEGIN

RETURN regexp_replace(strArg, '<[^>]+>', NULL);

END STRIPHTML;

Track RMAN Backup Job

----------------Rman Job done sofar ------------------------------------------
select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,
sysdate + TIME_REMAINING/3600/24 end_at
from v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%';

----------------------- Rman running session------------------------
SELECT s.SID, p.SPID, s.CLIENT_INFO
FROM V$PROCESS p, V$SESSION s
WHERE p.ADDR = s.PADDR
AND CLIENT_INFO LIKE 'rman%';

------------------------ Database Active session --------------------------------
SELECT
/**** session active users ****/
s.sid sid ,
s.serial# serial_id ,
lpad(s.status,9) session_status ,
lpad(s.username,35) oracle_username ,
lpad(s.osuser,12) os_username ,
lpad(p.spid,7) os_pid ,
s.program session_program ,
lpad(s.terminal,10) session_terminal ,
lpad(s.machine,19) session_machine
FROM v$process p ,
v$session s;
WHERE p.addr (+) = s.paddr
AND s.status = 'ACTIVE'
AND s.username IS NOT NULL
ORDER BY sid ;

Fact sheet about Oracle database passwords

Fact sheet about Oracle database passwords


Oracle Password Algorithm (7-10g Rel.2) (Designed by Bob Baldwin)

Up to 30 characters long. All characters will be converted to uppercase before the hashing starts
8-byte hash, encrypted with a DES encryption algorithm without real salt (just the username).
The algorithm can be found in the book "Special Ops Host and Network Security for Microsoft, Unix, And Oracle" on page 727.

Oracle database 11g offers the (optional) possibility to use passwords up to 50 characters (uppercase/lowercase). In Oracle 11g the passwords are now hashed with DES (column: password) AND using SHA-1 (column: spare4). The SHA-1 passwords are now supporting mixed-case passwords. In 11g the password hashes are no longer available in dba_users.

• Oracle (7-10g R2) encrypts the concatenation of (username||password)
  and sys/temp1 and system/p1 have the identical hashkey (2E1168309B5B9B7A)
• Oracle (11g R1) uses SHA-1 to hash the concatenation of (password||salt)

Oracle Password Cracker

A comparision of different password cracker can be found here. Benchmarks for different password crackers are available here (10g) and here (11g) .


Location of Oracle password hashes

• Database - SYS.USER$ - Password
• Oracle Password File
• Data File of the system tablespace
• (full) Export-Files
• archive logs

Show Oracle password hashkey (old DES hash)

You should always select database users from the table not from the views (ALL_USERS, DBA_USERS). An explanation (modification of database views via rootkits) can be found here.

• DBA_USERS : SELECT username, password FROM DBA_USERS;
• SYS.USER$ : SELECT name,password FROM SYS.USER$ WHERE password is not null;

Show Oracle password hashkey (11g, new SHA-1 hash)

In 11g the password hash is no longer accessible via dba_users

• SYS.USER$ : SELECT name,spare4 FROM SYS.USER$ WHERE password is not null;

How to change an Oracle password?

You should always use the password command because the password is sent unencrypted over the net (without Advanced Security Option) if you use the alter user syntax.

• alter user myuser identified by my!supersecretpassword;
• grant connect to myuser identified by my!supersecretpassword
• update sys.user$ set password='F894844C34402B67' where name='SCOTT'; (restart of the database necessary)
• SQL*Plus command: password or password username

How to change an Oracle password temporarily?

In Oracle it is possible to change a password temporarily. This can be useful for DBA which act as a different user.

SQL> select username,password from dba_users where username='SCOTT';

USERNAME PASSWORD
-------- ----------------
SCOTT F894844C34402B67

SQL> alter user scott identified by mypassword;


Now login with the following credentials: scott/tiger
After doing your work you can change the password back by using an undocumented feature called "by values"

SQL> alter user scott identified by values 'F894844C34402B67';


Oracle default password list

600+ default Oracle passwords


Oracle Password Policy

It is possible to setup a password policy (for strong Oracle passwords). A sample file how to do this can be found at $ORACLE_HOME/rdbms/admin/utlpwdmg.sql. If you use this functionality please be aware that the password policy function has access to the cleartext password (for the comparisions reasons). With Oracle 11gR1 Oracle greatly enhanced the password verification function.

A hacker could modify your function and log all cleartext passwords entered by the users to a table or send it to a foreign webserver with utl_http. That's why you should checksum this function, e.g. with Repscan.

Oracle brute force attacks / Oracle Password Decryption (7-10gR2)

It is not possible to decrypt a hashstring but the simple Oracle salt (=Username) it is possible to do a brute force or dictionary attack. There are several Oracle brute force or dictionary attack tools available. These tools encrypt the username/password and compare the hashkeys. If the hashkey are identical the password is known. From simple SQL based tools (<500 href="http://www.red-database-security.com/software/checkpwd.html">checkpwd. The fastest tool for brute force attacks orabf calculates 1.100.000 passwords/second. The fastest tool for dictionary attacks are checkpwd and repscan with 600.000 pw per second. On a Pentium 4 with 3 GHz it takes (26 ascii characters only, e.g. 26^5)

• 10 seconds to calculate all 5-ascii-character-combinations
• 5 minutes to calculate all 6-ascii-character-combinations
• 2 hours to calculate all 7-ascii-character-combinations
• 2,1 days to calculate all 8-ascii-character-combinations
• 57 days to calculate all 9-ascii-character-combinations
• 4 years to calculate all 10-ascii-character-combinations

You should always use strong and long passwords to avoid brute force or dictionary attacks.


Typical Error messages related to Oracle database passwords

The following error messages are related to Oracle passwords:

• ORA_28000: The account is locked
• Wait for PASSWORD_LOCK_TIME or contact your DBA
• ORA-28001: The password has expired
• Change the password or contact your DBA
• ORA-00988: Missing or invalid password(s)
• Use double quotes for the password (e.g. alter user scott identified by "!alex";)
• ORA-01017: Invalid username/password; logon denied
• Contact your DBA if the problem still persists

Oracle database passwords in cleartext

Cleartext passwords can be typically but not necessarily found at the following places

• Server
• Shell History files
• Unix Scripts
• Log Files
• Dump Files
• Trace Files
• Application Server
• JDBC-Config-Files
• Trace Files
• DBA Client PC
• Desktop-Shortcut
• Batch-Files
• Configuration files of Oracle Tools (like connections.ini)
• Trace Files

References

• Oracle Password Cracker Checkpwd 2.00 by Red-Database-Security GmbH (fast, 600.000 pw/sec, C-based, 7-11g)
• Oracle Password Cracker Benchmark Benchmark for various Oracle password crackers (old password algorithm)
• Oracle 11g Password Cracker Benchmark Benchmark for various Oracle11g password crackers
• Oracle Default Password Scanner (DPS) SQL Script that checks 689 default passwords
• Oracle Password Algorithmus 7-10g Rel.2
• Analysis Oracle Password Algorithmus 11g
• Oracle Password Cracker 11g (P.o.C.)
• Comparision Chart Oracle Password Cracker
• Hardening DBA Client PC
• An Assessment of the Oracle Password Hashing Algorithm

Database Last Restored and Recovered Time

Below simple query give you idea about when was your database restored and till what time it was recoverd.

SELECT incarnation#,
resetlogs_change#,
TO_CHAR(resetlogs_time, 'dd-mon-yyyy hh24:mi:ss') db_restored_time ,
scn_to_timestamp(resetlogs_change#) db_recovery_till_time
FROM v$database_incarnation
WHERE resetlogs_change# !=
(SELECT MIN(resetlogs_change#) FROM v$database_incarnation
);