Remote computer can’t connect to instance with backslash

connectivitysql serversql-server-2019

We are migrating two instances of SQL Server 2008 on a Win7 machine to SQL Server 2019 on a Win10 server. The old server had two instances: 123.123.123.123 and 123.123.123.123\ABC. We had no problem connecting to both instances from all of our networked computers.

On the new server the instances are: CMP0123456 and CMP0123456\ABC. We can connect to the instance without the slash, but we can't connect to the instance with the slash. The error is SQL Error 17 : Server does not exist or access denied.

Pinging CMP0123456 works for remote computers.

I've checked the configuration manager and the SQL Server and SQL Server Agent services are running for both instances. The Browser is also running.
Both instances use both TCP/IP and Shared Memory protocols. Named Pipes are disabled.

I can access both instances through the local SSMS.

I am not a DBA. I did not set up the old server or SQL Server 2008. I am only vaguely familiar with networking issues. I checked the firewall on the new server and both ports 1433 and 1434 are enabled/open. 1433 uses TCP and 1434 uses UDP.

For some reason PolyBase has Ports 1433 and 1434. I installed PolyBase, but I don't think I really need it. If this is the issue, is there a way to associate Ports 1433 and 1434 directly to SQL Server 2019?

Or is there some other setting in SQL Server and/or the Firewall/Networking/DNS that I need to change in order for remote computers to see the \ABC instance?

Best Answer

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
}
Related Question