In .NET, finding SQL instances in network is very simple, in fact it requires only to use an instance of SqlDataSourceEnumerator

This can be also achieved using SQLDMO (there is also an example for C++).

But it is not so clean and simple if you want to get list of instances in C++ without additional libraries, but only with those, that are already in system.

I’ve googled around and it seems there are two most common solutions.
One is to use ODBC SQLBrowseConnect another solution, quite popular, is to use NetServerEnum.

But searching deeper, I found an interesting article about how tools like sqlcmd are doing this under the hood, details here.

The whole thing is based on sending broadcast message with only one “0x02″ byte, so that all SQL Browsers will answer with information about version, instance name, server name etc…

So, based on this article I wrote a simple application that using sockets sends broadcast and recieves messages from SQL Browsers.

In short, respond message always starts with 0x05 byte and two following bytes are the data length. I’ve used this information to validate message.

In result, program should print received messages as below:

Received message:
 05 4c 00 53 65 72 76 65 72 4e 61 6d 65 3b 4d 56
 4d 3b 49 6e 73 74 61 6e 63 65 4e 61 6d 65 3b 53
 51 4c 45 58 50 52 45 53 53 3b 49 73 43 6c 75 73
 74 65 72 65 64 3b 4e 6f 3b 56 65 72 73 69 6f 6e
 3b 31 30 2e 35 30 2e 31 36 30 30 2e 31 3b 3b

Encoded message:
 ServerName   : MVM
 InstanceName : SQLEXPRESS
 IsClustered  : No
 Version      : 10.50.1600.1

The program itself:

#include "stdafx.h"
#include 
#include 

// Using winsock library 2.2
#include 
#pragma comment(lib, "Ws2_32.lib")

void sendBroadcast(SOCKET sock, int port);
void reciveBroadcastRespond(SOCKET sock, int port);

int main(int argc, char* argv[])
{
	WSADATA wsadata;
    if (WSAStartup(0x0202, &wsadata)) {
		std::cerr << "WSAStartup returned with error code: "
				  << WSAGetLastError();
        return 1;
	}

    if (wsadata.wVersion != 0x0202)
    {
        WSACleanup();
		std::cerr << "Incorrect version of Winsock API";
        return 1;
    }

	SOCKET sock;
    int broadcast = 1;
	int recvTimeout = 3000;
	int port = 1434;
	
    if ((sock = socket(AF_INET, SOCK_DGRAM, IPPROTO_UDP)) == INVALID_SOCKET)
	{
		std::cerr << "Creating socket failed with error code: " 
				  << WSAGetLastError();
        return 1;
	}

	// Setting socket broadcast option.
	if (setsockopt(sock, SOL_SOCKET, SO_BROADCAST, (char *)&broadcast, sizeof(broadcast)) == SOCKET_ERROR)
	{
		std::cerr << "Setting bradcast option failed with error code: " 
				  << WSAGetLastError();
        return 1;
	}

	// Setting socket timeout option to 3000, we don't want to wait forever.
	if (setsockopt(sock, SOL_SOCKET, SO_RCVTIMEO, (char *)&recvTimeout, sizeof(recvTimeout)) == SOCKET_ERROR)
	{
		std::cerr << "Setting bradcast option failed with error code: " 
				  << WSAGetLastError();
        return 1;
	}

	sendBroadcast(sock, port);
	reciveBroadcastRespond(sock, port);

	if (closesocket(sock) == SOCKET_ERROR)
	{
		std::cerr << "Closing socket failed with error code: " 
				  << WSAGetLastError();
        return 1;
	}

	return 0;
}

/**
 * Sends broadcast message with 0x02 byte.
 * @param sock Socket used for sending.
 * @param port Port on witch broadcast should be sent.
 */
void sendBroadcast(SOCKET sock, int port)
{
	struct sockaddr_in bcastAddr;    
    memset(&bcastAddr, 0, sizeof(bcastAddr));
    bcastAddr.sin_family = AF_INET;
    bcastAddr.sin_addr.s_addr = htonl(INADDR_BROADCAST);
    bcastAddr.sin_port = htons(port);

	char query = 0x02;
	if (sendto(sock, &query, sizeof(query), 0, (struct sockaddr *)&bcastAddr, sizeof(bcastAddr)) == SOCKET_ERROR)
	{
		std::cerr << "Send to failed with error code: " 
				  << WSAGetLastError();
	}
}

/**
 * Recives responds from broadcast and displays them on screen.
 * @param sock Socket used for recieving.
 * @param port Port on witch socked will receive messages.
 */
void reciveBroadcastRespond(SOCKET sock, int port) 
{
	struct sockaddr_in recvAddr;
    memset(&recvAddr, 0, sizeof(recvAddr));
    recvAddr.sin_family = AF_INET;
    recvAddr.sin_addr.s_addr = htonl(INADDR_ANY);
    recvAddr.sin_port = htons(port);

	int recvAddrLen = sizeof(sockaddr);
	int recvBytes = 0;
	char buf[512];
	memset(buf,'\0', 512);

	while ((recvBytes = recvfrom(sock, buf, sizeof(buf)-1, 0, (sockaddr*)&recvAddr, &recvAddrLen)) != SOCKET_ERROR)
	{
		// Just few lines to display message in hex.
		int lc = 1;
		std::cout << std::endl << "Received message:" << std::endl;
		for (int i=0; i < recvBytes; i++, lc++)
		{
			std::cout << " " 
					  << std::setw(2) 
					  << std::setfill('0') 
					  << std::hex << (unsigned int)buf[i];

			if (((lc%16) == 0))
				std::cout << std::endl;
		}

		std::cout << std::endl << std::endl;
		try {
			char *p = (char*)&buf;
			// Message received from SQL Browser have first byte as identifier 0x05.
			if (*p++ != 0x05)
				throw "Invalid message format!";

			// Getting size from message, it is written on second and third byte of message.
			unsigned char lb = *p++;
			unsigned char hb = *p++;
			int dataSize = lb + (hb << 8 );

			// If size of received message doesn't match, we're 
			// not going to do anything more with message.
			if (recvBytes != (dataSize+3))
				throw "Invalid message length!";

			// Printing the parsed message. This is just a string 
			// with ';' delimiter, also there is no 
			// additional delimiter for separating key from value.
			std::cout << "Encoded message:" << std::endl;
			char *pch = strtok (p,";");
			while (pch != NULL)
			{
				std::cout << " "
						  << std::left 
						  << std::setfill(' ') 
						  << std::setw(13)
						  << pch 
						  << ": ";
				pch = strtok (NULL, ";");
				std::cout << pch << std::endl;
				pch = strtok (NULL, ";");
			}
		} 
		catch (const char* ex) 
		{
			std::cerr << ex << std::endl;
		}

		// Clearing memory for next message.
		memset(buf,'\0', 512);
	}
}

Facebook comments:

Comment now!
















Trackbacks