Azure SQL Database Managed Instance enables you to directly send email messages to external email servers. You can send email messages directly using sp_send_dbmail procedure or via SQL Agent Jobs as alerts. Learn more about Database Mail here.
In a previous article, I have explained how to configure mail profile and send emails on Managed Instance. Database mail is a reliable API that you can use to deliver email messages directly from Managed Instance to an external email server; however, in some cases you might experience issues due to incorrect configuration of mail account, network connection, etc.
In this article you will learn how to troubleshoot the potential issues with DbMail if you cannot send emails.
Troubleshooting Database mail
If you are experiencing issues sending e-mail messages, try some of the following troubleshooting steps:
- Check if 'Database Mail XPs' option enabled in sys.configurations view. If not, you can enable it using the following script:
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Database Mail XPs', 1; GO RECONFIGURE GO
- Check have you correctly configured an email profile with a correct email server name/IP address, port and account information (username and password)
- Check if you can reach the mail server from Managed Instance.
- Check in the Network Security Group associated to the subnet where your database mail server is placed do you have inbound rule that allows communication via TCP port 25.
- Check in the Network Security Group associated to the Managed Instance subnet do you do have outbound rule that allows communication via TCP port 25:
- Make sure that subnet range where your Managed Instance is placed is configured as allowed sender in your target mail server. Otherwise, your mail server will not be able to receive the messages that Managed Instance Database Mail sends.
- If you are using custom DNS for mail server make sure you update Azure DNS with the DNS record of the mail server.
- Create a SQL Agent job that has one PowerShell task that executes command like
tns smtp.sendgrid.net -25(see the example below), run the job and check the job output in the job history. This is public mail server that should be reachable from your Managed Instance, unless if you have explicitly blocked that name and/or port. - Replace the name of the mail server and/or port in the job with the mail server and port that you are using and repeat the previous step.
- Check have you enabled the port that is used to communicate with the email server; the port should be added in the Outbound security rules
- Check the status of E-Mail messages sent with database mail in Database Mail Log, msdb.dbo.sysmail_event_log, and msdb.dbo.sysmail_faileditems.
- Script the email profile that you are using on Managed Instance, setup the identical email profile on SQL Server and try to send the email there. If possible, try to place SQL Server in a Azure Virtual machine in the same VNet where your Managed Instance is placed (in different subnet) to ensure that you have similar networking environment.
- Find more information in Troubleshooting Database Mail article.
Troubleshooting SQL Agent email alerts
If you are experiencing issues with sending e-mail alerts from SQL Agent, try some of the following troubleshooting steps:
- Check if you have an email profile called AzureManagedInstance_dbmail_profile.
- Try to send an email using sp_send_dbmail procedure using the AzureManagedInstance_dbmail_profile profile with T-SQL script.
DECLARE @body VARCHAR(4000) = 'The email is sent from ' + @@SERVERNAME;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AzureManagedInstance_dbmail_profile',
@recipients = 'ADD YOUR EMAIL HERE',
@body = 'Add some text',
@subject = 'Azure SQL Instance - test email' ;
- Repeat the steps from the previous section to troubleshoot potential database email issues.
- Check if there is some SQL Agent limitation that is causing this issue.
Recommended links
- Database Mail
- Configure Database Mail
- Complete troubleshooting guide for SQL Server Database mail
- Troubleshooting database mail failures
The following script shows the source code of SQL Agent job that can test network connectivity from Managed Instance to a mail server.
USE [msdb] DECLARE @jobId BINARY(16) EXEC msdb.dbo.sp_add_job @job_name=N'test DbMail', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'No description available.', @job_id = @jobId OUTPUT EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Test mail server', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'PowerShell', @command=N'tnc smtp.sendgrid.net -port 25', @database_name=N'master', @flags=0
EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'