How to discover SQL instances

If you need to discover an instance of an SQL server in code, there are three ways of doing so. Some are more reliable and consistent than others so a mixture of at least two will guarantee that you find the instance that you want to. The following methods hold true for all versions of SQL Server and Express 2005, 2008 and 2012. SQL 2000 might work but there is no guarantee as they seemed to do things differently in the olden days.

Registry

The easiest and probably most reliable way to get the SQL instances that are installed on the local machine is to simply check the registry. If you look in:

HK_LOCAL_MACHINE/Software/Microsoft/Microsoft SQL Server/

The registry value “InstalledInstances” contains a list of all instances installed on the local PC. The local default instance will be called MSSQLServer. Subsequent instances are given by name, but in order to log on to them, you need to prepend the hostname of the PC if using ODBC. For example, if your instance is called Accounts and your local PC is called SQL_SERVER_01, you would need to pass “SQL_SERVER_01/Accounts” into the ODBC string to log onto it. To log onto the local server, I recommend you simply use “(local)” as it saves any confusion that may be caused using the local hostname, “.” or “./” etc.

SQLBrowseConnect

Microsoft supply a function called SQLBrowseConnect that can be used in order to retrieve instances not only on the local PC, but also on remote PCs accessible over the local network. The function works by taking an ODBC connection string and returning a string with the instances it has found. If used according to the specification supplied by Microsoft, the string also contains clues as to information that is missing. This can be used to form user input dialogs. When missing data is retrieved, the ODBC connection string can be augmented to retrieve further information such as databases and languages available on each particular instance. However, to simply retrieve a list of instances, you just need the following connection string:

“Driver={SQL Server};"

This will return a string that must be parsed. It will return all SQL instances on the local network, so if you are only interested in those on the local machine you will have to parse for only instances that use the local host name.

In contradiction to the registry, the local default instance will be listed as the hostname of the PC and subsequent instances using a path style notation. So the “(local)” instance will be SQL_SERVER_01 and the accounts instance will be SQL_SERVER_01/Accounts.
A code excerpt of how to call SQLBrowseConnect is below:

[code language=”cpp”]
SQLHENV hSQLEnv;
SQLHDBC hSQLHdbc;
short sConnStrOut;

//Allocate the environment handle
m_iRetcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hSQLEnv);
if (m_iRetcode == SQL_SUCCESS || m_iRetcode == SQL_SUCCESS_WITH_INFO)
{
//Set the environment attribute to SQL_OV_ODBC3
m_iRetcode = SQLSetEnvAttr(hSQLEnv, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
if (m_iRetcode == SQL_SUCCESS || m_iRetcode == SQL_SUCCESS_WITH_INFO)
{
//Allocate a connection handle
m_iRetcode = SQLAllocHandle(SQL_HANDLE_DBC, hSQLEnv, &hSQLHdbc);
if (m_iRetcode == SQL_SUCCESS || m_iRetcode == SQL_SUCCESS_WITH_INFO)
{
CString szConnStrOut;
CString inputParam(pszInputParam);
//Call SQLBrowseConnect for additional information
m_iRetcode = SQLBrowseConnect(hSQLHdbc,
inputParam.GetBuffer(),
SQL_NTS,
(szConnStrOut.GetBuffer(MAX_RET_LENGTH)),
MAX_RET_LENGTH,
&sConnStrOut);
szConnStrOut.ReleaseBuffer();
}
}
}
[/code]

There are however some major disadvantages of this. Specifically, when you call this function, it will block for several seconds. In theory, you can change the timeout by calling SQLSetEnvAttr(), but this didn’t seem to work. Even if you call it from a thread, the user is probably going to get rather bored looking at a throbber each time he clicks on the refresh button to update the list of instances. We also found in testing that for some reason, this function sometimes could not find some instances on our network.

Do it yourself

The way in which SQLBrowseConnect works is quite simple. It sends out a UDP packet to the broadcast address (255.255.255.255) and port 1434 containing a payload of 0x02. SQL servers listening on this port respond in kind by supplying information about which instances are installed etc. The SQLBrowseConnect implementation chooses to wait for quite a while before returning, presumably to ensure that data is not missed. However, if you know what you are looking for i.e. you are only interested in instances on the local machine, you don’t have to hang around so long for the data to return. I created a simple app (below) that sends the 0x02 data and waits on a select for the response which is generally immediate. Below is a code excerpt that will hopefully help:

[code language=”cpp”]

SOCKET udpSocket;
struct sockaddr_in serverAddress;

if ((udpSocket = socket(AF_INET, SOCK_DGRAM, IPPROTO_UDP)) < 0)
return;

// set up the address
serverAddress.sin_family = AF_INET;
serverAddress.sin_addr.s_addr = htonl(INADDR_LOOPBACK);
serverAddress.sin_port = htons(1434);

// the payload
char payload = 0x02;

// config the port for broadcast
BOOL broadcast = TRUE;
setsockopt(udpSocket, SOL_SOCKET, SO_BROADCAST, reinterpret_cast(&broadcast), sizeof(BOOL));

// receive address info
sockaddr_in RecvAddr;
RecvAddr.sin_family = AF_INET;
RecvAddr.sin_addr.s_addr = htonl(INADDR_ANY);

sockaddr_in SenderAddr;
int SenderAddrSize = sizeof (SenderAddr);

// bind the socket to the receive address info
bind(udpSocket, (SOCKADDR *) & RecvAddr, sizeof (RecvAddr));

// send packet
sendto(udpSocket, &payload, 1, 0, (struct sockaddr *) &serverAddress, sizeof(serverAddress))

// set up a select so that if we don’t get a timely response we just bomb out.
fd_set fds ;
struct timeval tv ;

// Set up the file descriptor set.
FD_ZERO(&fds) ;
FD_SET(udpSocket, &fds) ;

// Set up the struct timeval for the timeout.
tv.tv_sec = 5 ;
tv.tv_usec = 0 ;

// Wait until timeout or data received.
int n = select ( (int)udpSocket, &fds, NULL, NULL, &tv ) ;
if ( n == 0) // timeout
return;

else if( n == -1 ) // error
return;

// receive buffer
char RecvBuf[4048];
int BufLen = 4048;
memset(RecvBuf, 0, 4048);

iResult = recvfrom(udpSocket, RecvBuf, 4048, 0, (SOCKADDR *) & SenderAddr, &SenderAddrSize);

if (iResult == SOCKET_ERROR)
return;
}

// we have received some data. However we need to parse it to get the info we require
if (iResult > 0)
{
// parse RecvBuf here
}
[/code]

Beware

You might think that the best of all of these methods is the DIY approach. I did quite a bit of trawling and fiddling with Wireshark to get to this stage and when I had the implementation, I was pretty smug to say the least because the instance that remained hidden to SQLBrowseConnect suddenly revealed itself. We still haven’t discovered why this is, and can only put it down to pixie dust. However, I did find out that there was a massive security problem with port 1434. Perhaps this has been fixed now, but jumpy sys-admins may switch this port off meaning that any IP style discovery mechanisms may be denied by this hosts firewall. However, on testing, I have found that the registry entry is very reliable and it is possible to install different versions of SQL on the same machine without any conflicts to this registry entry.