Get MAC Addresses Via SQL

The Media Access Control (MAC) address of a computer's network adapter is sometimes used as a unique identifier for that computer. This article presents code that lets you query the MAC address(es) via SQL using SQL Anywhere on Windows.

The MAC address is not perfect as a unique identifier; here are the main reasons:

• the MAC address changes when you change network adapters,

• a computer can have more than one network adapter, and thus more than one MAC address (up to 16), and

• it is possible to change (hack) the MAC address.

But, as Engineers often say, for all practical purposes the MAC address works as a unique id.

The code shown here has been tested using SQL Anywhere 11 running on XP SP3 and Vista 64-bit; the 32-bit version of SQL Anywhere was used on Vista 64 simply because the code shown here hasn't been compiled for 64 bits yet.

Here's the SQL code your application needs to retrieve all the MAC addresses in use by the computer that's running SQL Anywhere:

SELECT * 
  FROM mac_address()
 ORDER BY mac_address;

Here's the output from tests on three different computers; in each case, the result set from the SELECT is shown, followed by the output from the Windows ipconfig /all command to show that the result sets match "Physical Address" values:

-------------------------------------------------------------
Machine 1 - Windows XP SP3 - one network adapter

mac_address
0x001636193239

C:\>ipconfig /all
...
Ethernet adapter Local Area Connection:
...
        Physical Address. . . . . . . . . : 00-16-36-19-32-39

-------------------------------------------------------------
Machine 2 - Windows XP SP3 - two network adapters

mac_address
0x0011d82bccd7
0x00179a3b5162

C:\>ipconfig /all
...
Ethernet adapter Local Area Connection:
...
        Physical Address. . . . . . . . . : 00-17-9A-3B-51-62
...
Ethernet adapter Local Area Connection 2:
...
        Physical Address. . . . . . . . . : 00-11-D8-2B-CC-D7

-------------------------------------------------------------
Machine 3 - Vista 64 - one network adapter

mac_address
0x001cc04d09b5

C:\>ipconfig /all
...
Ethernet adapter Local Area Connection:
...
   Physical Address. . . . . . . . . : 00-1C-C0-4D-09-B5

Here is the SQL procedure called by the SELECT above; mac_address() calls a C procedure called getmac, breaks up the VARBINARY ( 96 ) return value into 6-byte substrings and returns them as a result set:

CREATE PROCEDURE mac_address()
   RESULT ( mac_address VARBINARY ( 6 ) )
BEGIN

DECLARE @mac_addresses     VARBINARY ( 96 );
DECLARE @return_code       INTEGER;
DECLARE @diagnostic_code   INTEGER;
DECLARE @diagnostic_string VARCHAR ( 255 );
DECLARE @pos               INTEGER;

DECLARE LOCAL TEMPORARY TABLE mac_address (
   mac_address  VARBINARY ( 6 ) NOT NULL PRIMARY KEY )
   NOT TRANSACTIONAL;

CALL getmac ( 
   @mac_addresses, 
   @return_code, 
   @diagnostic_code, 
   @diagnostic_string );

IF @return_code = 0 THEN

   SET @pos = 1;
   WHILE @pos < LENGTH ( @mac_addresses ) LOOP
      INSERT mac_address VALUES ( 
         SUBSTR ( @mac_addresses, @pos, 6 ) );
      SET @pos = @pos + 6;
   END LOOP;

ELSE
   MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP,  
      ' getmac error: @return_code = ',  @return_code,
      ', @diagnostic_code = ',    @diagnostic_code,
      ', @diagnostic_string = "', @diagnostic_string,
      '"' ) TO CONSOLE DEBUG ONLY;
END IF;

SELECT mac_address
  FROM mac_address;

END;

Here is the SQL declaration of the getmac EXTERNAL procedure, pointing to the getmac entry point in the getmac.dll (IMO there's no good reason for using different names for something that's really the same, it just makes life unnecessarily complex):

CREATE PROCEDURE getmac (
   OUT   mac_addresses      VARCHAR ( 96 ),
   OUT   return_code        INTEGER,    -- OK if = 0
   OUT   diagnostic_code    INTEGER,
   OUT   diagnostic_string  VARCHAR ( 255 ) )
EXTERNAL NAME 'getmac@C:\\temp\\getmac.dll';

Here is the getmac.cpp code, followed by the getmac.def file; the code is based on "Method Three: Use GetAdaptersInfo" from Three ways to get your MAC address by Khalid Shaikh.

// Required files:
//    getmac.cpp -- provided
//    getmac.def -- provided
//    getmac.sln 
//    getmac.vcproj
// 
// To open the DLL project in Visual C++ 2005:
//    Doubleclick on getmac.vcproj
//
// To specify the location of SQL Anywhere *.h 
// include files in Visual C++ 2005:
//    Solution Explorer  
//       - select getmac  
//       - right mouse  
//       - Properties 
//       - expand Configuration Properties 
//       - select C/C++ 
//       - select General
//       - see Additional Include Directories
//    For SQL Anywhere 10: 
//       "C:\Program Files\SQL Anywhere 10\h"
//    For SQL Anywhere 11: 
//       "C:\Program Files\SQL Anywhere 11\SDK\Include"
//
// To specify which DLL functions can be named 
// in CREATE PROCEDURE ... EXTERNAL statements:
//    Edit the getmac.def file.
//
// To build the DLL:
//    Build menu item  
//       - Build getmac, or Rebuild getmac
//
// To find and copy the resulting DLL...
//    C:\...\getmac\Release\getmac.dll
//    
// Attribution for SQL Anywhere C API sample code...
// *******************************************************
// Copyright 1994-2007 iAnywhere Solutions, Inc.  
// All rights reserved.
// This sample code is provided AS IS, without warranty 
// or liability of any kind.
// You may use, reproduce, modify and distribute this 
// sample code without limitation, on the condition that 
// you retain the foregoing copyright notice and 
// disclaimer as to the original iAnywhere code.
// *******************************************************
//
// Attribution and comment header from 
// www.codeguru.com/cpp/i-n/network/networkinformation
//    /article.php/c5451
// *******************************************************
// GetMACAdapters.cpp : Defines the entry point for the 
// console application.
// Author: Khalid Shaikh [Shake@ShakeNet.com]
// Date: April 5th, 2002
// This program fetches the MAC address of the localhost 
// by fetching the information through GetAdapatersInfo.  
// It does not rely on the NETBIOS protocol and the 
// ethernet adapter need not be connect to a network.
// Supported in Windows NT/2000/XP
// Supported in Windows 95/98/Me
// Supports multiple NIC cards on a PC.
// *******************************************************

#include 
#include 
#include 
#include 
#include 

#if defined( _MSC_VER )
   #include 
#endif

#include "extfnapi.h"
#include "Shlobj.h"
#include 
#pragma comment(lib, "iphlpapi.lib")

#if !defined( _unused )
   #define _unused( i ) ( (i) = (i) )
#endif

#if !defined( TRUE )
   #define TRUE 1
#endif

#if !defined( FALSE )
   #define FALSE 0
#endif

#if defined( WIN32 )
   #define _UINT32_ENTRY   unsigned int FAR __stdcall
   #define _VOID_ENTRY     void FAR __stdcall
#else
   #define _UINT32_ENTRY   unsigned int 
   #define _VOID_ENTRY     void 
#endif

#define int64  __int64
#define uint64 unsigned __int64

#if defined( WIN32 )
int __stdcall LibMain(
    HANDLE inst,
    ULONG reasoncalled,
    LPVOID reserved )
/*********************/
{
    _unused( inst );
    _unused( reasoncalled );
    _unused( reserved );
    return( 1 );
}
#endif

__declspec (dllexport ) _UINT32_ENTRY 
   extfn_use_new_api ( void )
{
   return ( EXTFN_API_VERSION );
}

//------------------------------------------
// getmac
//------------------------------------------

__declspec(dllexport) _VOID_ENTRY getmac ( 
   an_extfn_api *api, void *arg_handle ) {

   an_extfn_value   api_mac_addresses;
   an_extfn_value   api_return_code;
   an_extfn_value   api_diagnostic_code;
   an_extfn_value   api_diagnostic_string;
   char *           mac_addresses; 
   DWORD            mac_addresses_size;
   DWORD            return_code; 
   DWORD            diagnostic_code; 
   char *           diagnostic_string;
   IP_ADAPTER_INFO  AdapterInfo [ 16 ]; // up to 16 NICs
   DWORD            dwBufLen;
   PIP_ADAPTER_INFO pAdapterInfo;

   mac_addresses = ( char * ) malloc ( 96 );
   strcpy_s ( mac_addresses, 96, "" );
   mac_addresses_size = 0;

   return_code        = 0;     
   diagnostic_code    = 0;     
   diagnostic_string  = ( char * ) malloc ( 255 );
   strcpy_s ( diagnostic_string, 255, "" );

   dwBufLen = sizeof ( AdapterInfo );

   return_code = GetAdaptersInfo (
      AdapterInfo, // [out] buffer to receive data
      &dwBufLen);  // [in]  size of receive data buffer

   if ( return_code != ERROR_SUCCESS ) {
      strcpy_s ( mac_addresses, 96, "" );
      mac_addresses_size = 0;
      diagnostic_code = 1;
      strcpy_s ( diagnostic_string, 255, 
         "GetAdaptersInfo call failed" );
   }

   if ( return_code == ERROR_SUCCESS ) {

      pAdapterInfo = AdapterInfo;

      return_code = memcpy_s ( 
         &mac_addresses [ 0 ],  
         6,  
         pAdapterInfo -> Address,  
         6 ); 

      if ( return_code == ERROR_SUCCESS ) {
         mac_addresses_size = 6;
         pAdapterInfo = pAdapterInfo -> Next; // linked list
   }
   else {
         strcpy_s ( mac_addresses, 96, "" );
         mac_addresses_size = 0;
         diagnostic_code = 2;
         strcpy_s ( diagnostic_string, 255, 
            "memcpy_s call failed" );
      }

      while ( ( return_code == ERROR_SUCCESS )
         && ( pAdapterInfo ) 
         && ( mac_addresses_size < 96 ) ) {

         return_code = memcpy_s ( 
            &mac_addresses [ mac_addresses_size ],  
            6,  
            pAdapterInfo -> Address,  
            6 ); 

         if ( return_code == ERROR_SUCCESS ) {
            mac_addresses_size = mac_addresses_size + 6;
            pAdapterInfo = pAdapterInfo -> Next; // in list
         } 
   else {
            strcpy_s ( mac_addresses, 96, "" );
            mac_addresses_size = 0;
            diagnostic_code = 3;
            strcpy_s ( diagnostic_string, 255, 
               "memcpy_s call failed" );
         }

      } // while

   } // if ( return_code == ERROR_SUCCESS )

   api_mac_addresses.type      = DT_VARCHAR;
   api_mac_addresses.data      = mac_addresses;
   api_mac_addresses.piece_len = mac_addresses_size;
   api_mac_addresses.len.total_len = mac_addresses_size;
   api -> set_value (  
      arg_handle, 1, &api_mac_addresses, 0 );

   api_return_code.type = DT_INT;
   api_return_code.data = &return_code;
   api -> set_value ( 
      arg_handle, 2, &api_return_code, FALSE );

   api_diagnostic_code.type = DT_INT;
   api_diagnostic_code.data = &diagnostic_code;
   api -> set_value ( 
      arg_handle, 3, &api_diagnostic_code, FALSE );

   api_diagnostic_string.type = DT_VARCHAR;
   api_diagnostic_string.data = diagnostic_string;
   api_diagnostic_string.piece_len  
      = ( a_sql_uint32 )( strlen ( diagnostic_string ) );
   api_diagnostic_string.len.total_len  
      = ( a_sql_uint32 )( strlen ( diagnostic_string ) );
   api -> set_value ( 
      arg_handle, 4, &api_diagnostic_string, 0 );

   free ( mac_addresses );
   free ( diagnostic_string );

} // getmac

Here is the getmac.def file:

EXPORTS extfn_use_new_api
EXPORTS getmac