CODEWITHSUNDEEP
sql-serverwindows-networking
Joe Chin
Joe Chin

Reputation: 452

Browsing for SQL Servers

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.

Upvotes: 2

Views: 373

Answers (7)

Sam
Sam

Reputation: 7678

Perhaps SQLCMD /L will work for you.

Upvotes: 0

Rune Grimstad
Rune Grimstad

Reputation: 36320

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.

Upvotes: 5

Greg B
Greg B

Reputation: 14888

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

Upvotes: 0

Piskvor left the building
Piskvor left the building

Reputation: 92772

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).

Upvotes: 0

gbn
gbn

Reputation: 432311

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...

Upvotes: 0

Espen
Espen

Reputation: 2158

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.

Upvotes: 0

Galwegian
Galwegian

Reputation: 42237

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

Upvotes: 1

Related Questions