# SQLConfigDataSource(....) with "UID=sa" & "PWD=" dont work. Why



## wango (Nov 14, 2001)

When I do not send in a user id and password, I can create 
a DSN and open a database (remote database on a remote server) and everything is fine.
(This works on all Windows 2000 nmachines that I know and some WIN NT 4.0 machines).

For those where the "SQLConnect(....)" ( in the 2nd function) fails, I checked the ODBC connection and 
and found that a the Server was the correct one but the "Login ID" was "administrator". 
Trying to proceed in the ODBC panel fails. Apparentl

To try to solve the problem, I try to include specifically a User ID and a password.
When I include "UID=sa" & "PWD=",
creating a DSN fails. can someone show mo please!! I have noticed that a lot of people have the 
same problem as I have.

//Retrieving a List of All ODBC Data Sources
bool CSQLdbImportDlg::CreateODBCDataSourceName()
{

CString dsn_str;
CString server_str;
CString description_str;
CString database_str;
CString wholeString;
CString userIDStr;
CString dsnPassWordStr;

// for diagnostics only
int iCount = 0;
WORD iError = 1;
DWORD	pfErrorCode;
char lpszErrorMsg[301];
WORD cbErrorMsgMax = 300;
RETCODE retcode;

BOOL bretcoderemovedsn; 
BOOL bretcodeadddsn;
LPTSTR szDriver	= "SQL Server";

// add datasource name if it exixts
dsn_str = "DSN=" + m_MyProgramaticDSN + "\0"; // m_MyProgramaticDSN = "MemexDataDSN"
LPTSTR lpszMyProgramaticDSN = new TCHAR[dsn_str.GetLength()+1];
_tcscpy(lpszMyProgramaticDSN, dsn_str);

server_str = "SERVER=" + m_cmbSQLservername + "\0";
LPTSTR lpszSQLservername = new TCHAR[server_str.GetLength()+1];
_tcscpy(lpszSQLservername, server_str);

LPTSTR lpszDATABASE = "DATABASE=VersaScanMeSQL\0";
LPTSTR lpszDESCRIPTION	= "DESCRIPTION=Dynamically created datasetname\0";

//// I pass in a blank CString "m_sqlserverdsnpassword"
dsnPassWordStr = "PWD=" + m_sqlserverdsnpassword + "\0";
LPTSTR lpszPassWord = new TCHAR[dsnPassWordStr.GetLength()+1];
_tcscpy(lpszPassWord, dsnPassWordStr);

// I pass in m_sqlserverdsnuserid = "sa"
userIDStr = "UID=" + m_sqlserverdsnuserid + "\0";	
LPTSTR lpszUSERID = new TCHAR[userIDStr.GetLength()+1];
_tcscpy(lpszUSERID, userIDStr);

// remove the local datasource name if it exixts
TCHAR szAttributesRemove[1028];
TCHAR* szSubStringRemove = &(szAttributesRemove[0]);

_tcscpy(szSubStringRemove, lpszMyProgramaticDSN);
szSubStringRemove = (TCHAR*) (((int) szSubStringRemove) + strlen(lpszMyProgramaticDSN)
+ sizeof(TCHAR));

//	send in the server name
_tcscpy(szSubStringRemove, lpszSQLservername);
szSubStringRemove = (TCHAR*) (((int) szSubStringRemove) + strlen(lpszSQLservername) +
sizeof(TCHAR));

*szSubStringRemove = 0; // second null to end the list

//	remove an existing DSN
bretcoderemovedsn = SQLConfigDataSource(NULL,
ODBC_REMOVE_SYS_DSN,
szDriver,
&(szAttributesRemove[0]) /*szAttributes*/);

//	if removing a dsn fails 
if (bretcoderemovedsn == 0) // || rc == SQL_SUCCESS_WITH_INFO) 
{
while (SQL_NO_DATA != (retcode = SQLInstallerError(
iError ++,
& pfErrorCode,
lpszErrorMsg,
cbErrorMsgMax,
NULL )))

iCount++;
}

// Add the local datasource name
//	_tstrlen does not exist
TCHAR szAttributes[1028];
TCHAR* szSubString = &(szAttributes[0]);

//	the DSN
_tcscpy(szSubString, lpszMyProgramaticDSN);
szSubString = (TCHAR*) (((int) szSubString) + strlen(lpszMyProgramaticDSN)
+ sizeof(TCHAR));

//	description
_tcscpy(szSubString, lpszDESCRIPTION);
szSubString = (TCHAR*) (((int) szSubString) + strlen(lpszDESCRIPTION) +
sizeof(TCHAR));

//	server name
_tcscpy(szSubString, lpszSQLservername);
szSubString = (TCHAR*) (((int) szSubString) + strlen(lpszSQLservername) +
sizeof(TCHAR));

//	database
_tcscpy(szSubString, lpszDATABASE);
szSubString = (TCHAR*) (((int) szSubString) + strlen(lpszDATABASE) +
sizeof(TCHAR));

//////////////////////////////////////////////////////////////////////	
/*
//	password
_tcscpy(szSubString, lpszPassWord);
szSubString = (TCHAR*) (((int) szSubString) + strlen(lpszPassWord) +
sizeof(TCHAR));
//	user id
_tcscpy(szSubString, lpszUSERID);
szSubString = (TCHAR*) (((int) szSubString) + strlen(lpszUSERID) +
sizeof(TCHAR));
*/
//////////////////////////////////////////////////////////////////////

*szSubString = 0; // second null to end the list

bretcodeadddsn = SQLConfigDataSource(NULL,
ODBC_ADD_SYS_DSN,
szDriver,
&(szAttributes[0]) /*szAttributes*/);

//	if adding a dsn fails 
if (bretcodeadddsn == 0) // || rc == SQL_SUCCESS_WITH_INFO) 
{
while (SQL_NO_DATA != (retcode = SQLInstallerError(
iError ++,
& pfErrorCode,
lpszErrorMsg,
cbErrorMsgMax,
NULL )))

iCount++;

// do not proceed beyond trying to dynamically create a dataset name
return false;
}

return true;
}//CreateODBCDataSourceName()

/*************************************************
Function
Odbc_DBase::Connect_ToInput
Description
This is the Odbc_DBase constructor. 
**************************************************/
SQLRETURN CODBC_Dbase::Connect_ToDbase(CString &Db_Name_In, //	"MemexDataDSN"
CString &strUserID,	//	"sa"	
CString &strPassWord)//	"" it is blank
{

CString cause_str;

// Declare The Local Memory Variables
SQLRETURN return_code = SQL_SUCCESS;

//	mmk 01.03.2002 
//	The default user id for SQl Server is "sa"
SQLCHAR DBUser[10];	// = "sa"; // *** hard coded user name

//	there is no pswd
SQLCHAR DBPswd[10];	// = _T(""); // *** hard coded password

strcpy((char *) DBUser, strUserID); 
strcpy((char *) DBPswd, strPassWord);

SQLINTEGER Terminator;
Terminator = SQL_NTS;

//	Connect to access database
//	Keep this handle open for as long as you are accessing data
if (ConnHandle != NULL)
{

// data set name
LPTSTR lpszDataSetName = new TCHAR[Db_Name_In.GetLength()+1];

_tcscpy(lpszDataSetName, Db_Name_In);

//	connect/open the database
return_code = SQLConnect(ConnHandle,
(SQLCHAR*) lpszDataSetName,
SQL_NTS,
DBUser,
SQL_NTS,
DBPswd,
SQL_NTS);

TCHAR lpError[1024];

//	if the sql connection failed
if ((return_code == SQL_ERROR) || (return_code == SQL_INVALID_HANDLE)) 
{
//	delete this before quitting due to failure to connect
delete [] lpszDataSetName;

// Following provides diagnotic for function
cause_str = 
CString("Most likely the connection failed due to a wrong server name.\n")
+ CString("The server picked must have the [VersascanMeSQL] database in it.\n\n");

SQLErrors(SQL_HANDLE_DBC, ConnHandle, lpError, cause_str);
return (return_code);
}

delete [] lpszDataSetName;
}

// Return last ODBC return code to calling program. 
return(return_code);
}


----------

