SQL & PL/SQL

Hi All,

I am posting this here , as this is the ‘most’ relevant category for it (i guess)

Please note that I am not deeply knowledgeable about the wallets etc in Oracle, but I'm giving it a try, so be lineant with me if my issue is an obvious one !

My case:

I am running Oracle 19c on Win 2019.

I need to send emails using TLS to smtp-us.ser.proofpoint.com , port 587.

I am having an issue primarily with the ‘ssl handshake’ and i think this because of the error (ORA-28759: failure to open file) which the system cannot open the wallet to read the ‘root+intermediate certificates’ ..

What i have implemented.

• I know that i need to setup the ACLs for this and i have done so (I show the ACL entries below).
• I have created a wallet under d:\wallet_base\wallet for testing this connectivity (using orapki. I have created the wallet in many cases with and without -auto_login - as to create the .sso file , just in case).
• i have also, changed the location of the wallet (and also left it in the ‘default’ location just in case that was the fault, but no joy so i moved it back to d:\wallet_base\wallet
• Windows permissions on the folder path d:\wallet_base\wallet and also the files within are all ok, as i can access the file from oracle (i show this further down)
• the wallet location is specified in sqlnet.ora as WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = D:\wallet_base\wallet)))
• if i issue SELECT * FROM V$ENCRYPTION_WALLET; then i get the output below (which i am not sure if it means anyting in my case as this is the ‘TDE encryption wallet' and not a ‘ssl wallet’ (I might be wrong here as im no expert)
• I have also made sure that no other services etc have a handle ‘lock’ on the files (using windows Resource Monitor)
• 

I know the ‘database’ can access the wallet files as, if i issue the below, they can be read (so they do exist and they are accessible by the ‘database’

I have added the Root and intermediate cert in the wallet, (i have even added the actual certificate from the url above, obtained using openssl during my troubleshooting..but i guess my main issue is the wallet accesibility error I am getting.

so. to take out only the necessary bits to connect to the proofpoint relay i have the following script (i know the code below is incomplete, but i am taking it in steps ):

set serveroutput on; 
clear screen; 

DECLARE 
l_conn UTL_SMTP.connection; 
BEGIN 
--UTL_TCP.CLOSE_ALL_CONNECTIONS; 
DBMS_OUTPUT.PUT_LINE('lets try'); 
l_conn := UTL_SMTP.open_connection( 
host => 'smtp-us.ser.proofpoint.com', 
port => 587, 
wallet_path => 'file:/D:/wallet_base/wallet', 
wallet_password => 'mypasshere', 
secure_connection_before_smtp => false); 

UTL_SMTP.STARTTLS(l_conn); 
-- UTL_SMTP.ehlo(l_conn, 'my_domain'); 
-- UTL_SMTP.quit(l_conn); 

DBMS_OUTPUT.PUT_LINE('Connection successful'); 

DBMS_OUTPUT.PUT_LINE('PROC END !!!!'); 


EXCEPTION 
WHEN OTHERS THEN 
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM); 
END; 
/

I of course played around with the above,

1. If i run as is, then i get the ORA-28759: failure to open file .
2. If I comment out jus the utl_smtp.starttls then no attempt to start TLS handshake takes place, hence no need to read certificates i guess from the wallet so no error in thrown in this case.
3. If i comment out the utl_smtp.starttls and change the secure_connection_before_smtp to true I then get the ORA-28759 again, as I guess it tries to handhake with relay using wallet etc, and it cannot access it…
4. also tried specifying the wallet location in utl_smtp.open_connection with ‘real’ password and wallet_password=>null (as i have an .sso file now with the -auto_login option of orapki) but still the same issue…

So…i am tottally lost…at a first glance it seems that my problem is that when using utl_smtp procedures, the wallet cannot be accessed, but i can access each individual file of the wallet using UTL_FILE.FOPEN !

for posterity i show the ACL created for this (replaced my schema user with ‘SCHEMA_USER’

Any ideas please !

thank you in advance,

George

My ACLs for this scenario: (the alst two ones i found from various searches on the internet , which might not be helping me at all)

BEGIN
  DBMS_NETWORK_ACL_ADMIN.drop_ACL( acl=> 'proofpoint_email.xml');
end;
    

BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
    acl         => 'proofpoint_email.xml',
    description => 'ACL for sending emails',
    principal   => 'SCHEMA_USER',
    is_grant    => TRUE,
    privilege   => 'connect'
  );
END;
/

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
    acl  => 'proofpoint_email.xml',
    host => 'smtp-us.ser.proofpoint.com',
    lower_port => 587,
    upper_port => 587
  );
END;
/

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl       => 'proofpoint_email.xml',
    principal => 'SCHEMA_USER',
    is_grant  => TRUE,
    privilege => 'connect'
  );
END;
/
BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl       => 'proofpoint_email.xml',
    principal => 'SCHEMA_USER',
    is_grant  => TRUE,
    privilege => 'resolve'
  );
END;
/


---------------------------------
--someone suggests this permisions too
BEGIN
   DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
    acl          => 'proofpoint_email.xml',
    principal    => 'SCHEMA_USER',
    is_grant     => TRUE, 
    privilege    => 'use-client-certificates');

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl          => 'proofpoint_email.xml',
    principal   => 'SCHEMA_USER',
    is_grant    => TRUE,
    privilege   => 'use-passwords');
  COMMIT;
END;
/