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.
If you are experiencing issues sending e-mail messages, try some of the following troubleshooting steps:
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Database Mail XPs', 1; GO RECONFIGURE GO
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 reachable from your Managed Instance, unless if you have explicitly blocked that name and/or port.
If you are experiencing issues with sending e-mail alerts from SQL Agent, try some of the following troubleshooting steps:
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' ;
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)'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.