How to Fix 401 Authorization Required in Oracle Database for Wallet

 I frequently use Oracle wallet for my databases to store certificates. For one site, I started receiving '401 authorization required' error and it seemed that cert was expired. If you are after TLDR; then following shows how we fixed it.

SQL> select utl_http.request('https://dummysite.com',null,'file:/scripts/src/oracle/wallets','XXXXXXXXXX') from dual;

 

UTL_HTTP.REQUEST('https://dummysite.com',NULL,

--------------------------------------------------------------------------------

401 Authorization Required

 

SQL> select utl_http.request('https://dummysite.com',null,'file:/d01/oracle/prod2db/10.2.0/appsutil/wallet', null) from dual;

 

UTL_HTTP.REQUEST('https://dummysite.com',NULL,

--------------------------------------------------------------------------------

401 Authorization Required

All I did was I added the root certificates in the wallet located at /scripts/src/oracle/wallets for fixing this issue.

For details:

First identify which http call from within the application or database was failing:

SQL> select distinct sql_text,sql_id from v$sql where upper(sql_text) like  '%UTL%HTTP%';

SQL_TEXT

--------------------------------------------------------------------------------

SQL_ID

-------------

select distinct sql_text,sql_id from v$sql where upper(sql_text) like  '%UTL%HTT

P%'

gcgfyfty86c84

SQL> select distinct sql_text,sql_id from v$sqlarea where upper(sql_text) like  '%UTL%HTTP%';

SQL_TEXT

--------------------------------------------------------------------------------

SQL_ID

-------------

select distinct sql_text,sql_id from v$sql where upper(sql_text) like  '%UTL%HTT

P%'

gcgfyfty86c84

select distinct sql_text,sql_id from v$sqlarea where upper(sql_text) like  '%UTL

%HTTP%'

34x064xsfa0dy

and then locate the cert destination and put root certificate from there from your CA.

[oracle@oraapps3 ~]$ cd /scripts/src/oracle/wallets

[oracle@oraapps3 wallets]$ ls -lrt

total 96

-rw-r--r-- 1 oracle oinstall  1115 Aug 22  2023 test2.TESTint.net.cert

-rw-r--r-- 1 oracle oinstall   837 Aug 22  2023 test2

-rw------- 1 oracle oinstall 14765 Jan 25 13:47 ewallet.p12

[oracle@oraapps3 wallets]$ orapki wallet display -wallet .

Enter wallet password:      

   

Requested Certificates: 

User Certificates:

Trusted Certificates: 

Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

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) 2000 Entrust.net Limited,OU=www.entrust.net/SSL_CPS incorp. by ref. (limits liab.),O=Entrust.net