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:

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();
        }
    }
}

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:


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
}

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.

DLL Redirection – debugging techniques for Windows Applications

Just how exactly should that API be called?

When you’re dealing with a simple Windows API call such as MessageBox, you can probably deduce what you should be doing via an experiment or two, but some API calls are non-trivial and involve a lot of set up, particularly the likes of Volume Shadow Copy, and some more obscure, undocumented APIs.

Luckily, there is a way to trivially work out how applications use these APIs, if you can identify one that does what you think it should. Firstly, dependency walker should be used to profile the calls an application is making and which DLLs are being made.

The next thing you need to know is that it is possible to redirect DLL function calls in Windows. This technique means you can effectively replace a system DLL with your own DLL, provided you redirect all of the symbols in it to the original.

So the next thing you need to do is to list the functions exported by a DLL. The SDK gives you a utility to do this – here’s an example:

C:> dumpbin /exports c:windowssystem32kernel32.dll
Microsoft (R) COFF/PE Dumper Version 11.00.50214.1
Copyright (C) Microsoft Corporation.  All rights reserved.


Dump of file c:windowsSystem32kernel32.dll

File Type: DLL

  Section contains the following exports for KERNEL32.dll

    00000000 characteristics
    4E20FCBC time date stamp Sat Jul 16 03:51:40 2011
        0.00 version
           1 ordinal base
        1390 number of functions
        1390 number of names

    ordinal hint RVA      name
    ...
        138   89 00023190 CreateFileA
        139   8A 0000EAD0 CreateFileMappingA
        140   8B 00064FB0 CreateFileMappingNumaA
        141   8C 0004C550 CreateFileMappingNumaW
        142   8D 0000F9F0 CreateFileMappingW
        143   8E 000759C0 CreateFileTransactedA
        144   8F 00075820 CreateFileTransactedW
        145   90 00011870 CreateFileW
    ...

Since I detest the very sight of VBScript, I chose to write the next part in Python. This essentially processes the raw output from dumpbin, producing a header which includes linker pragmas to set up DLL redirection.

#!/usr/bin/env python

import re
import sys

def get_functions(file_path):
    funcs = []
    exports = re.compile('s+d+s+[A-Fa-f0-9]+s+'
                         '[A-Fa-f0-9]+s+(?P&lt;functionname&gt;S+)')
    redirexps = re.compile('s+d+s+[A-Fa-f0-9]+s+(?P&lt;functionname&gt;S+)'
                           's+(forwarded to '
                           '(?P&lt;forward_name&gt;[A-Za-z0-9_.]+))')

    with open(file_path, &quot;rb&quot;) as f:
        while True:
            line = f.readline()
            if not line:
                break
            matches = exports.match(line)
            if matches:
                funcs.append(matches.group('functionname'))
            matches = redirexps.match(line)
            if matches:
                funcs.append(matches.group('functionname'))
                
    return funcs

def convert_to_pragma(funcs, orig_dll):
    return [&quot;#pragma comment(linker, &quot;/export:%s=%s.%s,@%d&quot;)&quot; %
               (func, orig_dll, func, i) for i, func in enumerate(funcs, 1)]

def write_defs(funcs, output_file):
    deffile_text  = &quot;LIBRARY    vssapi.dllnn&quot;
    deffile_text += &quot;EXPORTSn&quot;

    for func in funcs:
        &quot;&quot;&quot;
        special processing is needed for C++ redirects
        &quot;&quot;&quot;
        deffile_text += &quot;    %s=Redirected%sn&quot; % (func, func)
        
    deffile_text += &quot;nn&quot;

    with open(output_file, &quot;wb&quot;) as f:
        f.write(deffile_text)

def write_full_header(pragma_lines, output):
    header_text  = &quot;/* DLL Redirection Header */ nn&quot;
    header_text += &quot;#pragma oncenn&quot;

    for p in pragma_lines:
        header_text += &quot;%sn&quot; % (p)

    header_text += &quot;nn&quot;

    with open(output, &quot;wb&quot;) as f:
        f.write(header_text)

if __name__ == &quot;__main__&quot;:
    input_file = sys.argv[1]
    target_dll = sys.argv[2]
    output_prefix = sys.argv[3]

    output_header = &quot;%s.h&quot; % (output_prefix)
    output_defs = &quot;%s.def&quot; % (output_prefix)
    
    write_full_header(convert_to_pragma(get_functions(input_file), target_dll), 
                      output_header)
    write_defs(get_functions(input_file), output_defs)

If you include this header in a project named after the DLL you want to intercept, in our case kernel32.dll, and rename the original dll kernel32_0.dll, paste these into the application directory, depends.exe will show you this:

Kernel32.dll Redirection in Dependency Walker

In other words, all the calls to kernel32.dll (ours) are being redirected to the original – note we’ve some interesting behaviour going on, because our kernel32.dll uses the real kernel32.dll…

Now, here’s the magic. We do not have to redirect those functions – we could export symbols matching the names of those functions ourselves, and do whatever we felt like doing.

This is how you work out how an obscure API call is used – replace it with a stub which does two things:

  1. Writes the arguments somewhere useful, e.g. a log file
  2. Calls the original

It’s that simple. The possibilities extend way beyond this, of course. You can write any code you like in place of the existing function call. A brief function would look like this:

typedef HANDLE (WINAPI *pCreateFileW)(LPCTSTR,DWORD,DWORD,LPSECURITY_ATTRIBUTES,
    DWORD, DWORD, HANDLE);

extern &quot;C&quot; HANDLE WINAPI RedirectedCreateFileW (
    LPCTSTR lpFileName,
    DWORD dwDesiredAccess,
    DWORD dwShareMode,
    LPSECURITY_ATTRIBUTES lpSA,
    DWORD dwCreationDisposition,
    DWORD dwFlagsAndAttributes,
    HANDLE hTemplateFile)
{
    HMODULE hOrigLib = LoadLibrary(L&quot;kernel32_0.dll&quot;);
    pCreateFileW orig_func = (pCreateFileW) 
        GetProcAddress(hOrigLib, &quot;CreateFileW&quot;);

    // do something here

    HANDLE h = orig_func(lpFileName, dwDesiredAccess, dwShareMode, lpSA,
        dwCreationDisposition, dwFlagsAndAttributes, hTemplateFile);
    CloseHandle(hOrigLib);
    return h;
}

Of course, this method is quite intrusive, requiring copying DLLs into place. On the plus side, it works with delay-loaded DLLs via LoadLibrary, for which IAT Hooking fails. Since we’re not needing to hide with this technique, for what we want it makes perfect sense.

Supporting Windows XP SP2+ with vNext

If you’ve been following Visual Studio development at all, you’ll no doubt be very interested in the Visual Studio 11 Beta, especially as it ships with a go live license.

We certainly were; however, a significant number of our customers still use and need to back up and restore Windows XP systems, so the news that vNext Beta would not support building executables for Windows XP was a showstopper for us. Indeed, the latest news is that XP Support will not ship with the RTM, however, it may be supplied later at an unspecified date.

However, we were keen to start using the latest and greatest features of Visual Studio 11 now, especially with our soon to be unleashed beta code. It turns out were are not the only ones interested in XP support, and Ted had a solution.

So we can understand how Ted’s solution works, we’ll begin with a quick primer on linker resolution. There are two ways to link an executable; statically or dynamically. Static linking is the easier of the two to understand – you look for a symbol and you change all references to that symbol to the relative address from the base of the executable. Well – sort of, anyway. It’s good enough here.

This all happens at compile time; dynamic linking on Windows often uses an import library to symbols that look like this:

__imp_

When Windows runs your executable, the import library pulls in the right references, and the stub functions call the real functions. For more, have a read of this article.

Now, the statically linked versions of these libraries use exactly the same names to link against – except that the relevant implementation is pulled directly into the executable.

Now, the final thing to understand is order. When the linker is searching for a symbol, it uses the first version of a symbol it finds and imports that object. As such, it is possible to override what gets imported into an executable for compilation.

Now, the reason vNext does not support Windows XP is because the MFC and CRT libraries have been built to assume symbols available on Vista or greater. As such, an executable built using vNext and run on Windows XP will attempt to use functions that do not exist in say, for example, kernel32.dll. When the dynamic link helper attempts to resolve the function it won’t be able to, and the application will not start.

You can probably see where this is going. The solution then is fairly straight forward – all we need to do is define those symbols, implement the relevant functions and ensure these, and not the default libraries, get linked.

That’s what Ted’s solution, to which I’ve contributed in a small part, does. Firstly, some assembler files are used to define function calls with the correct names, as using __imp_ prefixed functions from C is not easy (or nice). We’ll use an example function, CompareStringEx. Firstly, Ted’s implementation lives in AfxCompareStringEx, so we define a prototype (forward declaration, if you like):

AfxCompareStringEx PROTO  :QWORD,:DWORD,:QWORD,:DWORD,:QWORD,:DWORD,:QWORD,:QWORD,:QWORD

32-bit versions of this must also specify a calling convention – the Windows API uses stdcall. Then in our data section, we define our reference:

.data
    __imp_CompareStringEx dq AfxCompareStringEx

All that remains after this is to export the symbol, so the linker can find it:

EXTERNDEF __imp_CompareStringEx : DWORD

Note the sizes of the fields used correspond to the sizes of the datatypes on a 64-bit platform – pointers are QWORDs on 64-bit, and dq defines a qword data item. The 32-bit versions of these examples use DWORD pointers.

The actual implementations of these functions generally works like this:

// function pointer type
typedef BOOLEAN (WINAPI *pFunctionName)(**args**);

// are we running on Vista?
if (IsVista)
{
    // have we already obtained a pointer to the function we want?
    if (!FunctionName_p)
    {
        // open a handle to kernel32.dll
        HMODULE mod = GetModuleHandle( _T(&quot;KERNEL32.DLL&quot;));
        if (mod)
        {
            // get the address of the function; cast it to our function pointer.
            FunctionName_p = (pFunctionName)
                                 GetProcAddress(mod, &quot;FunctionName&quot;);
        }
    }
    // call the function with our arguments
    return FunctionName_p(**args**);
}
else
{
    // XP implementation
}

Hopefully that’s pretty clear – if we’re using Vista onwards, all we do is call the Vista functions. However, on XP we basically have to reimplement these functions, which is done. This is what Ted has done in the various c++ files he provides.

So, looking to get XP SP2 / Server 2003 support for your application? Get over to Ted’s blog and download the XP support code he has on offer. Don’t forget – you must be using static linkage.