Browsing for SQL Servers

Question

I'm writing a database application that connects to SQL server. I'd like to implement a similar connection dialog box like the one in SQL Management Studio. I've already found a way to get the list of databases on a server, but I'd really like to get the list of available servers on the network so end users won't have to type in the name/IP of the server.

Answer 1

From here:

CREATE PROCEDURE dbo.ListLocalServers 
AS 
BEGIN 
    SET NOCOUNT ON 

    CREATE TABLE #servers(sname VARCHAR(255)) 

    INSERT #servers EXEC master..XP_CMDShell 'OSQL -L' 
    -- play with ISQL -L too, results differ slightly 

    DELETE #servers WHERE sname='Servers:' 

    SELECT LTRIM(sname) FROM #servers WHERE sname != 'NULL' 

    DROP TABLE #servers 
END

Alternative SQL DMO method here

Answer 2

Not sure if there is a proper way but one hack would be to try to connect to port 1433 (default mssqlserver port) to machines in your network.

Answer 3

The tool SQLPing from sqlsecurity.com The older version downloads contains source code that may be useful.

Effectively, you need to query port 1434 UDP on every IP address on your network. However, firewalls and policies will block this.

Note: 1434 is where you enumerate SQL instances on a server. If you only use default instances, then port 1433 will do. Unless it's "hidden" on port 2433...

Answer 4

Your best guess would be to populate the list in advance, if you can.

Scanning the network for SQL servers is probably not the best idea (slow, unreliable).

Answer 5

A start might be to get MS Network monitor and see what it does when you click the dropdown in MSSMS.

My guess is (with the lag when you click that dropdown) is that it polls the local network.

Having said this, in my office, it appears to poll more than the local netowork in that it gets servers that are within the corporate network but on other subnets.

Good luck

Answer 6

If you are developing your program in .Net you can use the SMO objects to do this. Use the Microsoft.SqlServer.Management.Smo.SmoApplication.EnumAvailableSqlServers method to get a list of all sql servers running in your local network.
The scanning taKes a few seconds, but its not very slow.

Answer 7

Perhaps SQLCMD /L will work for you.