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.
If you are the experiencing the issues with 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 reached from your Managed Instance, unless if you have explicitly blocked that name and/or port.
If you are the 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' ;
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
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.