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:

Setting up DB email and getting email delivered successful looks quite simple in on-premises SQL server.

Even while configuring DB email on Azure SQL managed instance is also simple but need to care about some of the important points.

Here, while working on setting up same configuration we receives errors and troubleshooted the error and thought to share with you all so that while reading through this article, would be somewhat helpful for audiences.

Source:- Azure SQL managed Instance

Target SMTP server :- On-premises exchange server.

Error receives:-

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2023-04-20T11:07:58). Exception Message:

Exception Type: Microsoft.SqlServer.Management.SqlIMail.MailFramework.Exceptions.BaseMailFrameworkException

Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond [SMTP IP Address]:Port)

Data: System.Collections.ListDictionaryInternal

TargetSite: Void CheckServerValidity()

HelpLink: NULL

Source: DatabaseMailProtocols

HResult: -2146232832

SQL FQDN: xyz.database.windows.net

Exchange server: SMTP server name

In SQL Public endpoint – disabled.

Managed instance is configured to use subnet: [Subnet range]

---

Root cause:- Missing/wrong firewall configurations.

Resolution:

When disabling the public IP for an Azure SQL Managed Instance, it will continue to function using its private IP address. The private IP is assigned during the creation of the Managed Instance and falls within the specified IP subnet range.

To ensure proper functionality, it is recommended to create an exception for the entire subnet rather than a single IP address for the Managed Instance. This is because the Managed Instance may be restarted or relocated to a different host during Azure maintenance processes. If an exception is only created for a single IP address and that address changes, the firewall rule will no longer be effective and requires updating. However, if the exception covers the entire subnet, there is no need to update the firewall rule when the IP address of the Managed Instance changes. Additionally, creating a subnet exception allows other resources within the same subnet to communicate with the Managed Instance, which may be necessary for certain applications.

Firewall exceptions were created for the entire Azure SQL Managed Instance subnet and not for specific IP, as follows:

• Outbound traffic rule on Azure SQL Managed Instance portal to the Exchange Server for the entire subnet pool – [subnet range]. Login to Azure Portal–>Go to Managed instance –>Go to VNET–>Subnet–> NSG–>Outbound security rules–>Create new rule
• Inbound traffic to the on-premises Exchange Server for the entire Azure SQL Managed Instance subnet pool – [same subnet range]. Take help from FW or network team.
• All intermediate firewall and other network devices should allow all traffic for the entire Azure SQL Managed Instance subnet pool – [same subnet range]. Take help from FW or network team

Happy Learning!