Troubleshooting Database Mail issues in Azure SQL Managed Instance

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

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 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, 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 Logmsdb.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.
  • 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.

Recommended Document

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

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.