"Failure to open file" when trying to make a request through UTL_HTTP

Question

Setup: Windows 11 22H2 Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production SQLDeveloper version 22.1.2.234 build 234.1810

I'm trying to follow UTL_HTTP and SSL to make a request to an HTTPS endpoint.

I have followed the tutorial to:

1. To add an ACE for a test user to allow it to make requests through UTL_HTTP for oracle-base.com: select * from dba_host_acls; ACLS

select * from dba_host_aces; ACES

2. Export SSL certificate for https://oracle-base.com: ISRG Root X1 to a .CRTfile.
3. Create wallet using:

orapki wallet create -wallet .\wallet -pwd Password123 -auto_login

The wallet is created at folder C:\users\Lenovo\wallet. 4. Add the SSL certificate to wallet using

orapki wallet add -wallet .\wallet -trusted_cert -cert C:\users\Lenovo\Downloads\ISRGRootX1.crt -pwd Password123

5. Verify certificate is added as trusted certificate using:

orapki wallet display -wallet wallet

Oracle PKI Tool Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        CN=ISRG Root X1,O=Internet Security Research Group,C=US

Now, when I provide the wallet path and try to make an HTTPS request, it throws the following error:

**[Run from SQL Developer. Shows Running as "Lenovo" user in task manager]**
set serveroutput on
exec utl_http.set_wallet('**file:///C:/users/lenovo/wallet**', 'Password123');
exec show_html_from_url('https://oracle-base.com/');

Error starting at line : 3 in command -
BEGIN show_html_from_url('https://oracle-base.com/'); END;
Error report -
ORA-29273: HTTP request failed
ORA-06512: at "TEST1.SHOW_HTML_FROM_URL", line 33
ORA-28759: **failure to open file**
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1148
ORA-06512: at "TEST1.SHOW_HTML_FROM_URL", line 11
ORA-06512: at line 1
29273. 00000 -  "HTTP request failed"
*Cause:    The UTL_HTTP package failed to execute the HTTP request.
*Action:   Use get_detailed_sqlerrm to check the detailed error message.
           Fix the error and retry the HTTP request.

If I open file:///C:/users/lenovo/wallet directly through windows run, it opens the wallet folder so I'm pretty sure that the path to the wallet is correct. The cwallet.sso, ewallet.p12 files are present within this folder.

I have searched for this issue a lot and it is pointed everywhere this happens when the database can't access the wallet file.

I opened Oracle Instance Manager and found this: Oracle Run As. So with this in mind, I assumed Oracle Database runs as orcl_admin.

I changed owner of wallet folder and the files within to "orcl_admin" and added "Full Control" for orcl_admin to the wallet folder and the files within:

PS C:\Users\Lenovo> Get-Acl .\wallet\ | format-list


Path   : Microsoft.PowerShell.Core\FileSystem::C:\Users\Lenovo\wallet\
**Owner  : DESKTOP-BDS391A\orcl_admin**
Group  : DESKTOP-BDS391A\None
**Access : DESKTOP-BDS391A\orcl_admin Allow  FullControl**
         NT AUTHORITY\SYSTEM Allow  FullControl
         BUILTIN\Administrators Allow  FullControl
         DESKTOP-BDS391A\Lenovo Allow  FullControl
Audit  :
Sddl   : O:S-1-5-21-3701882691-2603361732-4164690872-1018G:S-1-5-21-3701882
         691-2603361732-4164690872-513D:AI(A;OICI;FA;;;S-1-5-21-3701882691-
         2603361732-4164690872-1018)(A;OICIID;FA;;;SY)(A;OICIID;FA;;;BA)(A;
         OICIID;FA;;;S-1-5-21-3701882691-2603361732-4164690872-1001)


PS C:\Users\Lenovo\wallet> Get-Acl .\cwallet.sso | Format-List


Path   : Microsoft.PowerShell.Core\FileSystem::C:\Users\Lenovo\wallet\cwall
         et.sso
**Owner  : DESKTOP-BDS391A\orcl_admin**
Group  : DESKTOP-BDS391A\None
Access : DESKTOP-BDS391A\Lenovo Allow  Write, Delete, Read,
         ChangePermissions, Synchronize
**         DESKTOP-BDS391A\orcl_admin Allow  FullControl**
Audit  :
Sddl   : O:S-1-5-21-3701882691-2603361732-4164690872-1018G:S-1-5-21-3701882
         691-2603361732-4164690872-513D:PAI(A;;0x17019f;;;S-1-5-21-37018826
         91-2603361732-4164690872-1001)(A;;FA;;;S-1-5-21-3701882691-2603361
         732-4164690872-1018)

PS C:\Users\Lenovo\wallet> Get-Acl .\cwallet.sso.lck | Format-List


Path   : Microsoft.PowerShell.Core\FileSystem::C:\Users\Lenovo\wallet\cwall
         et.sso.lck
**Owner  : DESKTOP-BDS391A\orcl_admin**
Group  : DESKTOP-BDS391A\None
Access : DESKTOP-BDS391A\Lenovo Allow  Write, Delete, Read,
         ChangePermissions, Synchronize
**         DESKTOP-BDS391A\orcl_admin Allow  FullControl**
Audit  :
Sddl   : O:S-1-5-21-3701882691-2603361732-4164690872-1018G:S-1-5-21-3701882
         691-2603361732-4164690872-513D:PAI(A;;0x17019f;;;S-1-5-21-37018826
         91-2603361732-4164690872-1001)(A;;FA;;;S-1-5-21-3701882691-2603361
         732-4164690872-1018)

PS C:\Users\Lenovo\wallet> Get-Acl .\ewallet.p12 | Format-List


Path   : Microsoft.PowerShell.Core\FileSystem::C:\Users\Lenovo\wallet\ewall
         et.p12
**Owner  : DESKTOP-BDS391A\orcl_admin**
Group  : DESKTOP-BDS391A\None
Access : DESKTOP-BDS391A\Lenovo Allow  Write, Delete, Read,
         ChangePermissions, Synchronize
**         DESKTOP-BDS391A\orcl_admin Allow  FullControl**
Audit  :
Sddl   : O:S-1-5-21-3701882691-2603361732-4164690872-1018G:S-1-5-21-3701882
         691-2603361732-4164690872-513D:PAI(A;;0x17019f;;;S-1-5-21-37018826
         91-2603361732-4164690872-1001)(A;;FA;;;S-1-5-21-3701882691-2603361
         732-4164690872-1018)

PS C:\Users\Lenovo\wallet> Get-Acl .\ewallet.p12.lck | Format-List


Path   : Microsoft.PowerShell.Core\FileSystem::C:\Users\Lenovo\wallet\ewall
         et.p12.lck
**Owner  : DESKTOP-BDS391A\orcl_admin**
Group  : DESKTOP-BDS391A\None
Access : DESKTOP-BDS391A\Lenovo Allow  Write, Delete, Read,
         ChangePermissions, Synchronize
**         DESKTOP-BDS391A\orcl_admin Allow  FullControl**
Audit  :
Sddl   : O:S-1-5-21-3701882691-2603361732-4164690872-1018G:S-1-5-21-3701882
         691-2603361732-4164690872-513D:PAI(A;;0x17019f;;;S-1-5-21-37018826
         91-2603361732-4164690872-1001)(A;;FA;;;S-1-5-21-3701882691-2603361
         732-4164690872-1018)

Even with all this, it still throws the error "failure to open file". Can anyone share any pointers to diagnose this error?

What I tried:

1. Changed owner of wallet folder to orcl_admin and added "Full Control" for orcl_admin.
2. Verified that the wallet path is correct by opening it in Windows run (Win + R).
3. Verified that the intended certificate is in the wallet folder.
4. Look at eventviewer > Windows Logs > Application. The event logs are somewhat cryptic and don't elaborate much on the error.

What actually resulted? The error still persists.

What I expected to happen? After changing ACLs, Owner on the wallet folder, I expected it to no longer run into "Failure to open file" error.

Answer 1

Check .ora files definitions for wallet location and authentication

#listener.ora
SSL_CLIENT_AUTHENTICATION = FALSE
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = your_wallet_folder_path)
    )
  )
-------------------------------------------------------------------------------------------  
#tnsnames.ora
WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = your_wallet_folder_path)
     )
   )
------------------------------------------------------------------------------------------ 
#sqlnet.ora
WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = your_wallet_folder_path)
     )
   )
   
SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ)
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)
-----------------------------------------------------------------------------------------

In my case the only certificate that worked with Oracle wallet (p12) was .p7c Chain certificate. I used Oracle wallet manager app to create and setup the wallet. The full answer is here.