0

I'm trying to setup UTL_MAIL through Oracle to email users regarding account deactivation's. Below is what i've done so far -

Installed the package - @utlmail.sql & @prvtmail.plb

Set smtp parameter - ALTER SYSTEM SET smtp_out_server = 'smtp.mail.com' SCOPE = BOTH;

Completed ACL setup:-
exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('utl_mail.xml','Allow mail to be send','SCHEMA', TRUE, 'connect');
commit;
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('utl_mail.xml','SCHEMA', TRUE, 'connect');
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('utl_mail.xml','SCHEMA', TRUE, 'resolve');
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('utl_mail.xml','*',25);
commit;

I've then create a test procedure just to see if this all works

CREATE OR REPLACE PROCEDURE deactivated_email IS

    vsender   VARCHAR2(30) := 'sender@sender.edu';
    vrecip    VARCHAR2(30) := 'recip@recip.edu';
    vsubj     VARCHAR2(50) := 'Enter the subject here';
    vmesg     VARCHAR2(4000) := 'Enter the body';
    vmtype    VARCHAR2(30) := 'text/plain; charset=us-ascii';
BEGIN
    utl_mail.send(
        vsender,
        vrecip,
        NULL,
        NULL,
        vsubj,
        vmesg,
        vmtype,
        NULL
    );
END;

Execute the proc

EXECUTE deactivated_email;

Error received

BEGIN deactivated_email; END;
Error report -
ORA-29279: SMTP permanent error: 530 5.7.57 SMTP; Client was not authenticated to send anonymous mail during MAIL FROM [smtp.mail.com]
ORA-06512: at "SYS.UTL_MAIL", line 662
ORA-06512: at "SYS.UTL_MAIL", line 679
ORA-06512: at "DEACTIVATED_EMAIL", line 17
ORA-06512: at line 1
29279. 00000 -  "SMTP permanent error: %s"
*Cause:    A SMTP permanent error occurred.
*Action:   Correct the error and retry the SMTP operation.

I had our exchange team open up the smtp over 25 for the VM i'm working on and i can do a sendmail through RHEL host just fine so the box is open. I'm guessing the smtp is requiring some kind of authentication from the utl_mail package/oracle but i've been unable so far to figure out where i can pass this authentication via the package/oracle

Any feedback is appreciated

phemor
  • 299
  • 6
  • 17
  • 1
    If your SMTP server requires authentication, I'd suggest using UTL_SMTP instead. I think UTL_MAIL only supports open mail relays. – kfinity Oct 30 '17 at 16:52
  • kfinity is correct. This is an issue with what you're sending to the SMTP server, not the database. You will have to work with the Exchange team to determine requirements and make those configuration changes in the database. – 1991DBA Oct 30 '17 at 17:15
  • Turns out the SMTP host i was using required authentication still even though the VM was open for 25 anonymous. Provided a different SMTP host alias and it works fine now. – phemor Oct 30 '17 at 18:02

1 Answers1

0

Turns out the SMTP host i was using required authentication still even though the VM was open for 25 anonymous. Provided a different SMTP host alias and it works fine now.

phemor
  • 299
  • 6
  • 17