Native Client connection to MSSQL 2000

For MARS support in SQL Server 2005, I'm changing our OLE DB code to use Native Client.

My understanding is that Native Client should support SQL Server 2000 as well.

When I point my DBPROP_INIT_DATASOURCE to a 2005 instance, everything works great. But when I point to a 2000 instance, the IDBInitialize->Initialize() call is crashing with a bounds violation.

In my test case, the only change I made was in my coCreateInstance from this:

::CoCreateInstance(DBIO_CLSID_SQLOLEDB, NULL, CLSCTX_INPROC_SERVER, IID_IDBInitialize, (void**)&m_handles.pIDBInitialize);

to this:

::CoCreateInstance(CLSID_SQLNCLI, NULL, CLSCTX_INPROC_SERVER,IID_IDBInitialize, (void**)&m_handles.pIDBInitialize);

which works against my 2005 instance, but causes the later Initialize() call to crash when run against 2000.

Is there an extra step needed to make Native Client compatable with SQL Server 2000

Answer this question

Native Client connection to MSSQL 2000

Hari_ken
Well, in terms of the InitProperties missing subscripts, I think my copy/paste converted my DBPROP InitProperties [ 6 ] to a picture of a devil, and my [ i ] subscripts got changed into lightbulbs. Maybe because I forgot to check the checkbox that says "This post contains a code sample" (assuming that disables emoticons).

So, I think I've got items (1) and (2) on Steve's list correct.

So, it comes down to my BSTR usage for SSPI and for the database name. I tried all combinations of declaring the BSTR, and still get the same issue running with Native Client against SQL Server 2000...

Thanks for all your suggestions on this so far!
valBG

I noticed a few issues with your code that may have caused your code to AV. For example, I think you intended the the variable InitProperties to be an array so it should have been declared as

DBPROP InitProperties[5]; // SA_DBPROPSET_DBINIT

and not

DBPROP InitProperties; // SA_DBPROPSET_DBINIT

Also, you might want to change the code for setting the integrated aut value SSPI to something like

BSTR bsSSPI = L"SSPI";
InitProperties[nProps].vValue.bstrVal = bsSSPI;

With these changes, I compiled and ran the code with SQLOLEDB and SQLNCLI against a SQL Server 2000 server and connected successfully in both cases.

Hope this helps.

Uwa Agbonile [MSFT]
Andreas_
It took me some time to get back to this issue, sorry. Here is a program that exhibits the problem. This program runs using standard MDAC by default, and initializes fine. If you set the NATIVE_CLIENT environment variable, it uses Native Client, and fails when run against a SQL Server 2000 instance. Both ways work fine running against 2005. Thanks for any info. The errors I get using Native Client against MSSQL 2000 are:

---------------------------------------------------------------------------------------------

Login timeout expired

An error has occurred while establishing a connection to the server. When connec
ting to SQL Server 2005, this failure may be caused by the fact that under the d
efault settings SQL Server does not allow remote connections.

TCP Provider: No connection could be made because the target machine actively re
fused it.

---------------------------------------------------------------------------------------------

#define _WIN32_DCOM
#include <stdlib.h> // getenv
#include <string.h> // strlen, strcat
#include <stdio.h> // printf
#include <sqloledb.h>
#define _SQLNCLI_OLEDB
#include <sqlncli.h>
#include "oledberr.h"

const GUID DBIO_CLSID_SQLOLEDB = {0xc7ff16cL,0x38e3,0x11d0,{0x97,0xab,0x0,0xc0,0x4f,0xc2,0xad,0x98}};
const DBID DBIO_DB_NULLID = {{DB_NULLGUID}, {0}, {(LPOLESTR)0}};
const GUID DBIO_DBPROPSET_DBINIT = {0xc8b522bc,0x5cf3,0x11ce,{0xad,0xe5,0x00,0xaa,0x00,0x44,0x77,0x3d}};

static BSTR DBIO_SysAllocString(const char *lpMultiByteStr)
{
if(!lpMultiByteStr)
return NULL;

int cchMultiByte = strlen(lpMultiByteStr);
int cchWideChar = ::MultiByteToWideChar(CP_ACP, 0, lpMultiByteStr,
cchMultiByte, NULL, NULL);
BSTR lpWideCharStr = ::SysAllocStringLen(NULL, cchWideChar);
::MultiByteToWideChar(CP_ACP, 0, lpMultiByteStr, cchMultiByte,
lpWideCharStr, cchWideChar);

return lpWideCharStr;
}

main()
{
IDBInitialize *pIDBInitialize;

HRESULT hr = ::CoInitializeEx(NULL, COINIT_MULTITHREADED);
if(hr == RPC_E_CHANGED_MODE)
::CoInitializeEx(NULL, COINIT_APARTMENTTHREADED);

// create an instance of SQLServer OLEDB provider or NATIVE_CLIENT, depending.
BSTR bsNativeClient = DBIO_SysAllocString(getenv("NATIVE_CLIENT"));
if (bsNativeClient)
{
hr = ::CoCreateInstance(CLSID_SQLNCLI, NULL, CLSCTX_INPROC_SERVER,
IID_IDBInitialize, (void**)&pIDBInitialize);
}
else
{
hr = ::CoCreateInstance(DBIO_CLSID_SQLOLEDB, NULL, CLSCTX_INPROC_SERVER,
IID_IDBInitialize, (void**)&pIDBInitialize);
}
printf("instance created (%d)\n",hr);

// Set initialization properties
IDBProperties *pIDBProperties = NULL;
hr = pIDBInitialize->QueryInterface(IID_IDBProperties,
(void**)&pIDBProperties);

printf("qi db props(%d)\n",hr);

BSTR bsServerName = DBIO_SysAllocString(getenv("DB_SERVER"));
BSTR bsDatabase = L"master";
DBPROP InitProperties; // SA_DBPROPSET_DBINIT
// Initialize common property options
int i;
for(i = 0; i < sizeof(InitProperties)/sizeof(DBPROP); ++i)
{
::VariantInit(&InitProperties.vValue);
InitProperties.dwOptions = DBPROPOPTIONS_REQUIRED;
InitProperties.colid = DBIO_DB_NULLID;
}

int nProps = 0;
// Level of prompting that will be be done to complete
// the connection process
InitProperties[nProps].dwPropertyID = DBPROP_INIT_PROMPT;
InitProperties[nProps].vValue.vt = VT_I2;
InitProperties[nProps].vValue.iVal = DBPROMPT_NOPROMPT;
++nProps;

// Data source name
InitProperties[nProps].dwPropertyID = DBPROP_INIT_DATASOURCE;
InitProperties[nProps].vValue.vt = VT_BSTR;
InitProperties[nProps].vValue.bstrVal = bsServerName;
++nProps;

// trusted connection
InitProperties[nProps].dwPropertyID = DBPROP_AUTH_INTEGRATED;
InitProperties[nProps].vValue.vt = VT_BSTR;
InitProperties[nProps].vValue.bstrVal = NULL; // the default authentication service should be used
++nProps;

// Database (initial catalog)
InitProperties[nProps].dwPropertyID = DBPROP_INIT_CATALOG;
InitProperties[nProps].vValue.vt = VT_BSTR;
InitProperties[nProps].vValue.bstrVal = bsDatabase;
++nProps;

DBPROPSET rgInitPropSet[1];
rgInitPropSet[0].guidPropertySet = DBIO_DBPROPSET_DBINIT;
rgInitPropSet[0].cProperties = nProps;
rgInitPropSet[0].rgProperties = InitProperties;

hr = pIDBProperties->SetProperties(sizeof(rgInitPropSet)/sizeof(rgInitPropSet[0]),
rgInitPropSet);
printf("did set (%d)\n",hr);
hr = pIDBInitialize->Initialize();
if (hr)
{
IErrorInfo *pIErrorInfo = NULL;
IErrorRecords *pIErrorRecords = NULL;
::GetErrorInfo(0, &pIErrorInfo);

pIErrorInfo->QueryInterface(IID_IErrorRecords, (void**)&pIErrorRecords);
ULONG cRecords = 0;
pIErrorRecords->GetRecordCount(&cRecords);

LCID lcids[] =
{
::GetUserDefaultLCID(),
::GetSystemDefaultLCID(),
MAKELCID(MAKELANGID(LANG_ENGLISH, SUBLANG_DEFAULT), SORT_DEFAULT)
};
for(ULONG ulRecordNum = 0; ulRecordNum < cRecords; ++ulRecordNum)
{
IErrorInfo *pIErrorInfo2 = NULL;
BSTR bstrDescription = NULL;
for(int iLCID = 0; iLCID < sizeof(lcids)/sizeof(LCID); ++iLCID)
{
pIErrorRecords->GetErrorInfo(ulRecordNum,lcids[iLCID],&pIErrorInfo2);
HRESULT hr = pIErrorInfo2->GetDescription(&bstrDescription);
}
printf("%S\n\n",bstrDescription);
}
}
}
Tolga Erdogus

If you use "SSPI" for the DBPROP_AUTH_INTEGRATED value instead of NULL I think you will work OK (DBIO_SysAllocString("SSPI")). NULL, empty, and "SSPI" should be equivalent.

There seems to be an issue using NULL for this value that I will follow up on.

Steve
dangerous2
Well, actually, it seems the SSPI thing was a red-herring. There were some other errors in your code preventing it from compiling, which I had fixed. Then I experienced the failure you described. After changing to use SSPI it then connected. However, upon running outside the debugger it always connected for me even when using NULL. So I went back to examine the code more closely and found another issue. After fixing that it always connects even in the debugger.

You may want to consider looking at the following in your code:
1. DBPROP InitProperties is being referenced as an array and should be declared as one: DBPROP InitProperties[SOMESIZE].
2. Inside the for loop you need to index into InitProperties array to properly init the variant of each property and set the dwOptions, etc.
3. When setting a BSTR value you should be using an actual BSTR: BSTR bsDatabase = L"master". This is initing the BSTR from a wide char string, which is not the same thing. Need to use SysAllocString().
Hope this helps,

Steve
Ken Cooper

Here is the code I used that connected fine to SQL8 SP4. It is basically your code with minor changes. If this still does not work at least it should not AV. You have to change the server name in the code below, which is hard-coded. Let me know if this works for you. If it does not, then make absolutely sure your Windows credentials are a valid user on your SQL8 server and that you have rights to the master database.

#define _WIN32_DCOM

#include <stdlib.h> // getenv

#include <string.h> // strlen, strcat

#include <stdio.h> // printf

#include <sqloledb.h>

#define _SQLNCLI_OLEDB

#include <sqlncli.h>

#include "oledberr.h"

const GUID DBIO_CLSID_SQLOLEDB = {0xc7ff16cL,0x38e3,0x11d0,{0x97,0xab,0x0,0xc0,0x4f,0xc2,0xad,0x98}};

const DBID DBIO_DB_NULLID = {{DB_NULLGUID}, {0}, {(LPOLESTR)0}};

const GUID DBIO_DBPROPSET_DBINIT = {0xc8b522bc,0x5cf3,0x11ce,{0xad,0xe5,0x00,0xaa,0x00,0x44,0x77,0x3d}};

static BSTR DBIO_SysAllocString(const char *lpMultiByteStr)

{

if(!lpMultiByteStr)

return NULL;

int cchMultiByte = (int)strlen(lpMultiByteStr);

int cchWideChar = ::MultiByteToWideChar(CP_ACP, 0, lpMultiByteStr,

cchMultiByte, NULL, NULL);

BSTR lpWideCharStr = ::SysAllocStringLen(NULL, cchWideChar);

::MultiByteToWideChar(CP_ACP, 0, lpMultiByteStr, cchMultiByte,

lpWideCharStr, cchWideChar);

return lpWideCharStr;

}

main()

{

IDBInitialize *pIDBInitialize;

HRESULT hr = ::CoInitializeEx(NULL, COINIT_MULTITHREADED);

if(hr == RPC_E_CHANGED_MODE)

::CoInitializeEx(NULL, COINIT_APARTMENTTHREADED);

// create an instance of SQLServer OLEDB provider or NATIVE_CLIENT, depending.

BSTR bsNativeClient = DBIO_SysAllocString(getenv("NATIVE_CLIENT"));

if (true) // bsNativeClient)

{

printf("Using SQL Native Client provider.\r\n");

hr = ::CoCreateInstance(CLSID_SQLNCLI, NULL, CLSCTX_INPROC_SERVER,

IID_IDBInitialize, (void**)&pIDBInitialize);

}

else

{

printf("Using SQLOLEDB provider.\r\n");

hr = ::CoCreateInstance(DBIO_CLSID_SQLOLEDB, NULL, CLSCTX_INPROC_SERVER,

IID_IDBInitialize, (void**)&pIDBInitialize);

}

printf("instance created (%d)\n",hr);

// Set initialization properties

IDBProperties *pIDBProperties = NULL;

hr = pIDBInitialize->QueryInterface(IID_IDBProperties,

(void**)&pIDBProperties);

printf("qi db props(%d)\n",hr);

BSTR bsServerName = DBIO_SysAllocString("YourServer"); // getenv("DB_SERVER"));

printf("Connecting to %S...\r\n", bsServerName);

BSTR bsDatabase = DBIO_SysAllocString("master");

// BSTR bsDatabase = L"master";

DBPROP InitProperties[4]; // SA_DBPROPSET_DBINIT

// Initialize common property options

int i;

for(i = 0; i < sizeof(InitProperties)/sizeof(DBPROP); ++i)

{

VariantInit(&InitProperties.vValue);

InitProperties.dwOptions = DBPROPOPTIONS_REQUIRED;

InitProperties.colid = DBIO_DB_NULLID;

}

int nProps = 0;

// Level of prompting that will be be done to complete

// the connection process

InitProperties[nProps].dwPropertyID = DBPROP_INIT_PROMPT;

InitProperties[nProps].vValue.vt = VT_I2;

InitProperties[nProps].vValue.iVal = DBPROMPT_NOPROMPT;

++nProps;

// Data source name

InitProperties[nProps].dwPropertyID = DBPROP_INIT_DATASOURCE;

InitProperties[nProps].vValue.vt = VT_BSTR;

InitProperties[nProps].vValue.bstrVal = bsServerName;

++nProps;

// trusted connection

InitProperties[nProps].dwPropertyID = DBPROP_AUTH_INTEGRATED;

InitProperties[nProps].vValue.vt = VT_BSTR;

printf("Using SSPI...\r\n");

InitProperties[nProps].vValue.bstrVal = DBIO_SysAllocString("SSPI"); //NULL;

++nProps;

// Database (initial catalog)

InitProperties[nProps].dwPropertyID = DBPROP_INIT_CATALOG;

InitProperties[nProps].vValue.vt = VT_BSTR;

InitProperties[nProps].vValue.bstrVal = bsDatabase;

++nProps;

DBPROPSET rgInitPropSet[1];

rgInitPropSet[0].guidPropertySet = DBIO_DBPROPSET_DBINIT;

rgInitPropSet[0].cProperties = nProps;

rgInitPropSet[0].rgProperties = InitProperties;

hr = pIDBProperties->SetProperties(sizeof(rgInitPropSet)/sizeof(rgInitPropSet[0]),

rgInitPropSet);

printf("did set (%d)\n",hr);

hr = pIDBInitialize->Initialize();

if (hr)

{

IErrorInfo *pIErrorInfo = NULL;

IErrorRecords *pIErrorRecords = NULL;

::GetErrorInfo(0, &pIErrorInfo);

pIErrorInfo->QueryInterface(IID_IErrorRecords, (void**)&pIErrorRecords);

ULONG cRecords = 0;

pIErrorRecords->GetRecordCount(&cRecords);

LCID lcids[] =

{

::GetUserDefaultLCID(),

::GetSystemDefaultLCID(),

MAKELCID(MAKELANGID(LANG_ENGLISH, SUBLANG_DEFAULT), SORT_DEFAULT)

};

for(ULONG ulRecordNum = 0; ulRecordNum < cRecords; ++ulRecordNum)

{

IErrorInfo *pIErrorInfo2 = NULL;

BSTR bstrDescription = NULL;

for(int iLCID = 0; iLCID < sizeof(lcids)/sizeof(LCID); ++iLCID)

{

pIErrorRecords->GetErrorInfo(ulRecordNum,lcids[iLCID],&pIErrorInfo2);

HRESULT hr = pIErrorInfo2->GetDescription(&bstrDescription);

}

printf("%S\n\n",bstrDescription);

}

}

else

printf("Init succeeded.\r\n");

}
Develop3r
We have similar code that runs against both SS2005 and SS2000 and it's not clear why your code is crashing. Can you provide or send me a more complete snippet of the code that reproduces the crash
SturnaR
Thank you Steve... I'm still experiencing the same problem, however, and I'm wondering if I did something wrong. I changed that bit of code to the following:

// trusted connection
InitProperties[nProps].dwPropertyID = DBPROP_AUTH_INTEGRATED;
InitProperties[nProps].vValue.vt = VT_BSTR;
if (!bsNativeClient)
InitProperties[nProps].vValue.bstrVal = NULL; // the default authentication service should be used
else
{
BSTR bsSSPI = DBIO_SysAllocString(getenv("SSPI"));
InitProperties[nProps].vValue.bstrVal = bsSSPI; // native client doesn't like NULL
}

and I'm still getting the same errors:

-------------------------------------------------

Login timeout expired

An error has occurred while establishing a connection to the server. When connec
ting to SQL Server 2005, this failure may be caused by the fact that under the d
efault settings SQL Server does not allow remote connections.

TCP Provider: No connection could be made because the target machine actively re
fused it.
-------------------------------------------------

Did I make your suggested change incorrectly
Native Client connection to MSSQL 2000

Answer this question