Integrated Kerberos Authentication (OLE DB)

This sample shows how to get mutual Kerberos authentication by using OLE DB in SQL Server Native Client. This sample works with SQL Server 2008 or later.

For more information about SPNs and Kerberos authentication, see Service Principal Name (SPN) Support in Client Connections.

Example

You must specify a server. In the .cpp file, change "MyServer" to a machine name that has an instance of SQL Server 2008 (or later).

You will also have to specify a customer-provided SPN. In the .cpp file, change "CPSPN" to a customer-provided SPN.

Make sure your INCLUDE environment variable includes the directory that contains sqlncli.h. Compile with ole32.lib oleaut32.lib.

// compile with: ole32.lib oleaut32.lib
#pragma once

#define WIN32_LEAN_AND_MEAN   // Exclude rarely-used stuff from Windows headers
#include <stdio.h>
#include <tchar.h>
#include <sqlncli.h>

#define CHECKHR(stmt) \
   do { \
      hr = (stmt); \
      if (FAILED(hr)) { \
         printf("CHECK_HR " #stmt " failed at (%hs, %d), hr=0x%08X\r\n", __FILE__, __LINE__, hr); \
         goto CleanUp; \
      } \
   } while(0)

#define CHECKVB(stmt) \
   do { \
      if ((stmt)!= VARIANT_TRUE) { \
         printf("CHECK_VB " #stmt " failed at (%hs, %d)\r\n", __FILE__, __LINE__); \
         goto CleanUp; \
      } \
   } while(0)

#define CHECKBOOL(stmt) \
   do { \
      if (!(stmt)) { \
         printf("CHECK_BOOL " #stmt " failed at (%hs, %d)\r\n", __FILE__, __LINE__); \
        goto CleanUp; \
      } \
   } while(0)

#define CHECKNULL(stmt) \
   do { \
      if ((stmt) == NULL) { \
         printf("CHECK_NULL " #stmt " failed at (%hs, %d)\r\n", __FILE__, __LINE__); \
         goto CleanUp; \
      } \
   } while(0)

#define SAFERELEASE(p) \
   do { \
      if ((p)!= NULL) { \
         p->Release(); \
         p = NULL; \
      } \
   } while(0)

#define SAFE_SYSFREESTRING(p) \
   do { \
      if ((p)!= NULL) { \
         ::SysFreeString(p); \
         p = NULL; \
      } \
   } while(0)

int _tmain(int argc, _TCHAR* argv[]) {
   HRESULT hr = S_OK;
   IDBInitialize* pInitialize = NULL;
   IDBProperties* pProperties = NULL;
   DBPROPSET PropertySet[1];
   DBPROP rgdbprop[1];
   LPCWSTR lpwszProviderString = L"Server=MyServer;"   // server with SQL Server 2008 (or later)
      L"Trusted_Connection=Yes;"
      L"ServerSPN=CP_SPN;";   // customer-provided SPN
   DBPROPID rgdbPropID[2];
   DBPROPIDSET rgdbPropIDSet[1];
   ULONG cPropertySets;
   DBPROPSET *prgPropertySets;

   CHECKHR(CoInitialize(NULL));
   CHECKHR(CoCreateInstance(CLSID_SQLNCLI11, NULL, CLSCTX_INPROC_SERVER, __uuidof(IDBProperties), reinterpret_cast<void **>(&pProperties)));

   // set provider string
   rgdbprop[0].dwPropertyID = DBPROP_INIT_PROVIDERSTRING;
   rgdbprop[0].dwOptions = DBPROPOPTIONS_REQUIRED;
   rgdbprop[0].colid = DB_NULLID;
   VariantInit(&(rgdbprop[0].vValue));
   V_VT(&(rgdbprop[0].vValue)) = VT_BSTR;
   V_BSTR(&(rgdbprop[0].vValue)) = SysAllocString(lpwszProviderString);

   // set the property to the property set
   PropertySet[0].rgProperties = &rgdbprop[0];
   PropertySet[0].cProperties = 1;
   PropertySet[0].guidPropertySet = DBPROPSET_DBINIT;

   // set properties and connect to server
   CHECKHR(pProperties->SetProperties(1, PropertySet));
   CHECKHR(pProperties->QueryInterface(__uuidof(pInitialize), (void **)&pInitialize));
   CHECKHR(pInitialize->Initialize());

   // get properties
   rgdbPropID[0] = SSPROP_INTEGRATEDAUTHENTICATIONMETHOD;
   rgdbPropID[1] = SSPROP_MUTUALLYAUTHENTICATED;
   rgdbPropIDSet[0].rgPropertyIDs = &rgdbPropID[0];
   rgdbPropIDSet[0].cPropertyIDs = 2;
   rgdbPropIDSet[0].guidPropertySet = DBPROPSET_SQLSERVERDATASOURCEINFO;

   CHECKHR(pProperties->GetProperties(1, rgdbPropIDSet, &cPropertySets, &prgPropertySets));
   wprintf(L"Authentication method: %s\r\n", (LPWSTR)V_BSTR(&(prgPropertySets[0].rgProperties[0].vValue)));
   wprintf(L"Mutually authenticated: %s\r\n", (VT_BOOL == V_VT(&(prgPropertySets[0].rgProperties[1].vValue)))?L"yes":L"no");

CleanUp:
   SAFERELEASE(pProperties);
   SAFERELEASE(pInitialize);

   VariantClear(&(rgdbprop[0].vValue));
   CoUninitialize();
}