Send Email from Autonomous AI Database with Microsoft Exchange using OAuth2

Oracle Autonomous AI Database supports sending email through Microsoft Exchange by using OAuth 2.0.

To use OAuth2, you first need to register an application in Azure, assign the correct API permissions, create a secret for the application, and create a credential object in Autonomous AI Database. Once configured, you can send messages using DBMS_CLOUD_NOTIFICATION.

Steps for sending email using DBMS_CLOUD_NOTIFICATION

1. Register an application in Microsoft Entra (Azure AD).

   In the Azure portal, create a new app registration and note the Application (client) ID and Azure Tenant ID. For details, see Microsoft guidance on registering an application.

2. Create a secret in client credentials for the application.

   The client ID and the client secret are needed to create a secure credential object in your database that is used to identify your database as the registered application that can send emails.

3. Add the Mail.Send permission and grant admin consent.

   In Azure, under API permissions, add the Mail.Send application permission in Microsoft Graph and have an administrator grant consent.

Your environment is now configured to send emails using the DBMS_CLOUD_NOTIFICATION package with any valid email account of your Microsoft Exchange environment.

Send Email

Use DBMS_CLOUD_NOTIFICATION.SEND_MESSAGE with the provider attribute set as email using a valid email account and your credential with the client ID or secret that was registered as a trusted application to send email.

1. Create a credential that stores the client ID and secret:

   BEGIN
     DBMS_CLOUD.CREATE_CREDENTIAL(
       credential_name => 'MS_TOKEN',
       username        => '<client_id>',
       password        => '<client_secret>');
   END;
   /

2. Send an email using the client credentials flow:

   BEGIN
     DBMS_CLOUD_NOTIFICATION.SEND_MESSAGE(
       provider        => 'email',
       credential_name => 'MS_TOKEN',
       message         => 'Message content',
       params          => json_object(
                            'recipient' value '[e-mail]',
                            'subject'   value 'Test subject',
                            'smtp_host' value 'smtp.office365.com',
                            'sender'    value '[e-mail]',
                            'tenant'    value '<azure_tenant_id>'));
   END;
   /

Troubleshooting

• Port connectivity: SMTP connections require outbound TCP port 587. If you receive ORA-29278: SMTP transient error: 421 Service not available, verify that network ACLs allow outbound connections to smtp.office365.com:587.

• Missing permissions: Authentication failures often occur when the application has not been granted admin consent or when the service principal has not been registered in Exchange. Revisit the steps to grant permissions and register the service principal.

• SMTP AUTH disabled: If you cannot authenticate after obtaining tokens, ensure that SMTP AUTH is enabled for the mailbox and not globally disabled.

See Also:

• Microsoft documentation on Authenticate an IMAP, POP or SMTP connection using OAuth.

• Microsoft Learn article on Microsoft identity platform and OAuth 2.0 authorization code flow.

• Microsoft Learn article on Enable or disable SMTP AUTH in Exchange Online.