Hi,
We have been working on a ticket where customer was receiving errors when his Managed Instance was trying to send mails.
He was implemented this functionality time ago, but suddenly had started to fail with error "No such host is known"
Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2022-09-22T01:00:45). Exception Message:
1) Exception Information
===================
Exception Type: Microsoft.SqlServer.Management.SqlIMail.MailFramework.Exceptions.BaseMailFrameworkException
Message: Could not connect to mail server. (No such host is known)
Data: System.Collections.ListDictionaryInternal
TargetSite: Void CheckServerValidity()
HelpLink: NULL
Source: DatabaseMailProtocols
HResult: -2146232832
In order to test network connectivity from Managed Instance to a mail server, we used following script included on "Troubleshooting Database Mail issues in Azure SQL Managed Instance" article and confirmed that name resolution was not working correctly from MI.
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, _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
EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, _name = N'(local)'
Reviewing Managed Instance VNET we could see that it was using custom DNS, and also, we confirmed that those DNS servers were able to resolve DNS name related to mail server. As a workaround, to get the Database mail system working customer replaced FQDN of mail server with IP address. So where was the issue? Why Manage Instance was not able to resolve DNS mail server name, if DNS records where there? The answer was that customer was updated DNS servers for the VNET but MI was not aware about this.
If the DNS server setting is changed in a virtual network which already hosts SQL Managed Instances, SQL Managed Instances in that network must also be notified of this change. You can do it using Azure CLI or Azure PowerShell scripts included on article Resolve private domain names in Azure SQL Managed Instance
Azure PowerShell
$ResourceGroup = 'enter resource group of virtual network'
$VirtualNetworkName = 'enter virtual network name'
$virtualNetwork = Get-AzVirtualNetwork -ResourceGroup $ResourceGroup -Name $VirtualNetworkName
Get-AzSqlVirtualCluster `
| where SubnetId -match $virtualNetwork.Id `
| select Id `
| Invoke-AzResourceAction -Action updateManagedInstanceDnsServers -Force
Azure CLI
resourceGroup="auto-failover-group"
virtualNetworkName="vnet-fog-eastus"
virtualNetwork=$(az network vnet show -g $resourceGroup -n $virtualNetworkName --query "id" -otsv)
az sql virtual-cluster list --query "[? contains(subnetId,'$virtualNetwork')].id" -o tsv \
| az resource invoke-action --action updateManagedInstanceDnsServers --ids @-
After execute PowerShell script, MI was able to resolve DNS mail server name and started to send mails again.
Here you have useful Information used to configure sending mails and troubleshooting it.
Troubleshooting Database Mail issues in Azure SQL Managed Instance
Sending emails in Azure SQL Managed Instance
Regards, Paloma.-
