Category: SQL

The objective of this article is to discover the presence of Microsoft SQL Server  across subnet. There are API’s to enumerate SQL Server instances in single subnet (Win32 API : NetServerEnum ) and Microsoft enterprise manger uses this API to populate the list of SQL server available in current subnet. The API NetServerEnum broadcast UDP packets in the network and SQL server respond the message by sending their details. Since UDP packets can’t cross subnets and hence it will only return the partial list in particular domain.

I was working on project where I have to find if on given IP or IP range , any sql server exists or not and if sql server exists I need to find the instance names. etc.

The SQL server discovery module is hosted on web server and will be accessed by Silverlight application via WCF service. The code can be used to determine if SQL server is running or not (Before trying to connect) to build more responsive applications.

The code sends the UDP packet (point to point access) directly to provide ip address on port 1434 and revived the data back from machines.

If server is there the another function connects on TCP channel on TCP IP port to extract Sql server Netlib version.

public class SqlServerInfo
{
    public string ServerName { get; private set; }
    public string IpAddress { get; private set; }
    public string InstanceName { get; private set; }
    public bool IsClustered { get; private set; }
    public string Version { get; private set; }
    public int tcpPort { get; private set; }
    public string NamedPipe { get; private set; }
    public string Rpc { get; private set; }
    public bool IsActive { get; private set; }

    static public List<SqlServerInfo> DiscoverSQLServer(string[] possibleIPs, bool requiredDeepCheck)
    {
        List<SqlServerInfo> servers = new List<SqlServerInfo>();
        foreach (string ip in possibleIPs)
        {
            servers.AddRange(SqlServerInfo.DiscoverSQLServer(ip));
        }
        return servers;
    }

    static public List<SqlServerInfo> DiscoverSQLServer(string ip)
    {
        Socket socket = new Socket(AddressFamily.InterNetwork, SocketType.Dgram, ProtocolType.Udp);
        socket.EnableBroadcast = true;
        socket.ReceiveTimeout = 1000;
        List<SqlServerInfo> servers = new List<SqlServerInfo>();
        try
        {
            byte[] msg = new byte[] { 3 };
            IPEndPoint ep = new IPEndPoint(IPAddress.Parse(ip), 1434);
            socket.SendTo(msg, ep);
            int cnt = 0;
            byte[] bytBuffer = new byte[64000];
            do
            {
                cnt = socket.Receive(bytBuffer);
                string s = System.Text.ASCIIEncoding.ASCII.GetString(bytBuffer, 3, BitConverter.ToInt16(bytBuffer, 1));
                string[] parts = s.Split(new string[] { ";;" }, StringSplitOptions.RemoveEmptyEntries);
                foreach (string s1 in parts)
                {
                    SqlServerInfo sInfo = new SqlServerInfo(s1);
                    sInfo.IpAddress = ip;
                    if (sInfo.CheckIsActive())
                    {
                        servers.Add(sInfo);
                    }

                }
                socket.SetSocketOption(SocketOptionLevel.Socket, SocketOptionName.ReceiveTimeout, 300);
            } while (cnt != 0);
        }
        catch
        {

        }
        finally
        {
            socket.Close();
        }

        return servers;
    }

    private SqlServerInfo()
    {

    }

    public  string SSNetlibVersion(string remoteIP, int port)
    {
        string str = "";
        try
        {
            TcpClient client = new TcpClient();
            client.SendTimeout = 300;
            client.ReceiveTimeout = 300;
            client.Connect(remoteIP, port);
            NetworkStream stream = client.GetStream();
            byte[] buffer = new byte[] {
                0x12, 1, 0, 0x34, 0, 0, 0, 0, 0, 0, 0x15, 0, 6, 1, 0, 0x1b,
                0, 1, 2, 0, 0x1c, 0, 12, 3, 0, 40, 0, 4, 0xff, 8, 0, 1,
                0x55, 0, 0, 0, 0x4d, 0x53, 0x53, 0x51, 0x4c, 0x53, 0x65, 0x72, 0x76, 0x65, 0x72, 0,
                4, 8, 0, 0};
            stream.Write(buffer, 0, buffer.Length);
            byte[] buffer2 = new byte[0xff];
            string str2 = string.Empty;
            int count = stream.Read(buffer2, 0, buffer2.Length);
            str2 = Encoding.ASCII.GetString(buffer2, 0, count);
            string[] strArray = new string[] { buffer2[0x1d].ToString(), ".", buffer2[30].ToString(), ".", ((buffer2[0x1f] * 0x100) + buffer2[0x20]).ToString() };
            str = string.Concat(strArray);
            if (str.Substring(0, 1) == "0")
            {
                str = "";
            }
        }
        catch
        {
        }
        return str;
    }

    private bool CheckIsActive()
    {
        string version= SSNetlibVersion(this.IpAddress, this.tcpPort);
        if (version.Length == 0)
        {
            this.IsActive = false;
            return false;
        }
        else
        {
            this.IsActive = true;
            this.Version = version;
            return true;
        }

    }

    private SqlServerInfo(string info)
    {
        string[] nvs = info.Split(';');
        for (int i = 0; i < nvs.Length; i += 2)
        {
            switch (nvs[i].ToLower())
            {
                case "servername":
                    this.ServerName = nvs[i + 1];
                    break;

                case "instancename":

                    this.InstanceName = nvs[i + 1];
                    break;

                case "isclustered":
                    this.IsClustered = (nvs[i + 1].ToLower() == "yes");   //bool.Parse(nvs[i+1]);
                    break;

                case "version":
                    this.Version = nvs[i + 1];
                    break;

                case "tcp":
                    this.tcpPort = int.Parse(nvs[i + 1]);
                    break;

                case "np":
                    this.NamedPipe = nvs[i + 1];
                    break;

                case "rpc":
                    this.Rpc = nvs[i + 1];
                    break;

            }
        }
    }
}

using the code

List<SqlServerInfo> sqlSrv = null;

sqlSrv = SqlServerInfo.DiscoverSQLServer(address);

//Or

sqlSrv = SqlServerInfo.DiscoverSQLServer(
                    new string[]{ address+ ".01",
                        address+ ".02",
                        address+ ".03",
                        address+ ".04",
                        address+ ".05",
                        address+ ".06",
                        address+ ".07",
                        address+ ".08",
                        address+ ".09",
                        address+ ".10",
                        address+ ".21",
                        address+ ".51",
                        address+ ".100"},true);