Cannot find SQL Instance name on network PC

Question

I have been facing this type of situation a dozen of times now and final want a permanent solution to it.

Problem

Cannot find Server PC's SQL Instance name on client PC.

Note:- I have installed SQL Express And Management Studio on both server and client
Server PC Name : M-PC
Server SQL Version : SQL Server 2008
Client PC Name : SHIVANG
Client SQL Version : SQL Server 2008

Client's PC SQL Server Screenshot

Solution that i have tried

1) Switching OFF firewall of both server and client PC completely (Domain, Private & Public)
2) Enabling and restarting the services : SQL Server and Browser Service 3) Enabling SQL Network Configuration settings 4) Also enabled "Allow remote connection" from SQL Server of both the PC
5) Networking between both the PC is perfect can access files through network of both the PC.
6) Even changed the IP configuration settings from obtain to static of both the PC (IP of Server is 192.168.1.41 and IP of client is 192.168.1.44)
7) I have completely uninstalled SQL Express and Management Studio from client and server and installed it again.
8) I also told my client to format the PC and then installed SQL Express and Management Studio. Still nothing.
9) They don't have any antivirus on any of the system (to block the connection)

FUN PART

Now the fun part here is while i was fiddling with the settings of client and server PC. Server PC started showing the client PC SQL Instance while client PC was still unable to find the server SQL Instance.

Below image is of Server PC's SQL Server
Below image is of Client PC's SQL Server

Here are the following settings i have been fiddling with:-

1. Changed IP from Obtain to Static of both the PC's (client and server)
2. Changed the Protocol : TCP/IP of SQL of both the PC's (client and server) [sorry i forgot to get the screenshots of it and was fiddling with it even more when the server PC stopped showing the client's PC SQL Instance]
   
   

Now i don't have a clue what to do next to solve this problem. Please help me if you can.

Thanks in advance

Answer 1

There are two levels of security that need to be changed in order to allow remote access.

1. SQL Server configuration: By default in Express, Developer, and Enterprise Evaluation editions, connecting by the TCP/IP protocol is disabled. Enable this using SQL Server Configuration Manager.
2. Windows Firewall: While disabling the firewall entirely will work for this component, doing so is not a security best-practice (nor is it required). (Note: in this section, I assume a default configuration. There are many settings that can be changed which affect these steps slightly.)

There are two cases depending on the type of SQL Server instance you're connecting to:

1. Default instance (connect by computer name only). Add an allow incoming rule either on TCP port 1433 or the database engine service .

2. Named instance (connect by computer name + instance name). Add an allow incoming rule on UDP port 1434 to access to the SQL Browser service. Add an allow incoming rule on the database engine service.

   • Microsoft Step by Step Tutorial to Getting Started With DB Sharing

   • Reference Stack

   • A Youtube Tutorial

Answer 2

I don't have a definitive answer for your inconsistent results but I do have some background information that may help you troubleshoot the problem.

SQL instances are enumerated using the SQL Server Resolution Protocol. This is an application layer protocol that uses UDP (not TCP) to send enumeration requests to servers on the local subnet and receive responses as shown in the communication flow for multi-instance discovery diagram. It is the SQL Server Browser services that listens on UDP port 1434 to handle enumeration requests.

Importantly, the UDP multicast protocol does not guarantee requests or responses are delivered. Consequently, loss may occur that can only be detected by a network trace. The SQL Server Resolution Protocol cannot know why no response was received; it could be because a server is down, network problem, firewall, SQL instance hidden, etc. Also UDP multicast requests are delivered only to devices on the local subnet so resolution is limited to those machines.

The PowerShell script below sends a UDP request to a specific host. You can run this to ensure the SQL Browser service responds with the expected information about the instance(s) on the host.

Function List-Instances($hostName)
{

    Write-Host ("Retrieving information from SQL Server Browser service on host $HostName ...");

    try
    {
        $udpClient = new-object Net.Sockets.UdpClient($HostName, 1434);
        $bufferLength =  1;
        $browserQueryMessage = new-object byte[] $bufferLength;
        $browserQueryMessage[0] = 2; # list all instances
        $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);
        $payloadLength = $browserResponse.Length - 3;
        $browserResponseString = [System.Text.ASCIIEncoding]::ASCII.GetString($browserResponse, 3, $payloadLength);
        $browserResponseString
        $elements = $browserResponseString.Split(";");
        $elements
        $namedInstancePort = "";
        Write-host "SQL Browser Data:" -ForegroundColor Green;
        $i = 0;
        while($i -lt $elements.Length)
        {
            if([String]::IsNullOrEmpty($elements[$i]))
            {
                Write-Host "";
                $i += 1;
            }
            else
            {
                Write-Host $("    " + $elements[$i] + "=" + $elements[$i+1]) -ForegroundColor Green;
                $i += 2;
            }
        }
    }
    catch [Exception]
    {
        Write-host $("ERROR: " + $_.Exception.Message) -ForegroundColor Red;
    }

}

# send request to SQL Brower service to enumerate all SQL instances running on specific host
List-Instances -hostName "SomeMachine";

In summary, successful resolution requires:

• SQL Server Browser Service must be running
• UDP port 1434 must be allowed through the firewall
• The servers must be on the same subnet
• No UDP packet loss
• The servers respond in a timely manner