Azure SQL Database – Managed Instance enables you to directly send the email messages to the 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 the previous article, I have explained how to configure mail profile and send emails on Managed Instance. Database mail is reliable API that you can use to deliver email messages directly within the Managed Instance to external email server; however, in some cases you might experience the 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 the emails.
Troubleshooting Database mail
If you are the experiencing the issues with sending e-mail messages, try some of the following troubleshooting steps:
- Check is the option ‘Database Mail XPs’ enabled in sys.configurations view. If not, yo 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 email profile with correct email server name/IP address, port, and account information (username and password)
- Check could you reach the mail server from Managed Instance.
- Create SQL Agent job that has one PowerShell task that executes command like tns smpt.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 reached 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. 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 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 the experiencing issues with sending e-mail alerts from SQL Agent, try some of the following troubleshooting steps:
- Check do you have an email profile called AzureManagedInstance_dbmail_profile.
- Try to send an email using sp_send_dbmail procedure on 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 the potential database email issues.
- Check is there some SQL Agent limitation that is causing this issue.
- Database Mail
- Configure Database Mail
- Complete troubleshooting guide for SQL Server Databas mail
- Troubleshooting database mail failures
In the following script is shown the source code of SQL Agent job that can test network connectivity form Managed Instance to 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