none
Back to the drawing board - Listing SQL Instances. RRS feed

  • Question

  • This is getting frustrating for both me and the individuals who have been trying to help me find a solution.

    To this end I created a barebones application that just lists the instances of SQL Server.

    I have an application that contains one form with a combo box control and a DataGridView control.

    The application searches for SQL Server instances when the Form_Load event is executed.

    Code:

    Imports System.Data.Sql
    
    Public Class frmMain
        Private Sub frmMain_Load(sender As Object, e As EventArgs) Handles Me.Load
            Dim dt As Data.DataTable = Nothing, dr As Data.DataRow = Nothing
    
            Try
                'get sql server instances in to DataTable object
                dt = Sql.SqlDataSourceEnumerator.Instance.GetDataSources()
    
                'load data in to ComboBox
                For Each dr In dt.Rows
                    Me.CmbSQLInstance.Items.Add(dr.Item(0).ToString)
                Next
                'load data in to DataGridView
                Me.DGVSQLInstances.DataSource = dt
    
            Catch ex As System.Data.SqlClient.SqlException
                MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error!")
    
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error!")
    
            Finally
                'clean up ;)
                dr = Nothing
                dt = Nothing
            End Try
        End Sub
    End Class
    

    I found this code at https://www.codeproject.com/Tips/543989/How-to-enum-SQL-Server-instances-in-network

    Here is the form when the project is executed from within the IDE:

    This is as simple as it gets. So why doesn't it show the name of my PC(MRM-WINX-002) as containing an instance of SQL Server?


    MRM256

    Monday, February 25, 2019 4:26 PM

All replies

  • There are drawbacks to GetDataSources

    • Doesn't find SQL Servers if the SQL Browser is off
    • Doesn't find SQL Servers if they are hidden
    • Subject to firewall rules (Blocked TCP/IP 1433 and UDP 1434)


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, February 25, 2019 5:06 PM
    Moderator
  • Hi Karen,

    I can access SQL Server 2017 on MRM-WINX-002 using SSMS on MRM-WINX-001, so I don't think the SQL Server is hidden. By the same token I don't think the SQL Browser is disabled either.

    How do I check "Subject to firewall rules (Blocked TCP/IP 1433 and UDP 1434?" If I can use SSMS there shouldn't be any problems.

    BTW-I downloaded the project from the website I provided in this problem report. It doesn't work either.

    Thanks,


    MRM256


    • Edited by MRM256 Monday, February 25, 2019 5:20 PM Additional info
    Monday, February 25, 2019 5:17 PM
  • Hi Karen,

    I found a SQL query that shows you what ports are listening for SQL Server. 

    I ran the query and it returned -

    2019-02-25 10:04:17.190 spid17s Server is listening on [ 'any' <ipv6> 1433].
    2019-02-25 10:04:17.190 spid17s Server is listening on [ 'any' <ipv4> 1433].
    2019-02-25 10:04:17.190 spid17s Server is listening on [ 'any' <ipv6> 49809].
    2019-02-25 10:04:17.200 spid17s Server is listening on [ 'any' <ipv4> 49809].
    2019-02-25 10:04:17.200 Server Server is listening on [ ::1 <ipv6> 1434].
    2019-02-25 10:04:17.200 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].

    Is this correct? 

    Thanks,


    MRM256

    Tuesday, February 26, 2019 6:02 PM