locked
Enumerate SQL Server Instances on my (Workgroup) network returns "nothing"'? RRS feed

  • Question

  • In SSMS v18.4, I try "Browse for more" and end up with this dialog box :

    Then, after a while (20 seconds or so), the words "....Retrieving data…." disappears and nothing further happens.

    I turned all firewalls off on all machines on my network to no avail.

    Is there any options I have to activate on each server to make sure they can find each other?

    I made sure I have "SQL Browser" Service turned On, on every machine (it was turned off by default).

    Wednesday, April 22, 2020 7:18 PM

Answers

  • Eureka!!!

    I ended up installing SQL Server 2014 Express "fresh" on a new Win10-64 machine (in case?). 

    At first, "naturally", it did not work -- sigh! I could not connect. 

    But then I read on some more in this page, in Stack Overflow :

    https://stackoverflow.com/questions/6987709/unable-to-connect-to-sql-server-instance-remotely

    Particularly, the comments with regards to adding Port 1434 and adding the two programs, SQLSERVER.EXE and SQLBROWSER.EXE as exceptions  to the Firewall rules, on the machine running the fresh, new Server 2014 installation. Once I did that, I stopped the SQL Server and SQL Browser services and restarted them. 

    Then I went back to SSMS 18.4, on my machine (XPS8930), and tried to connect through 'Browse for more' and, after holding my breath for the 20th time today -- tada!!!, I got the following:

    As you can see, the two SQLEXPRESS instances are listed above. Not for the faint of heart :(

    • Marked as answer by roger.breton Wednesday, April 22, 2020 11:36 PM
    Wednesday, April 22, 2020 11:36 PM

All replies

  • Small progress… I now have this :

    I would have expected to see other machines in this appear in this window but it's a step in the right direction.

    One possible explanation is that I'm not running on a Domain but Workgroup? I came across this idea on my search on the internet? 

    First, I found out all the machines on my network did not have "Network discovery" turned on. So, after turning that feature on, on all of them, they all showed up under Desktop > Network:

    I also visited SQL Server Configuration on each machines. This is my "Config" dialog:

    Double-clic on 'Protocols' and made sure everything is Enabled:

    Only "Shared Memory" was Enabled?

    Then, under TCP/IP, I made sure select "Enable = Yes" It was no before:

    Then, under IP1 and IP All, I typed in 1433 for TCP Port while making sure Enabled = Yes:

    Of course, I add one rule for Inbound connection and one rule for outbound connection in my Firewall for Port 1433.

    Then I restarted SQL Server services on both machines.

    Still… I only see MY instance of MY SQL Server in that "Browse for more" dialog...

    Wednesday, April 22, 2020 9:15 PM
  • One more thing… I tried connecting to the 1433 port of the remote machine through PowerShell and got the following:

    It looks like PowerShell is able to connect to the remote machine 1433 port successfully.

    Wednesday, April 22, 2020 9:32 PM
  • Eureka!!!

    I ended up installing SQL Server 2014 Express "fresh" on a new Win10-64 machine (in case?). 

    At first, "naturally", it did not work -- sigh! I could not connect. 

    But then I read on some more in this page, in Stack Overflow :

    https://stackoverflow.com/questions/6987709/unable-to-connect-to-sql-server-instance-remotely

    Particularly, the comments with regards to adding Port 1434 and adding the two programs, SQLSERVER.EXE and SQLBROWSER.EXE as exceptions  to the Firewall rules, on the machine running the fresh, new Server 2014 installation. Once I did that, I stopped the SQL Server and SQL Browser services and restarted them. 

    Then I went back to SSMS 18.4, on my machine (XPS8930), and tried to connect through 'Browse for more' and, after holding my breath for the 20th time today -- tada!!!, I got the following:

    As you can see, the two SQLEXPRESS instances are listed above. Not for the faint of heart :(

    • Marked as answer by roger.breton Wednesday, April 22, 2020 11:36 PM
    Wednesday, April 22, 2020 11:36 PM