can not send email from azure manged instance database mail

Question

set up database email profile and account in the azure manged instnace, however, when i send email, it never send. i have same settin gin premier sql server without issue.

our azure manged instance is using private node.

smpt auto relay email

Answer 1

Hi David Zhou,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

As we understand that, you are having trouble sending emails from your Azure SQL Managed Instance, even though the same settings work fine on your on-premises SQL Server.

Here are some steps and considerations that might help resolve the issue:

• Ensure that your Managed Instance can reach your mail server. If your email server uses port 25, you need to open the outbound Network Security Group (NSG) on port 25 to the Internet. Since your Managed Instance is using a private node, make sure that the necessary network configurations are in place to allow outbound traffic to your mail server.
• You need to set up the email account information, including the address of the email server, login, and password. Here is an example script to create a Database Mail account and profile.

-- Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp  
    @account_name = 'YourAccountName',  
    @description = 'YourDescription',  
    @email_address = 'YourEmailAddress',  
    @display_name = 'YourDisplayName',  
    @mailserver_name = 'YourMailServer',  
    @username = 'YourEmailAddress',  
    @password = 'YourPassword';
-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp  
    @profile_name = 'AzureManagedInstance_dbmail_profile',  
    @description = 'YourProfileDescription';
-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp  
    @profile_name = 'AzureManagedInstance_dbmail_profile',  
    @account_name = 'YourAccountName',  
    @sequence_number = 1;

Ensure that the Database Mail extended stored procedures are enabled:

EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
EXEC sp_configure 'Database Mail XPs', 1;  
GO  
RECONFIGURE;  
GO

You can test the configuration by sending a test email using the sp_send_dbmail stored procedure:

DECLARE @body VARCHAR(4000) = 'The email is sent with msdb.dbo.sp_send_dbmail from ' + @@SERVERNAME;  
EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'AzureManagedInstance_dbmail_profile',  
    @recipients = 'RecipientEmailAddress',  
    @subject = 'Test Email',  
    @body = @body;

If you are using SQL Agent jobs to send emails, ensure that the Database Mail profile is named AzureManagedInstance_dbmail_profile.

If you have followed these steps and the issue persists, it might be helpful to check the error logs for any specific error messages that could provide more insight into why the emails are not being sent.

Please refer to the below mentioned links for more information.

https://techcommunity.microsoft.com/blog/azuresqlblog/sending-emails-in-azure-sql-managed-instance/386235

https://learn.microsoft.com/en-us/sql/relational-databases/database-mail/configure-sql-server-agent-mail-to-use-database-mail?view=sql-server-ver16

https://learn.microsoft.com/en-us/troubleshoot/sql/tools/troubleshoot-database-mail-issues?source=recommendations

I hope, This response will address your query and helped you to overcome on your challenges.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.