SqlDataSourceEnumerator.Instance.GetDataSources() does not locate local SQL server 2008 instance

Question

I use the following code to list all the remote and local SQL Server instances:

public static void LocateSqlInstances()
  {
     using( DataTable sqlSources = SqlDataSourceEnumerator.Instance.GetDataSources())
     {
        foreach(DataRow source in sqlSources.Rows )
        {
           string instanceName = source["InstanceName"].ToString();

           if (!string.IsNullOrEmpty(instanceName))
           {
              Console.WriteLine(" Server Name:{0}", source["ServerName"]);
              Console.WriteLine("   Instance Name:{0}", source["InstanceName"]);
              Console.WriteLine("   Version:{0}", source["Version"]);
              Console.WriteLine();
           }
        }
        Console.ReadKey();
     }
  }

running the code on my local machine. The code can find and list a SQL server express instance (version 9.0.5000) installed but failed to list the other SQL server instance (version 10.0.1600).

I've done a lot of research on the Internet and made sure that (1) the Sql Browser is running and (2) the UDP port 1434 is open.

Anybody knows why? Thanks.

Solution 1

Thanks a lot to Mitch for the great answer he puts together. However, what I've done eventually is like the following:

I have two separate methods to get local and remote server instance respectively. The local instances are retrieved from the registry. You need to search both WOW64 and WOW3264 hives to get both SQL server 2008 (64bit) and SQL server Express (32 bit)

here is the code I use:

/// <summary>
  ///  get local sql server instance names from registry, search both WOW64 and WOW3264 hives
  /// </summary>
  /// <returns>a list of local sql server instance names</returns>
  public static IList<string> GetLocalSqlServerInstanceNames()
  {
     RegistryValueDataReader registryValueDataReader = new RegistryValueDataReader();

     string[] instances64Bit = registryValueDataReader.ReadRegistryValueData(RegistryHive.Wow64,
                                                                             Registry.LocalMachine,
                                                                             @"SOFTWARE\Microsoft\Microsoft SQL Server",
                                                                             "InstalledInstances");

     string[] instances32Bit = registryValueDataReader.ReadRegistryValueData(RegistryHive.Wow6432,
                                                                             Registry.LocalMachine,
                                                                             @"SOFTWARE\Microsoft\Microsoft SQL Server",
                                                                             "InstalledInstances");

     FormatLocalSqlInstanceNames(ref instances64Bit);
     FormatLocalSqlInstanceNames(ref instances32Bit);

     IList<string> localInstanceNames = new List<string>(instances64Bit);

     localInstanceNames = localInstanceNames.Union(instances32Bit).ToList();

     return localInstanceNames;
  }

---

public enum RegistryHive
{
  Wow64,
  Wow6432
}

public class RegistryValueDataReader
{
  private static readonly int KEY_WOW64_32KEY = 0x200;
  private static readonly int KEY_WOW64_64KEY = 0x100;

  private static readonly UIntPtr HKEY_LOCAL_MACHINE = (UIntPtr)0x80000002;

  private static readonly int KEY_QUERY_VALUE = 0x1;

  [DllImport("advapi32.dll", CharSet = CharSet.Unicode, EntryPoint = "RegOpenKeyEx")]
  static extern int RegOpenKeyEx(
              UIntPtr hKey,
              string subKey,
              uint options,
              int sam,
              out IntPtr phkResult);


  [DllImport("advapi32.dll", SetLastError = true)]
  static extern int RegQueryValueEx(
              IntPtr hKey,
              string lpValueName,
              int lpReserved,
              out uint lpType,
              IntPtr lpData,
              ref uint lpcbData);

  private static int GetRegistryHiveKey(RegistryHive registryHive)
  {
     return registryHive == RegistryHive.Wow64 ? KEY_WOW64_64KEY : KEY_WOW64_32KEY;
  }

  private static UIntPtr GetRegistryKeyUIntPtr(RegistryKey registry)
  {
     if (registry == Registry.LocalMachine)
     {
        return HKEY_LOCAL_MACHINE;
     }

     return UIntPtr.Zero;
  }

  public string[] ReadRegistryValueData(RegistryHive registryHive, RegistryKey registryKey, string subKey, string valueName)
  {
     string[] instanceNames = new string[0];

     int key = GetRegistryHiveKey(registryHive);
     UIntPtr registryKeyUIntPtr = GetRegistryKeyUIntPtr(registryKey);

     IntPtr hResult;

     int res = RegOpenKeyEx(registryKeyUIntPtr, subKey, 0, KEY_QUERY_VALUE | key, out hResult);

     if (res == 0)
     {
        uint type;
        uint dataLen = 0;

        RegQueryValueEx(hResult, valueName, 0, out type, IntPtr.Zero, ref dataLen);

        byte[] databuff = new byte[dataLen];
        byte[] temp = new byte[dataLen];

        List<String> values = new List<string>();

        GCHandle handle = GCHandle.Alloc(databuff, GCHandleType.Pinned);
        try
        {
           RegQueryValueEx(hResult, valueName, 0, out type, handle.AddrOfPinnedObject(), ref dataLen);
        }
        finally
        {
           handle.Free();
        }

        int i = 0;
        int j = 0;

        while (i < databuff.Length)
        {
           if (databuff[i] == '\0')
           {
              j = 0;
              string str = Encoding.Default.GetString(temp).Trim('\0');

              if (!string.IsNullOrEmpty(str))
              {
                 values.Add(str);
              }

              temp = new byte[dataLen];
           }
           else
           {
              temp[j++] = databuff[i];
           }

           ++i;
        }

        instanceNames = new string[values.Count];
        values.CopyTo(instanceNames);
     }

     return instanceNames;
  }
}


SqlDataSourceEnumerator.Instance.GetDataSources() is used to get remote sql server instances. 

At the end, I just merge the remote instance list and local instance list to produce the final result.

Solution 2

You are skipping over servers that are not named instances. Modify your code:

public class SqlServerInstance
{
    public string ServerInstance { get; set; }
    public string Version { get; set; } 
}

public static List<SqlServerInstance> LocateSqlInstances()
{
    List<SqlServerInstance> results = new List<SqlServerInstance>();

    using (DataTable sqlSources = SqlDataSourceEnumerator.Instance.GetDataSources())
    {
        foreach (DataRow source in sqlSources.Rows)
        {
            string servername;
            string instancename = source["InstanceName"].ToString();

            if (!string.IsNullOrEmpty(instancename))
            {
                servername =  source["ServerName"].ToString() + '\\' + instancename;
            }
            else
            {
                servername = source["ServerName"].ToString();
            }

            results.Add(new SqlServerInstance (){ ServerInstance = servername, Version = source["Version"].ToString() });
        }
    }

    return results;
}

Please Note: SqlDataSourceEnumerator.Instance.GetDataSources() has drawbacks:

• Subject to firewall rules (Blocked TCP/IP 1433 and UDP 1434)
• Doesn't find SQL Servers if the SQL Browser is off
• Doesn't find SQL Servers if they are hidden
• List contents not guaranteed to be repeatable (due to timeouts). In fact, a subsequent call is quite likely to give a different list depending on the network I/O, server performance, number of servers on the network and other time-dependent constraints

Several sources say you have to make 2 calls to SqlDataSourceEnumerator.Instance.GetDataSources() ...

Refs:

• SqlDataSourceEnumerator.Instance; not returning all instances
• EnumAvailableSqlServers or SqlDataSourceEnumerator - Incorrect list of available Databases
• Enumerating SQL Servers
• Programmatically Listing SQL Servers

Solution 3

Something People need to know about the methods of GetDataSources and SqlDataSourceEnumerator. If the Instance Name is the default - InstanceName will be blank! [Why .. I do not know, why can't I specify verbose, don't know that either but the guy at MS that wrote it ... arrrgh]

ServerName: Name of the server.

InstanceName: Name of the server instance. Blank if the server is running as the default instance.

IsClustered Indicates whether the server is part of a cluster.

Version Version of the server (8.00.x for SQL Server 2000, and 9.00.x for SQL Server 2005).

FROM HERE: https://msdn.microsoft.com/en-us/library/system.data.sql.sqldatasourceenumerator.getdatasources(v=vs.110).aspx

Solution 4

var registryViewArray = new[] { RegistryView.Registry32, RegistryView.Registry64 };

foreach (var registryView in registryViewArray)
{
    using (var hklm = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, registryView))
    using (var key = hklm.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server"))
    {
        var instances = (string[]) key?.GetValue("InstalledInstances");
        if (instances != null)
        {
            foreach (var element in instances)
            {
                if (element == "MSSQLSERVER")
                    Console.WriteLine(System.Environment.MachineName);
                else
                    Console.WriteLine(System.Environment.MachineName + @"\" + element);

            }
        }
    }
}

Console.ReadKey();

View more solutions

Comments

[Zag Gol]

the visual studio doesnt recognize the functrion : FormatLocalSqlInstanceNames(ref instances64Bit); FormatLocalSqlInstanceNames(ref instances32Bit);

[yaronkl]

What does "FormatLocalSqlInstanceNames" do? Can you please post the code?