Each SQL Server instance listens on a different port. Your default instance is listening on port 1433 but the named instance must listen on a different port to avoid conflicts, which is dynamically assigned by default.
Make sure the firewall allows TCP connections for the named instance port as well. The dynamic port number can be identified using SQL Server Configuration Manager (C:\Windows\System32\SQLServerManager15.msc) and is also logged to the SQL Server error log at startup (e.g. "Server is listening on ['any' 12345]").
To avoid connectivity issues should the named instance dynamic port number change, assign a static port number to the named instance (along with the TCP firewall rule) or create a firewall rule for the named instance SQL Server application executable (e.g. C:\Program Files\Microsoft SQL Server\MSSQL15.ABC\MSSQL\Binn\sqlservr.exe").
Run PowerShell commands below on a remote machine to verify TCP port connectivity for each instance.
Test-NetConnection 123.123.123.123 -Port 1433 # default instance
Test-NetConnection 123.123.123.123 -Port 12345 # specify named instance port number
The SQL Server Browser is used when an instance name (rather than port number) is specified in the client connection string. The client API sends a UDP 1434 request to the browser service, which returns a datagram that includes the current named instance port number for subsequent use. The implication is the SQL Server Browser service be running and UDP 1434 allowed through the firewall in order to connect to a named instance by name.
Unfortunately, there isn't a built-in PowerShell command to test remote UDP port connectivity (at least that I'm aware of). But with the help of .NET objects, one can verify remote SQL Server Browser connectivity with the help of this script, which sends a UDP 1434 browser query (reverse engineered from a network trace ) to get info for the specified named instance.
# verify UDP port 1434 connectivity and query SQL Server Browser for single instance
$hostNameOrIpAddress = "CMP0123456"
$instanceName = "ABC"
try
{
Write-Host "Quering SQL Browser for host $hostNameOrIpAddress, instance $instanceName ..."
$instanceNameBytes = [System.Text.Encoding]::ASCII.GetBytes($instanceName)
$udpClient = New-Object Net.Sockets.UdpClient($hostNameOrIpAddress, 1434)
$bufferLength = $InstanceNameBytes.Length + 2
$browserQueryMessage = New-Object byte[] $bufferLength
$browserQueryMessage[0] = 4
$instanceNameBytes.CopyTo($browserQueryMessage, 1)
$browserQueryMessage[$bufferLength-1] = 0
$bytesSent = $udpClient.Send($browserQueryMessage, $browserQueryMessage.Length)
$udpClient.Client.ReceiveTimeout = 10000
$remoteEndPoint = New-Object System.Net.IPEndPoint([System.Net.IPAddress]::Broadcast, 0)
$browserResponse = $udpClient.Receive([ref]$remoteEndPoint)
Write-Host "SQL Server Browser response received" -ForegroundColor Green
$payloadLength = $browserResponse.Length - 3
$browserResponseString = [System.Text.ASCIIEncoding]::ASCII.GetString($browserResponse, 3, $payloadLength)
Write-Host "SQL Server Browser raw datagram value: $browserResponseString"
$elements = $browserResponseString.Split(";")
$namedInstancePort = ""
Write-Host "SQL Server Browser parsed datagram:`r`n"
for($i = 0; $i -lt $elements.Length; $i = $i + 2)
{
if ($elements[$i] -ne "")
{
Write-Host "`t$($elements[$i])=$($elements[$i+1])"
if($elements[$i] -eq "tcp")
{
$namedInstancePort = $elements[$i+1]
}
}
}
}
catch [Exception]
{
Write-Host "ERROR: $($_.Exception.Message)" -ForegroundColor Yellow
}