C# - Finding Database Servers in a network.

Question

What should be the C# code to find all the Database Servers connected in a network(including instance-name, port no, and IP address)?

Answer 1

Resources: http://msdn.microsoft.com/en-us/library/ms162169.aspx http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en

You would want to look into SMO. This requires each client to have the Sql Management Objects and CLR types installed. Below is a code snippet from one of my working applications that does just this.

private void OnClicked_RefreshDataSources(object sender, EventArgs e) {
        Cursor = Cursors.WaitCursor;

        DataTable dt = SmoApplication.EnumAvailableSqlServers(false);
        uxDataSource.Items.Clear();

        foreach (DataRow row in dt.Rows) {
            uxDataSource.Items.Add(row["Name"]);
        }

        if (dt.Rows.Count > 0) {
            uxDataSource.SelectedIndex = 0;
        }

        Cursor = Cursors.Default;
    }

    private void OnSelectedIndexChanged_PopulateDatabases(object sender, EventArgs e) {
        ConnectionString.DataSource = uxDataSource.SelectedItem.ToString();

        Server server = new Server(uxDataSource.SelectedItem.ToString());
        server.ConnectionContext.LoginSecure = false;
        server.ConnectionContext.Login = Program.DesktopService.AccountName;

        uxInitialCatalog.Items.Clear();

        try {
            foreach (Database db in server.Databases) {
                uxInitialCatalog.Items.Add(db.Name);
            }

            if (server.Databases.Count > 0) {
                uxInitialCatalog.SelectedIndex = 0;
            }
        }
        catch {
            MessageBox.Show("You do not have access to this server.", "Sql Connection", MessageBoxButtons.OK, 
                MessageBoxIcon.Warning);
            uxInitialCatalog.Items.Clear();
        }
    }

Answer 2

You could use the SqlDataSourceEnumerator class for this as well. Keep in mind, this is MS SQL Server specific...

var results = SqlDataSourceEnumerator.Instance.GetDataSources();

foreach (var row in results.Rows)
{
    Console.WriteLine("{0}\{1}", row["ServerName"], row["InstanceName"]);
}

See this link for additional info

Answer 3

Does it work for any RDBMS Server?

Your answer is always going to be "no" here. Every RDBMS lets you set up a custom port - MySQL could be on 1433 or 1434 or 99999. Every RDBMS responds differently from other RDBMSes and even sometimes from previous versions of itself... You'd have to check every networking port possible on every computer for every RDBMS (and every version of that RDBMS if they change response strings often) and HOPE they have them configured with standard plaintext responses instead of being encrypted or whatnot. This is basic networking - first you portscan the IP range, then you can try to appscan on the active ports you've found to see how they respond to various requests, then you use that information to say "these IPs have apps that appear to be databases on the following ports" -- you're still not going to get things like MS Instance Name without going through the proper channels (as listed with code samples above).

Your best bet for understanding where to start is probably - http://www.nmap.org

Answer 4

To combine things said in the previous answers / comments:

Contact your sysadmin, becasue this is undoable. You would need to account for every version of every (R)DBMS out there (or at least the latest ones).