Search code examples
c#.netsql-serverenumerationsqldatasource

.NET not showing instance or version when enumeration is done via SqlDataSourceEnumerator.Instance.GetDataSources()

I'm attempting to enumerate all SQL instances locally or on the network using SQLDataSourceEnumerator.Instance.GetDataSources(). A data table is returned but only the ServerName column has data. The InstanceName, IsClustered and Version columns are empty.

Running the same code on the server in the same network shows valid results for all columns noted.

Note that the client system has the following installed:

  • (A) Visual Studio 2019 Community Edition Version 16.1.6
  • (B) .NET 4.8.03752
  • (C) OS = Windows 10 Pro Version 1903 (18362.239 build)
  • (D) NuGet package Microsoft.SqlServer.SqlManagementObjects installed with version 150.18118.0

Here is what I've tried and checked, and I've been researching this for a couple of days and I've exhausted everything that I've found...

  1. SQL Browser service on SQL server side is running on all instances.
  2. All firewalls are OFF on both the server side and client side.
  3. SQL instances running on Windows 10 Pro versions 1803 (17134.885) and 1903 (18362.239)
  4. SQL configuration of all instances have TCP/IP enabled.
  5. since firewalls are OFF, UDP port 1434 is not being blocked.
  6. Servers are on same network segment.
  7. I can log into the SQL instances from client system via SSMS.
  8. SQL instances are running Dev Edition of SQL Server versions 2014, 2016, and 2017.
  9. I've called GetDataSources() more than once in code as I've read that output across results can vary from one call to the next.
  10. I have also tried Microsoft.SqlServer.Management.Smo.SmoApplication.EnumAvailableSqlServers with the same results, empty columns.
  11. The feature in Windows 10, .NET Framework 3.5 (Includes .NET 2.0 and 3.0) has been enabled and disabled, same results, empty columns.

In .NET I use the following:

Dim AllEnumeratedSQLInstancesTable2 As DataTable = New DataTable()
AllEnumeratedSQLInstancesTable2 = instance.GetDataSources()

In PowerShell I use this:

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

The output from Instance.GetDataSources() is this on the client side:

ServerName      InstanceName IsClustered Version
----------      ------------ ----------- -------
DESKTOP-SQL2K14
DESKTOP-SQL2K16
DESKTOP-SQL2K17

Now, when I run this on any of the SQL server systems above, I get the following, which is what I'm expecting on the client side:

ServerName      InstanceName IsClustered Version
----------      ------------ ----------- -------
DESKTOP-SQL2K14              No          12.0.6024.0
DESKTOP-SQL2K16              No          13.2.5026.0
DESKTOP-SQL2K17              No          14.0.1000.169
DESKTOP-SQL2K17 INSTANCE2    No          14.0.1000.169

Unfortunately, the data is not making it to the client side for some reason. This is the root of this problem.

I've also read that this might be a problem in .NET 4.6, so I've targeted 4.6.1, 4.5.1 along with 4.7.2 with no change in results.

Any further suggestions outside of what has been attempted are welcome.

Solution

  • Well, it turns out this was a networking based issue with my lab environment, and hopefully this helps someone in the future. While the suggestion to use Wireshark was a good one, it didn't resolve my issue, but made me think about my setup a little further.

    My virtual lab environment involves using Oracle's VirtualBox, and to give some further background, when adding a NIC to the VM, the option that was chosen in the drop-down Attached type: was Bridged Adapter. This worked fine, the VM received an IP via DHCP on my network, I as able to ping the VM, connect to it via SSMS, RDP to it, etc.

    However, for some reason, the UDP packets were not "leaving" that VM and thus the output from the SQL Browser service were not making it back to my client, therefore the SQL enumeration operation discussed above would return empty results.

    So, my solution was to do the following.

    1. Change the attached type on the NIC from Bridged Adapter to VirtualBox Host-Only Ethernet Adapter.

    This will change the VM's IP to a something starting at 192.168.56.101 (default) and will not assign a gateway to the adapter. This means that if you wanted Internet access for the VM, it will not work.

    1. To allow the VM Internet access, you need to shutdown the VM in question, and add a second NIC in the VirtualBox settings to the VM, under Network and by checking the box Enable Network Adapter under the Adapter 2 tab. Here is where you can choose Bridged Adapter in the Attached to: drop-down so that the VM will get an IP on your network when restarted.

    Now, since the VM has a "leg" into both networks with the routing table configured by VirtualBox, the SQL Browser data is now making it back out to the client machine and the results were as expected.

    Hopefully this can be of use to someone.