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.
Introduction:
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.
Note:
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 https://docs.microsoft.com/en-us/azure/service-fabric/scripts/service-fabric-powershell-add-nsg-rule
Login-AzAccount
Get-AzSubscription
Set-AzContext -SubscriptionId "yourSubscriptionID"
$RGname="Your RG_Name"
$port=25
$rulename="DBMailOutbound"
$nsgname="nsg_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-AzNetworkSecurityGroupEnable 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
RECONFIGURE
EXEC sp_configure 'Database Mail XPs', 1
RECONFIGURECreate a suitable database mail account and profile
Note
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')
BEGIN
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 = 'Support@adsql.in',
@display_name = @servername,
@mailserver_name = 'SMTP Server Name',
@mailserver_type = 'SMTP',
@username = 'Your UserName',
@password = '******************',
@port = 25,
@enable_ssl = 1;
END
ELSE
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')
BEGIN
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 ;
END
ELSE
PRINT 'DBMail profile already configured'
Send a test email
-- Test Email
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AzureManagedInstance_dbmail_profile',
@recipients = 'support@adsql.in',
@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.
ADsql-Tech Forever 