How do I set up database mail for Azure SQL DB Managed Instance
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. This post will describe how to set up database mail for Azure SQL DB Managed Instance. I will use Sendgrid as the mail provider but you can follow the same steps for any other mail provider or your company’s smtp server.
Before I go on, my personal opinion is that including database mail is a massive feature for Managed Instances. 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.
Create an Azure SQL DB Managed Instance
Whilst I’m planning a separate post on how to provision an Azure SQL DB Managed Instance, I’m going to assume for the purposes of this post that you already have an instance up and running. If you don’t, have a look at this howto article before going any further.
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 sendgrid, you must open port 587 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 # and amended to be used to set up an outbound rule for Database Mail on Port 587 Login-AzAccount Get-AzSubscription Set-AzContext -SubscriptionId "yourSubscriptionID" $RGname="RG-JOHN-DEMO" $port=587 $rulename="DBMailOutbound" $nsgname="nsg-johnmccormack-demo" # 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
Set up a sendgrid account
The simplest way if you haven’t done it before is just to use the Azure Portal.
- Start typing sendgrid in the search bar
- Click on Sendgrid accounts from services
- Click + Add
- Complete the form to create your new account. (See image at bottom of post)
- Click manage sendgrid to get the credentials you will need for Database Mail (See other at bottom of post)
- Once you are logged in:
- Click Settings
- Click API Keys
- Click Create API Key
- Create and verify a sender identity. (If you don’t do this, no sendgrid emails will work)
- Once you are logged in:
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 RECONFIGURE EXEC sp_configure 'Database Mail XPs', 1 RECONFIGURE
Create a suitable database mail account and profile
Your database mail profile must be called AzureManagedInstance_dbmail_profile. If you try to call it anything else, your database mail won’t work.
-- Create a database mail account IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysmail_account WHERE [name] = 'DBA Sendgrid Account') BEGIN DECLARE @servername NVARCHAR(255) = @@servername SET @servername = substring(@servername, 1, charindex('.', @servername)-1) EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'DBA Sendgrid Account', @description = 'DB account for DBAs and SQL Agent', @email_address = 'john.mccormack@*****.***', @display_name = @servername, @mailserver_name = 'smtp.sendgrid.net', @mailserver_type = 'SMTP', @username = 'apikey', @password = '******************', @port = 587, @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 = 'DBA Sendgrid Account', @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 = 'john.mccormack@*****.***', @body = 'Email successfully sent from managed instance.', @subject = 'OK, this works now. Thats great!';
So that’s all there is to it. Now you know how to set up database mail for Azure SQL DB Managed Instance.
If you have any comments or suggestions, please tweet me or leave them in the comments section.
More handy Azure links
How do I find the agent service account for Azure SQL Database Managed Instance
Change a user’s resource class within Azure Synapse Analytics using Azure Automation runbooks
Danny Mellor says
Hi John, thanks for the great write up on this. We seem to be having an issue however when sending multiple e-mails using a query. Example below runs the first time but fails with the following error if then run again straight after;
Msg 22050, Level 16, State 1, Line 2
Failed to initialize sqlcmd library with error number -2147467259.
It then works fine but then fails again if run a short time afterwards.
@profile_name = ‘AzureManagedInstance_dbmail_profile’,
@recipients = ‘firstname.lastname@example.org’,
@subject = ‘TEST’,
@body = ‘Test’,
@execute_query_database = ‘Test DB’,
@query = ‘select getdate();’;
Doing a bit of digging it appears the SPID created in the background to run the query for the e-mail sticks around for about 1 minute & if another sp_mail is executed in that time against the same DB the SPID is closed & that is when the error appears.
This also corresponds to an error in the logs of;
The connection has been dropped because the principal that opened it subsequently assumed a new security context, and then tried to reset the connection under its impersonated security context. This scenario is not supported. See “Impersonation Overview” in Books Online.
I’m just wondering if you have the same issue or if it is something specific to our environment.
I’m guessing this has something to do with the credential which is used to impersonate the apikey account needed to sign into sendgrid as we don’t have the same issue with any of our on-prem servers.
John McCormack says
Thanks for your comment and apologies for not noticing it sooner. I haven’t come across the issue before. I think you may be best with a question on dba.stackexchange.com however if I find time later this week, I may dig into the issue.