Introduction
This articles shows how to retrieve a list of PCs on the local network which are running MS SQL Server, and gets information about the instances, such as server name, instance name, version, and databases.
Background
To get a list of the nearby SQL Servers is as simple as typing "osql /L" on a command prompt. But getting them programmatically, for use inside an application, is a bit trickier. The most common use for this would be to create a connection string building form in managed code, which will be the subject of my next article.
Note also, that while the subject matter is SQL Server, the first half of this article deals mostly with sockets.
To create a list of SQL Server instances, I first tried several different methods, all of which had two things in common: they took a very long time, and they were only marginally successful at finding SQL Servers instances on the network. Eventually, I had the idea of running "osql -L" with a packet sniffer running (I used Ethereal), to see how osql was doing it.
Its method was to send out a Broadcast UDP packet on port 1434, with the contents of just a byte of 0x02, to which each server would respond. Sending such a message is quite simple:
Socket socket = new Socket(AddressFamily.InterNetwork,
SocketType.Dgram, ProtocolType.Udp );
socket.SetSocketOption(SocketOptionLevel.Socket,
SocketOptionName.Broadcast, 1);
socket.SetSocketOption(SocketOptionLevel.Socket,
SocketOptionName.ReceiveTimeout, 3000);
IPEndPoint ep = new IPEndPoint(IPAddress.Broadcast, 1434);
byte[] msg = new byte[] { 0x02 };
socket.SendTo(msg, ep);
It's vitally important that you set the timeout. Without it, the Receive method will just keep waiting (forever!) until it gets something. However, since we'll be waiting for responses from an unknown number of servers, we have to decide to give up at some point. I've set that point, initially, at 3 seconds. Before I realized that the Broadcast & Timeout options were available (but hidden) on the Socket class, I had this elaborate class which derived from UdpClient and implemented asynchronous receiving.
As it turns out, with the proper options set, getting a response is as simple as you'd hope.
byte[] bytBuffer = new byte[256];
socket.Receive(bytBuffer);
The Receive method will get the response from one server, so we'll have to put that code in a loop to get all of them. Of course, once we've gotten a response, we need to interpret it. For this, I've created a simple class called SqlServerInfo, which I'll explain further in a minute.
After we've received, processed and stored the response, we just keep looping until we don't get a response within the timeout. The downside of using a timeout, is that when it is reached, the Socket throws a SocketException, which means we have to incur the overhead time to process a throw every time we call this method. This is in addition to the timeout delay itself. I was able to find a way to minimize this.
I initially set the timeout to 3 seconds. The problem here was that after each response, I'd call Receive again, and the timeout timer would start over. So the total time for the method would be: the total actual processing time + the timeout + the throw overhead. This was about 5 seconds in my tests. This seems way too long to me -- remember, this is something we are going to want to do in the Page_Load of a form. One thing I noticed was that after I send the broadcast message, there would be a short delay as the message went out, the remote servers received it and prepared their responses. However, after that delay, all the responses would come in a pack. So, I decided, after I processed the first response, to reset the timeout to 0.3 seconds. This cut the total time down to about 1-2 seconds, a much more reasonable delay. (However, if there were no servers at all on the network, it would still take 3+ seconds to realize that.)
Now, back to interpreting the response we've gotten, which is quite simple. The first three bytes are a byte of 0x05, followed by two bytes giving the length of the rest which is straight ASCII text of pairs of items, separated by semicolons:
data item 1 name ; data item 1 value ; data item 2 name ; data item 2 value;
or more specifically:
ServerName;DATA001;InstanceName;MSSQLSERVER;IsClustered;No;Version;8.00.194;tcp;1433
To make use of this, we'll have to convert from a byte array of ASCII characters to a .NET string of UNICODE characters, which the framework is nice enough to provide a method for (although it does bury in under System.Text.ASCIIEncoding.ASCII). Then, separate the parts, and move the the data values to properties where they can be accessed easier.
Of course, while the information provided in the broadcast response is useful, it doesn't provide one vitally important piece of data: the list of databases on that server. To get that, we need to use more traditional database access methods.
But, to do that, we are going to have to connect to the server, which means a connection string, which means a username & password. SqlServerInfo provides read/write properties for those as well as a boolean IntegratedSecurity option (the other properties are read-only).
Once we've established a connection, getting the list of catalogs is a method built right into the OleDbConnection object:
DataTable schemaTable =
myConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Catalogs, null);
Using the code
Everything is packaged in the class SqlServerInfo. It has one static method, Seek(), which gets the information about the SQL Server instances on the network, and returns an array of SqlServerInfo objects.
SqlServerInfo[] servers = SqlServerInfo.Seek();
Each SqlServerInfo object contains a number of properties describing that particular MS SQL Server instance. Each of these is a read-only property.
public string ServerName
public string InstanceName
public bool IsClustered
public string Version
public int TcpPort
public string NamedPipe
public IPAddress Address
InstanceName is typically "MSSQLSERVER", which is the default if it isn't given a specific name at installation. Version should be "8.0.xxx" for SQL Server 2000. And TcpPort will typically be 1433.
Note that, although Address is a property, in the current implementation it will always be null --- until I can figure a way of finding out what the server's IP address is. The Socket.Receive method doesn't say what machine is sending the data -- it is just assumed to be the machine you just transmitted to -- the naivet� of which is apparent when one sends a broadcast message. (If anyone knows how to get that information out of a Socket, please let me know.)
Next there are four Read/Write properties, which must be set to get any more information out of this class:
public string UserId
public string Password
public bool IntegratedSecurity
public int TimeOut
These establish how we are going to attempt to connect to that server. IntegratedSecurity defaults to true, and TimeOut defaults to 2 seconds, so if these work for you, you don't have to do anything more. Setting either UserId or Password sets IntegratedSecurity to false.
The final property retrieves a list of databases available on the server.
public StringCollection Catalogs
The first time it is called, it will attempt to connect to the server, so the above property needs to be set right.
Finally, there are two methods:
public bool TestConnection()
public override string ToString()
TestConnection is just as its name says --- it tests if you can connect with the given userID/password.
ToString returns either the ServerName (if the InstanceName is the default) or ServerName / InstanceName. Either way it is what you need to specify as the Data Source in a connection string. As a ToString, you can set the DataSource property of a ListBox or similar control to an array of SqlServerInfo objects, and the appropriate values will be displayed.
SqlServerInfo Data = servers[0];
Console.WriteLine(Data.ToString());
Console.WriteLine("Version:", Data.Version);
Data.IntegratedSecurity = true;
foreach(string Catalog in Data.Catalogs)
{
Console.WriteLine(" {0}", Catalog);
}
History
- 20-Nov 2005 - v 1.0 Initial release.
|
|
 |
 | Dont understand ping to instances
juliotrujilloleon | 8:30 12 Jan '10 |
|
|
Sorry, I'am spanish and dont speak englis very well, and your text in the post dont translate well
I don't understand how to search intances across local network, I supose that your app send a generic ping to port 1433 to all machines on network by using brodcast that reply the signal of ping to all computers, ad them response when have a SQL listening to port 1433
¿This explanation it's correct? I understand well the app?
Thaks lot
|
|
|
|
|
| Some nicely written code... but why all the extra work?
Drew Stegon | 9:51 18 Dec '09 |
|
|
While trying to figure this problem out for our company, I ran into your article. I converted it into vb.net and used some newer types such as List(Of ) instead of arrays. But, then in running your code, it didnt find all the instances we had on our development servers. Thought it might be the vb code vs the c# code, but your raw code didnt work either. We run both SQL 2000 and SQL 2005, and your code would not find the 2005 stuff. So I started to research MS for sql stuff and found the DataSourceEnumerator (which I sadly have to say if I would have just read other comments, it would have saved me HOURS!!... live and learn).
Much easier to allow Windows to retrieve. Although MS has a disclaimer that states this method may "not" retrieve all the servers(data sources) and catalogs, we have never run into a time where it wasn't accurate.
Dim instance As SqlDataSourceEnumerator = _
SqlDataSourceEnumerator.Instance
table = instance.GetDataSources()
Once it is in the datatable you can use the datasources stored in the table and use your GetCatalogs method (very nice) and retrieve the catalogs easily and store those in a datatable to present in a treeview datagridview or even list or combo box controls.
Private Function GetCatalogs() As StringCollection
Dim restrictions As Object()
Dim Catalogs As New StringCollection
restrictions = Nothing
Try
Dim myConnection = GetConnection()
myConnection.Open()
Dim schemaTable As DataTable = myConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Catalogs, restrictions)
myConnection.Close()
For Each dr As DataRow In schemaTable.Rows
Dim CatName As String = dr(0).ToString
Catalogs.Add(CatName)
Next
Catch ex As Exception
MsgBox(ex.Message)
End Try
Return Catalogs
End Function
Private Function GetConnection() As OleDbConnection
Dim myConnString As String
myConnString = String.Format("Provider=SQLOLEDB;Data Source={0};Integrated Security=SSPI;Connect Timeout={1}", DataSource, Timeout)
Return New OleDbConnection(myConnString)
End Function
|
|
|
|
|
| Great
dexter_wrocek | 2:51 16 Oct '09 |
|
|
|
| SqlDataSourceEnumerator
Motz | 9:34 31 Jul '09 |
|
|
Instead of using SMO since there are DLL problems if Sql server isn't installed on the machine you can use SqlDataSourceEnumerator
DataTable dt = System.Data.Sql.SqlDataSourceEnumerator.Instance.GetDataSources();
foreach (System.Data.DataRow row in dt.Rows)
{
string serverName = row["servername"] as string;
string serverInstance = row["instancename"] as string;
}
there are more things you can get like the version number as well.
Enjoy.
|
|
|
|
|
 | Does not show localhost
DEGT | 22:05 9 Jul '09 |
|
|
I have an instance of SQL Server 2005 running on my own machine (where I ran this code snippet) and it shows all other SQL servers on our network except the one on localhost. Any ideas?
http://www.PanamaSights.com/
http://www.coralys.com/
http://www.virtual-aviation.info/
|
|
|
|
|
|
|
Check if the following services are enabled and running locally:
Computer Browser
Server
Sql Browser
Greetz....
|
|
|
|
|
| SMO Short comming
csniffer | 3:33 31 May '09 |
|
|
Thx. This was just what I needed. I tried using SMO but it wouldn't see another machine on my network as the guest account is turned off. While trying to solve this I found your post which seemed better than SMO to me for discovering SQL servers.
Thx Again!
Penguin power
|
|
|
|
|
| SqlDataSourceEnumerator.Instance.GetDataSources()
Jeremy Long | 14:36 9 Mar '09 |
|
|
Is there something wrong with SqlDataSourceEnumerator.Instance.GetDataSources()? why not just use that?
|
|
|
|
|
| IP Address fix
Salvador S. Delvisco Jr. | 11:34 19 Sep '08 |
|
|
First of all, great work. This code helped me a lot.
I used your code, and then modified it as "Member 1053820" had posted to make sure I had all of the instances of SQL Server in my list. Then I worked on getting the IP Address to correctly function instead of being null.
I came up with the following fix to the Address Public Property.
public IPAddress Address
{
get
{
IPHostEntry ipEntry = Dns.GetHostEntry(oServerName);
oIP = ipEntry.AddressList[0];
return oIP;
}
}
|
|
|
|
|
| Great work James I made slight modifications for my purposes that i think might be useful for some.
Member 1053820 | 6:35 19 Aug '08 |
|
|
First I added new fields: Collation and Authentication Mode, couse, well, I needed them.
private string oCollation;
private string oAuthMode;
then I made public properties for them:
public string Collation
{
get
{
return oCollation;
}
}
public string AuthMode
{
get
{
return oAuthMode;
}
}
Then I modified TestConnection() method to use native connection provider and to get collation and authentication mode while testing the connection:
public bool TestConnection()
{
SqlConnection conn = this.GetConnection();
bool success = false;
try
{
conn.Open();
SqlCommand command = new SqlCommand("SELECT CONVERT (varchar, SERVERPROPERTY('collation'))");
command.Connection = conn;
oCollation = (string)command.ExecuteScalar();
command.CommandText = "SELECT CONVERT (int, SERVERPROPERTY('IsIntegratedSecurityOnly'))";
switch ((int)command.ExecuteScalar())
{
case 1: oAuthMode = "Windows only"; break;
case 0: oAuthMode = "Mixed mode"; break;
default: oAuthMode = "Greska"; break;
}
conn.Close();
success = true;
}
catch { }
return success;
}
and GetConnection():
private SqlConnection GetConnection()
{
string myConnString = this.IntegratedSecurity ?
String.Format("Data Source={0};Integrated Security=SSPI;Connect Timeout={1}", this, this.TimeOut)
: String.Format("Data Source={0};User Id={1};Password={2};Connect Timeout={3}",
this, this.UserId, this.Password, this.TimeOut);
return new SqlConnection(myConnString);
}
I also changed Seek method because there was this thing where test machine had two instances default and named and they were returned in same string, well it may not be the most elegant of solutions, but it works.
static public SqlServerInfo[] Seek()
{
Socket socket = new Socket(AddressFamily.InterNetwork, SocketType.Dgram, ProtocolType.Udp);
socket.EnableBroadcast = true; socket.ReceiveTimeout = 3000;
ArrayList servers = new ArrayList();
try
{
byte[] msg = new byte[] { 0x02 };
IPEndPoint ep = new IPEndPoint(IPAddress.Broadcast, 1434);
socket.SendTo(msg, ep);
int cnt = 0;
byte[] bytBuffer = new byte[1024];
do
{
cnt = socket.Receive(bytBuffer);
servers.Add(new SqlServerInfo(null, bytBuffer));
string info = System.Text.ASCIIEncoding.ASCII.GetString(bytBuffer, 3, BitConverter.ToInt16(bytBuffer, 1));
if (info.Contains(";;") & (info.Length > info.IndexOf(";;") + 12))
{
if (info.Substring(info.IndexOf(";;") + 2, 10).ToLowerInvariant().Equals("servername"))
{
info = info.Substring(info.IndexOf(";;") + 2);
servers.Add(new SqlServerInfo(null, info));
}
}
socket.SetSocketOption(SocketOptionLevel.Socket, SocketOptionName.ReceiveTimeout, 300);
} while (cnt != 0);
}
catch (SocketException socex)
{
const int WSAETIMEDOUT = 10060; const int WSAEHOSTUNREACH = 10065;
if (socex.ErrorCode == WSAETIMEDOUT || socex.ErrorCode == WSAEHOSTUNREACH)
{
}
else
{
throw;
}
}
finally
{
socket.Close();
}
SqlServerInfo[] aServers = new SqlServerInfo[servers.Count];
servers.CopyTo(aServers);
return aServers;
}
|
|
|
|
|
| Use SMO to get SQL Servers
Member 4768163 | 23:13 14 May '08 |
|
|
You can also use:
DataTable availableServers;
DataTable availableLocalServers;
availableServers = SmoApplication.EnumAvailableSqlServers(false); // local and Network SQL instances
availableLocalServers = SmoApplication.EnumAvailableSqlServers(true); // only local SQL instances
enjoy.
|
|
|
|
|
|
|
SMO is not all fun and games, it fails to find all servers, it fails to work unless you include some dlls which are version specific, in a word: It worked slow but reliable on development machine but it had problems on some test machines, and you need to create an installer, you can't just use .exe file because of aforementioned dlls.
|
|
|
|
|
| Got an Exception while comming in the loop second time
nsadhasivam | 4:05 5 May '08 |
|
|
Hi,
It identifies the local server in the first loop and when it receives in the second loop it gets an exception ofTime out,. Please help me
|
|
|
|
|
| Always times out, no servers respond
ghawkes | 10:25 23 Mar '08 |
|
|
Anyone else getting the issue where this times out every time? I am not getting any servers to respond. Thank you.
- G
static void Main(string[] args)
{
// Many thanks to CodeProject and James Curran
Socket socket = new Socket(AddressFamily.InterNetwork, SocketType.Dgram, ProtocolType.Udp);
socket.EnableBroadcast = true;
socket.ReceiveTimeout = 30000;
List<string> servers = new List<string>();
try
{
byte[] msg = new byte[] { 0x02 };
IPEndPoint ep = new IPEndPoint(IPAddress.Broadcast, 1434);
socket.SendTo(msg, ep);
int count = 0;
byte[] buffer = new byte[1024];
do
{
count = socket.Receive(buffer);
Console.WriteLine(count);
Console.WriteLine(buffer.ToString());
socket.ReceiveTimeout = 30000;
} while (count != 0);
}
catch(SocketException ex)
{
const int WSAETIMEDOUT = 10060; // Connection timed out.
const int WSAEHOSTUNREACH = 10065; // No route to host.
Console.WriteLine("ErrorCode: " + ex.ErrorCode);
// Re-throw if it's not a defined exception
if (ex.ErrorCode == WSAETIMEDOUT || ex.ErrorCode == WSAEHOSTUNREACH)
{
// DO nothing......
}
else
{
throw;
}
}
finally
{
socket.Close();
}
Console.ReadLine();
}
|
|
|
|
|
|
|
hey
me too got time out exception. But not in the first time. The loop executes once. but it goes for second time it throws an exception. Any solution please!
|
|
|
|
|
|
|
I had the same issue, I was working from home at the time but once I was back in the office it worked, it was down to the vpn software blocking the port I needed, so you might want to check that nothing is blocking that port i.e. vpn, fireware etc.
|
|
|
|
|
| Doesn't return if the network is disabled
arupk_das_2002 | 18:28 12 Nov '07 |
|
|
Hi
I am facing a problem, the code doesn't return anything if the network is disabled, though there are local instances. 
|
|
|
|
|
| Get list of all the terminals
Mathur Preeti | 20:21 9 Feb '07 |
|
|
Can you help me to get the list of all the terminals connected into the network..
I want all the hosts connected to the network.
Thank you in advance.
-Preeti
|
|
|
|
|
| How about WMI
smch | 23:15 25 Jan '07 |
|
|
Very good article.
As I understand, WMI is meant to deliver exactly this kind information.
I was just curious to know if you have experience in using WMI for such purposes, and its pros & cons?
Shrirang
|
|
|
|
|
|
|
I'm not an expert on WMI, but as I understand it, you would have to get a list of computers on the network, and then use WMI to ask each indivdually if they had an sql server running.
Truth,
James
|
|
|
|
|
| detecting named msde instances
rajeshatsrin | 21:52 22 Aug '06 |
|
|
Hi James
Great arcticle.
I have one doubt about fetching the list of running msde instances through c#.
I have 3 instances of MSDE installed on my machine say for example
MACHINE11\DBSTORAGE1
MACHINE11\DBSTORAGE2
MACHINE11\DBSTORAGE3
Now through c# using SMO or SQL DMO when I retreive the list of sql server instances, I get only the default instance. How can I get the list of named instances like above.
Can you pls help me with this.
Rajesh
|
|
|
|
|
| TCP disabled?
mcm_xyz1 | 4:51 22 Aug '06 |
|
|
The code is great.
The drawback is that it does not discover SQL Servers not having TCP protocol enabled...
Are there any other known methods for finding them? (note that sql service manager sees them)...
|
|
|
|
|
| Using Registry [modified]
Bahadir Cambel | 4:12 7 Jun '06 |
|
|
How about to use registry to look up last connected servers in Sql Server Enterprise Manager ?
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\LastConnect
Left Side of the "||" represents name section
Right Side of the "||" represents data section
There are 4 entries in the registry field ;
NAME || DATA
------ --------
1-(local) || -33423352:lpc:BADOLAPTOP
-----------------------------------------------
2-BADOLAPTOP || -33423352:lpc:BADOLAPTOP - Local MSDE database with machine name(so its servername is default)
-----------------------------------------------
3-MUHASEBE\LOKAL || -150536184:tcp:MUHASEBE,1433 - Remote DB with machine name (MUHASEBE) and "LOKAL" = SERVER NAME
------------------------------------------------
4-BADOLAPTOP\TEKNOIN || -150536184:lpc:BADOLAPTOP\TEKNOIN - Local DB with machine name (BADOLAPTOP) AND Server Name (TEKNOIN)
------------------------------------------------
So , could it take us any easy solution ?
(Frankly , I'm too lazy to implement any solution.If somebody is interested , please let me know.)
Imo , there could be some security issues regarding to read the reg
Bado.
-- modified at 9:16 Wednesday 7th June, 2006
|
|
|
|
|
| ULTIMATE! Many thanks!
XTonos | 0:33 30 May '06 |
|
|
I didnt want to use SQLDMO and couldnt use .net2.0 SqlDataSourceEnumeratorm so I was using SQLInfoEnumerator. Unfortunately it was unable to detect some specific servers (even if there were only 4 servers totally), so I was thinking about broadcasting udp manually, but too lazy to sniff with Ethereal 
Many thanks!!!
Tonos
|
|
|
|
|
| Timeout problem
Knut Morten Ovesen | 22:20 19 Feb '06 |
|
|
I always time out after 3000 ms no mater what i set as timeout. Why??
I have tried this in VS2003 and VS2005..
socket.SetSocketOption(SocketOptionLevel.Socket, SocketOptionName.Broadcast, 1);
socket.SetSocketOption(SocketOptionLevel.Socket, SocketOptionName.ReceiveTimeout, 100000);
|
|
|
|
|
|
|
Last Updated 24 Nov 2005 |
Advertise |
Privacy |
Terms of Use |
Copyright ©
CodeProject, 1999-2010
