Set up and troubleshoot database mail for Azure SQL DB Managed Instance

Disclaimer: Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

InterServer Web Hosting and VPS

The lack of DB Mail on Azure SQL DB Single Database or Amazon RDS is a major blocker to PaaS adoption. Now with Managed Instance, we can have PaaS and database mail.

It’s not too difficult to set up database mail for Azure SQL DB Managed Instance in comparison to SQL Server (on-prem or IaaS) however there are a few extra things to consider.


I just hope that we already have SQL managed instance deployed or setup under required resource group.

Change outbound settings on your Network Security Group (NSG)

All managed instances are created with a NSG. The NSG allows you to create inbound and outbound rules for traffic coming in and out of your database. You can configure ports, protocols and IP ranges etc.

To allow secure smtp for your mail server, you must open SMTP port for outbound traffic. Otherwise, any attempt to send mail will result in an error. You can do this in the Azure Portal by navigating to the NSG or you could do it in PowerShell by modifying and running the code below.

# This code was originally taken from

Set-AzContext -SubscriptionId "yourSubscriptionID"

$RGname="Your RG_Name"

# Get the NSG resource
$nsg = Get-AzNetworkSecurityGroup -Name $nsgname -ResourceGroupName $RGname

# Add the inbound security rule
$nsg | Add-AzNetworkSecurityRuleConfig -Name $rulename -Description "Allow DBMail" -Access Allow `
-Protocol * -Direction Outbound -Priority 500 -SourceAddressPrefix "*" -SourcePortRange * `
-DestinationAddressPrefix * -DestinationPortRange $port

# Update the NSG
$nsg | Set-AzNetworkSecurityGroup
Enable database mail for your Managed Instance

You must run this t-sql code to enable database mail on your Managed Instance:

EXEC sp_configure 'show advanced options', 1

EXEC sp_configure 'Database Mail XPs', 1
Create a suitable database mail account and profile


The important thing is that you can use any name for the DbMail profile (and you can have several db Mail profiles) for Db Mail procedures. However, if you want to send e-mail using SQL Agent jobs, there should be a profile that must be called ‘AzureManagedInstance_dbmail_profile‘. Otherwise, Managed Instance will be unable to send emails via SQL Agent. If you are using one  profile in your instance and you want to use it both for classic emails and SQL Agent, rename the profile to ‘AzureManagedInstance_dbmail_profile‘ so it can be used on both places.

-- Create a database mail account
IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysmail_account WHERE [name] = 'DBAMailAccount')
DECLARE @servername NVARCHAR(255) = @@servername
SET @servername = substring(@servername, 1, charindex('.', @servername)-1)
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DBAMailAccount',
@description = 'DB account for DBAs and SQL Agent',
@email_address = '',
@display_name = @servername,
@mailserver_name = 'SMTP Server Name',
@mailserver_type = 'SMTP',
@username = 'Your UserName',
@password = '******************',
@port = 25,
@enable_ssl = 1;
PRINT 'sysmail_account already configured'

-- Create a database mail profile (Profile must be called AzureManagedInstance_dbmail_profile)
IF NOT EXISTS (SELECT 1 FROM msdb..sysmail_profile WHERE name = 'AzureManagedInstance_dbmail_profile')
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'AzureManagedInstance_dbmail_profile',
@description = 'Main profile for sending database mail';

-- Associate account with profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'AzureManagedInstance_dbmail_profile',
@account_name = 'DBAMailAccount',
@sequence_number = 1 ;
PRINT 'DBMail profile already configured'
Send a test email
-- Test Email
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AzureManagedInstance_dbmail_profile',
@recipients = '',
@body = 'Email successfully sent from managed instance.',
@subject = 'OK, this works now. Thats great!';

For more info and help on troubleshooting email issue, follow the MS sites.

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s