Extended Stored Procedure: Loopback connection

Hi
How can I create a loopback connection? I havn't a DSN and want make a
connection to the SQL server in my Extended Stored Procedure. As I see I can
read the user and the password in my Extended Stored Procedure, but i havn't
the DSN. Can I make a (local) connection without a DSN?
I want to communicate with the SQL server in my extended stored procedure
via ODBC (SQLConnect, SQLExecDirect, SQLBindCol and so on).
A small code sample would be great.
Thanks
HansSQL Server ships a sample exactly showing this:
C:\Program Files\Microsoft SQL
Server\80\Tools\DevTools\Samples\ods\unz
ip_ods.exe
Contains
C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Samples\ods\xp_
odbc
/ ****************************************
*******************************
Copyright (c) 2000, Microsoft Corporation
All Rights Reserved.
****************************************
*******************************/
// This is an example of an extended procedure DLL built with Open Data
// Services. The functions within the DLL can be invoked by using the
extended
// stored procedures support in SQL Server. To register the functions
// and allow all users to use them run the ISQL script XP_ODBC.SQL.
//
// For further information on Open Data Services refer to the Microsoft Open
// Data Services Programmer's Reference.
//
// The extended procedures implemented in this DLL is:
//
// XP_GETTABLE_ODBC -- Used to show the creation of a new connection to
// SQL Server using ODBC that is bound to the initial client connection
#include <windows.h>
#include <tchar.h>
#include <string.h>
#include <sql.h>
#include <sqlext.h>
#include <odbcss.h>
#include <srv.h>
// Miscellaneous defines.
#define XP_NOERROR 0
#define XP_ERROR 1
// Extended procedure error codes.
#define SRV_MAXERROR 50000
#define GETTABLE_ERROR SRV_MAXERROR + 1
#define REMOTE_FAIL 4002
void handle_odbc_err(PSTR szODBCApi,
SQLRETURN sret,
DBINT msgnum,
SQLHANDLE herror,
SQLSMALLINT htype,
SRV_PROC* srvproc);
// It is highly recommended that all Microsoft SQL Server (7.0
// and greater) extended stored procedure DLLs implement and export
// __GetXpVersion. For more information see SQL Server
// Books Online
ULONG __GetXpVersion()
{
return ODS_VERSION;
}
// XP_GETTABLE_ODBC
// Returns the result of the SQL statement
// select * from <szTable>
//
// Parameters:
// srvproc - the handle to the client connection that
// got the SRV_CONNECT.
//
// Returns:
// XP_NOERROR
// XP_ERROR
//
// Side Effects:
// Returns messages and/or a result set to client.
RETCODE xp_gettable_odbc(srvproc)
SRV_PROC *srvproc;
{
HENV henv = SQL_NULL_HENV;
HDBC hdbc = SQL_NULL_HDBC;
HSTMT hstmt = SQL_NULL_HSTMT;
SQLRETURN sret;
RETCODE rc;
char acBindToken[256];
// ODBC column attributes.
TCHAR acColumnName[MAXNAME];
SQLINTEGER cbColData;
SQLSMALLINT eSQLType;
SQLINTEGER iNumAttr;
SQLSMALLINT cbAttr; // pointer to storage for descriptor info
PBYTE* ppData = NULL;
SQLINTEGER* pIndicators = NULL;
DBINT rows = 0L; // number of rows sent
PTSTR szDSN = _T("local"); // for integrated security to work you need to
// specify a local server in the ODBC setting
// in the Control Panel in Windows
int bImpersonated;
TCHAR acUID[MAXNAME];
TCHAR acPWD[MAXNAME];
int nParams;
DBINT paramtype;
TCHAR szTable[MAXNAME * 3]; // database.owner.table
TCHAR szExec[128 + (MAXNAME * 3)];
SQLSMALLINT nCols;
SQLSMALLINT nCol;
RETCODE rcXP = XP_ERROR; // Assume failure until shown otherwise.
// Get number of parameters.
nParams = srv_rpcparams(srvproc);
// Check number of parameters
if (nParams != 1) {
// Send error message and return
srv_sendmsg(srvproc, SRV_MSG_ERROR, GETTABLE_ERROR, SRV_INFO, (DBTINYINT)0,
NULL, 0, 0, "Error executing extended stored procedure: Invalid Parameter",
SRV_NULLTERM);
// A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the
// result set of an Extended Stored Procedure.
srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
return(XP_ERROR);
}
// If parameter is not varchar (should be a table name), send an
// error and return.
paramtype = srv_paramtype(srvproc, nParams);
if (paramtype != SRVVARCHAR) {
srv_sendmsg(srvproc, SRV_MSG_ERROR, GETTABLE_ERROR, SRV_INFO, (DBTINYINT)0,
NULL, 0, 0,
"Error executing extended stored procedure: Invalid Parameter Type",
SRV_NULLTERM);
// A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the
// result set of an Extended Stored Procedure.
srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
return(XP_ERROR);
}
// Terminate parameter string with NULL.
memcpy(szTable, srv_paramdata(srvproc, 1),
srv_paramlen(srvproc, 1));
szTable[srv_paramlen(srvproc, 1)] = '\0';
// Allocate an ODBC environment handle
sret = SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv);
if (sret != SQL_SUCCESS) {
handle_odbc_err("SQLAllocHandle:Env",
sret,
(DBINT) REMOTE_FAIL,
henv,
SQL_HANDLE_ENV,
srvproc);
return(XP_ERROR);
}
SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3,
SQL_IS_INTEGER);
// Allocate an ODBC connection handle
sret = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if (sret != SQL_SUCCESS) {
handle_odbc_err("SQLAllocHandle:Dbc",
sret,
(DBINT)REMOTE_FAIL,
henv,
SQL_HANDLE_ENV,
srvproc);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
return(XP_ERROR);
}
// Check for integrated security.
if (strcmp(srv_pfield(srvproc, SRV_LSECURE, (int *)NULL), "TRUE") == 0) 
3;
// Client has accessed using some form of integrated security
// Impersonate client and set SQL_INTEGRATED_SECURITY option
bImpersonated = srv_impersonate_client(srvproc);
// Connect to DSN using integrated security
SQLSetConnectAttr(hdbc, SQL_INTEGRATED_SECURITY,
(SQLPOINTER) SQL_IS_ON, SQL_IS_INTEGER);
_tcscpy(acUID, _T(""));
_tcscpy(acPWD, _T(""));
}
else {
// Client used standard login. Set the user name and password.
#ifdef UNICODE
MultiByteToWideChar(CP_ACP, 0, srv_pfield(srvproc, SRV_USER, NULL),
-1, acUID, MAXNAME);
MultiByteToWideChar(CP_ACP, 0, srv_pfield(srvproc, SRV_PWD, NULL),
-1, acPWD, MAXNAME);
#else
strncpy(acUID, srv_pfield(srvproc, SRV_USER, NULL),
MAXNAME);
strncpy(acPWD, srv_pfield(srvproc, SRV_PWD, NULL),
MAXNAME);
#endif
}
if (!SQL_SUCCEEDED(
sret = SQLConnect(hdbc, (SQLTCHAR*) szDSN, SQL_NTS,
(SQLTCHAR*) acUID, SQL_NTS, (SQLTCHAR*) acPWD, SQL_NTS)
)) {
handle_odbc_err("SQLConnect",
sret,
(DBINT)REMOTE_FAIL,
hdbc,
SQL_HANDLE_DBC,
srvproc);
goto SAFE_EXIT;
}
// Process data after successful connection
sret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
if (sret != SQL_SUCCESS) {
handle_odbc_err("SQLAllocHandle",
sret,
(DBINT)REMOTE_FAIL,
hdbc,
SQL_HANDLE_DBC,
srvproc);
return(XP_ERROR);
}
// Get the client session token...
rc = srv_getbindtoken(srvproc, acBindToken);
if (rc == FAIL) {
srv_sendmsg(srvproc,
SRV_MSG_ERROR,
GETTABLE_ERROR,
SRV_INFO,
(DBTINYINT) 0,
NULL,
0,
0,
"Error with srv_getbindtoken",
SRV_NULLTERM);
srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
return(XP_ERROR);
}
// ...bind it as an ODBC parameter for the stored procedure call...
_tcscpy(szExec, _T("{call sp_bindsession(?)}"));
sret = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARCHAR, 255, 0, acBindToken, 256, NULL);
if (sret != SQL_SUCCESS) {
handle_odbc_err("SQLBindParameter",
sret,
(DBINT)REMOTE_FAIL,
hstmt,
SQL_HANDLE_STMT,
srvproc);
return(XP_ERROR);
}
// ...and, using sp_bindsession, bind our session to the client's
// session so that we share transaction space.
sret = SQLExecDirect(hstmt, (SQLTCHAR*) szExec, SQL_NTS);
if (!((sret == SQL_SUCCESS) ||(sret == SQL_SUCCESS_WITH_INFO))) {
handle_odbc_err("SQLExecDirect",
sret,
(DBINT) GETTABLE_ERROR,
hstmt,
SQL_HANDLE_STMT,
srvproc);
return(XP_ERROR);
}
SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
// SELECT the result set.
_tcscpy(szExec, _T("SELECT * FROM "));
_tcscat(szExec, szTable);
sret = SQLExecDirect(hstmt, (SQLTCHAR*) szExec, SQL_NTS);
if (sret != SQL_SUCCESS) {
handle_odbc_err("SQLExecDirect",
sret,
(DBINT) GETTABLE_ERROR,
hstmt,
SQL_HANDLE_STMT,
srvproc);
return(XP_ERROR);
}
// Get the number of columns in the ODBC result set.
SQLNumResultCols(hstmt, &nCols);
ppData = (PBYTE*) malloc(nCols * sizeof(PBYTE));
pIndicators = malloc(nCols * sizeof(SQLINTEGER));
if (ppData == NULL || pIndicators == NULL)
goto SAFE_EXIT;
// Build the column description for this results set.
for (nCol = 0; nCol < nCols; nCol++) {
// Get the column name, length and data type.
SQLColAttribute(hstmt,
(SQLSMALLINT) (nCol + 1),
SQL_DESC_NAME,
(SQLTCHAR*) acColumnName, // returned column name
MAXNAME, // max length of rgbDesc buffer
&cbAttr, // number of bytes returned in rgbDesc
&iNumAttr);
SQLColAttribute(hstmt,
(SQLSMALLINT) (nCol + 1),
SQL_DESC_OCTET_LENGTH,
NULL,
0,
NULL,
&cbColData);
// Get the column's SQL Server data type, then reset the length
// of the data retrieved as required.
SQLColAttribute(hstmt,
(SQLSMALLINT) (nCol + 1),
SQL_CA_SS_COLUMN_SSTYPE,
NULL,
0,
NULL,
&eSQLType);
// Over-write the column length returned by ODBC with the correct value
// to be used by ODS
switch( eSQLType ) {
case SQLMONEYN:
case SQLMONEY:
cbColData = sizeof(DBMONEY);
break;
case SQLDATETIMN:
case SQLDATETIME:
cbColData = sizeof(DBDATETIME);
break;
case SQLNUMERIC:
case SQLDECIMAL:
cbColData = sizeof(DBNUMERIC);
break;
case SQLMONEY4:
cbColData = sizeof(DBMONEY4);
break;
case SQLDATETIM4: //smalldatetime
cbColData = sizeof(DBDATETIM4);
break;
}
// Allocate memory for row data.
if ((ppData[nCol] = (PBYTE) malloc(cbColData)) == NULL)
goto SAFE_EXIT;
memset(ppData[nCol], 0, cbColData);
// Bind column
SQLBindCol(hstmt,
(SQLSMALLINT) (nCol + 1),
SQL_C_BINARY, // No data conversion.
ppData[nCol],
cbColData,
&(pIndicators[nCol]));
// Prepare structure that will be sent via ODS back to
// the caller of the extended procedure
srv_describe(srvproc,
nCol + 1,
acColumnName,
SRV_NULLTERM,
eSQLType, // Dest data type.
(DBINT) cbColData, // Dest data length.
eSQLType, // Source data type.
(DBINT) cbColData, // Source data length.
(PBYTE) NULL);
}
// Initialize the row counter
rows = 0;
// Get each row of data from ODBC until there are no more rows
while((sret = SQLFetch(hstmt)) != SQL_NO_DATA_FOUND) {
if (!SQL_SUCCEEDED(sret)) {
handle_odbc_err("SQLFetch",
sret,
(DBINT) GETTABLE_ERROR,
hstmt,
SQL_HANDLE_STMT,
srvproc);
goto SAFE_EXIT;
}
// For each data field in the current row, fill the structure
// that will be sent back to the caller.
for (nCol = 0; nCol < nCols; nCol++) {
cbColData = (pIndicators[nCol] == SQL_NULL_DATA ?
0 : pIndicators[nCol]);
srv_setcollen(srvproc, nCol+1, (int) cbColData);
srv_setcoldata(srvproc, nCol+1, ppData[nCol]);
}
// Send the data row back to SQL Server via ODS.
if (srv_sendrow(srvproc) == SUCCEED)
rows++;
}
if (rows > 0)
srv_senddone(srvproc, SRV_DONE_MORE | SRV_DONE_COUNT, (DBUSMALLINT)0, rows);
else
srv_senddone(srvproc, SRV_DONE_MORE, (DBUSMALLINT)0, (DBINT)0);
// We got here successfully, let the client know.
rcXP = XP_NOERROR;
SAFE_EXIT:
// Free the data buffers.
if (ppData != NULL)
{
for (nCol = 0; nCol < nCols; nCol++)
free(ppData[nCol]);
free(ppData);
}
if (pIndicators != NULL)
free(pIndicators);
// Free handles.
if (hstmt != SQL_NULL_HSTMT)
SQLFreeStmt(hstmt, SQL_DROP);
if (hdbc != SQL_NULL_HDBC)
{
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
if (henv != SQL_NULL_HENV)
SQLFreeEnv(henv);
// Revert back to SQL Server's user account.
if( bImpersonated )
srv_revert_to_self(srvproc);
return (rcXP);
}
// HANDLE_ODBC_ERR
// This routine is called to send messages to clients when an ODBC
// function returns what could be considered an error (e.g., SQL_ERROR,
// SQL_INVALID_HANDLE).
//
// Parameters:
// szODBCApi - The name of the failing function.
// srODBAPI - The SQLRETURN of the failing function.
// msgnum - The ODS user message code.
// herror - The ODBC handle involved in the error.
// htype - The ODBC handle type.
// srvproc - Contains additional client information.
//
// Returns:
// none
//
void handle_odbc_err(PSTR szODBCApi,
SQLRETURN sret,
DBINT msgnum,
SQLHANDLE herror,
SQLSMALLINT htype,
SRV_PROC* srvproc)
{
SQLTCHAR szErrorMsg[SQL_MAX_MESSAGE_LENGTH + 1];
SQLSMALLINT cbErrorMsg;
SQLSMALLINT nRec = 1;
// If sret is SQL_SUCCESS, return without doing anything
if (sret == SQL_SUCCESS)
return;
while (
SQLGetDiagField(htype, herror, nRec++, SQL_DIAG_MESSAGE_TEXT,
szErrorMsg, SQL_MAX_MESSAGE_LENGTH, &cbErrorMsg)
== SQL_SUCCESS)
{
// If sret is SUCCESS_WITH_INFO, send as "message" (severity
// <= 10, we use zero), else send to client as "error"
// (severity > 10, we use 11).
srv_sendmsg(srvproc,
SRV_MSG_INFO,
msgnum,
(DBTINYINT) (sret == SQL_SUCCESS_WITH_INFO ? 0 : 11),
(DBTINYINT) 1,
NULL,
0,
0,
szErrorMsg,
SRV_NULLTERM);
}
}
"Hans Stoessel" <hstoessel.list@.pm-medici.ch> wrote in message
news:%23Vtf%23S3YGHA.3704@.TK2MSFTNGP03.phx.gbl...
> Hi
> How can I create a loopback connection? I havn't a DSN and want make a
> connection to the SQL server in my Extended Stored Procedure. As I see I
> can
> read the user and the password in my Extended Stored Procedure, but i
> havn't
> the DSN. Can I make a (local) connection without a DSN?
> I want to communicate with the SQL server in my extended stored procedure
> via ODBC (SQLConnect, SQLExecDirect, SQLBindCol and so on).
> A small code sample would be great.
> Thanks
> Hans
>

Extended Stored Procedure - ODBC Loopback Connection Problem

Hi,
I have a loopback connection using ODBC in the DLL initialization code of
the SQL Server ESP Module (SQL Server 2000). The loopback connection works
fine when the DSN is specifed with the "NT Authentication", however the same
fails when specified with the "SQL Server user authentication". I have tried
using both the SQLConnect and SQLDriverConnect calls, butu none of them
works. Also the same code works fine on SQL Server 2005. Is this a known
problem with some fix, or am I doing something wrong here'
The code is as given below,
// ESPODBCLoopback.cpp : Defines the entry point for the DLL application.
//
#include "stdafx.h"
#include <sql.h>
#include <sqlext.h>
#include <srv.h>
#define XP_NOERROR 0
#define XP_ERROR 1
#define SEND_ERROR(szMessage, pServerProc) \
{ \
srv_sendmsg(pServerProc, SRV_MSG_ERROR, 20001, SRV_INFO, 1, \
NULL, 0, (DBUSMALLINT) __LINE__, szMessage, SRV_NULLTERM); \
srv_senddone(pServerProc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0); \
}
// typedef const char* (_MakeODBCConnection)(void);
static const char* _szMessage = "ODBC Working out...";
void
_MakeODBCConnection(void)
{
char szConnOut[1024];
SQLSMALLINT nOut = 0;
const char* szDSNName = "TestOdbc";
const char* szUsername = "test";
const char* szPassword = "test";
SQLHANDLE hEnvironment = NULL;
SQLHANDLE hDBConnection = NULL;
if (SQL_ERROR == SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,
&hEnvironment)) {
_szMessage = "Failed to create the environment handle";
return;
}
SQLSetEnvAttr(hEnvironment, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3,
SQL_IS_INTEGER);
if (SQL_ERROR == SQLAllocHandle(SQL_HANDLE_DBC, hEnvironment,
&hDBConnection)) {
SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
_szMessage = "Failed to create the database connection";
return;
}
/*-- This is where it fails --*/
/* Tried both the with/Without database name */
if (SQL_ERROR == SQLDriverConnect(hDBConnection, GetWindow(,
(SQLCHAR*)" {DSN=TestOdbc;UID=test;PWD=test;DATABASE
=test;}", SQL_NTS,
(SQLCHAR*)szConnOut, sizeof(szConnOut), &nOut, SQL_DRIVER_COMPLETE)) {
SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);
SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
_szMessage = "Failed to connect to the database";
return;
}
/*
if (SQL_ERROR == SQLConnect(hDBConnection, (SQLCHAR*)szDSNName, SQL_NTS,
(SQLCHAR*)szUsername, SQL_NTS, (SQLCHAR*)szPassword, SQL_NTS)) {
SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);
SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
_szMessage = "Failed to connect to the database";
return;
}
*/
SQLFreeConnect(hDBConnection);
SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);
SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
_szMessage = "ODBC Connection cycle completed successfully";
}
BOOL APIENTRY DllMain( HANDLE hModule,
DWORD ul_reason_for_call,
LPVOID lpReserved
)
{
switch (ul_reason_for_call)
{
case DLL_PROCESS_ATTACH:
_MakeODBCConnection();
break;
case DLL_THREAD_ATTACH:
break;
case DLL_THREAD_DETACH:
break;
case DLL_PROCESS_DETACH:
break;
}
return TRUE;
}
static void
_CheckODBCConnection(void)
{
// _MakeODBCConnection pFunction = NULL;
// _szMessage = pFunction();
}
extern "C" __declspec(dllexport)
RETCODE xp_test_odbc(SRV_PROC *pServerProc)
{
//_szMessage = _MakeODBCConnection();
if (FAIL == srv_paramsetoutput(pServerProc, 1, (BYTE*)_szMessage,
(ULONG)strlen(_szMessage),FALSE)) {
return XP_ERROR;
}
return XP_NOERROR;
}
Thanks,
Anil Kumar
Arizcon Corporation ( http://www.arizcon.com )I dont know if this will make any difference, but maybe you can try adding
";trusted_connection=no"
- Neil
"Anil Saharan" <aks@.discussions.microsoft.com> wrote in message
Hi,
I have a loopback connection using ODBC in the DLL initialization code of
the SQL Server ESP Module (SQL Server 2000). The loopback connection works
fine when the DSN is specifed with the "NT Authentication", however the same
fails when specified with the "SQL Server user authentication". I have tried
using both the SQLConnect and SQLDriverConnect calls, butu none of them
works. Also the same code works fine on SQL Server 2005. Is this a known
problem with some fix, or am I doing something wrong here'
The code is as given below,
// ESPODBCLoopback.cpp : Defines the entry point for the DLL application.
//
#include "stdafx.h"
#include <sql.h>
#include <sqlext.h>
#include <srv.h>
#define XP_NOERROR 0
#define XP_ERROR 1
#define SEND_ERROR(szMessage, pServerProc) \
{ \
srv_sendmsg(pServerProc, SRV_MSG_ERROR, 20001, SRV_INFO, 1, \
NULL, 0, (DBUSMALLINT) __LINE__, szMessage, SRV_NULLTERM); \
srv_senddone(pServerProc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0); \
}
// typedef const char* (_MakeODBCConnection)(void);
static const char* _szMessage = "ODBC Working out...";
void
_MakeODBCConnection(void)
{
char szConnOut[1024];
SQLSMALLINT nOut = 0;
const char* szDSNName = "TestOdbc";
const char* szUsername = "test";
const char* szPassword = "test";
SQLHANDLE hEnvironment = NULL;
SQLHANDLE hDBConnection = NULL;
if (SQL_ERROR == SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,
&hEnvironment)) {
_szMessage = "Failed to create the environment handle";
return;
}
SQLSetEnvAttr(hEnvironment, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3,
SQL_IS_INTEGER);
if (SQL_ERROR == SQLAllocHandle(SQL_HANDLE_DBC, hEnvironment,
&hDBConnection)) {
SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
_szMessage = "Failed to create the database connection";
return;
}
/*-- This is where it fails --*/
/* Tried both the with/Without database name */
if (SQL_ERROR == SQLDriverConnect(hDBConnection, GetWindow(,
(SQLCHAR*)" {DSN=TestOdbc;UID=test;PWD=test;DATABASE
=test;}", SQL_NTS,
(SQLCHAR*)szConnOut, sizeof(szConnOut), &nOut, SQL_DRIVER_COMPLETE)) {
SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);
SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
_szMessage = "Failed to connect to the database";
return;
}
/*
if (SQL_ERROR == SQLConnect(hDBConnection, (SQLCHAR*)szDSNName, SQL_NTS,
(SQLCHAR*)szUsername, SQL_NTS, (SQLCHAR*)szPassword, SQL_NTS)) {
SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);
SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
_szMessage = "Failed to connect to the database";
return;
}
*/
SQLFreeConnect(hDBConnection);
SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);
SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
_szMessage = "ODBC Connection cycle completed successfully";
}
BOOL APIENTRY DllMain( HANDLE hModule,
DWORD ul_reason_for_call,
LPVOID lpReserved
)
{
switch (ul_reason_for_call)
{
case DLL_PROCESS_ATTACH:
_MakeODBCConnection();
break;
case DLL_THREAD_ATTACH:
break;
case DLL_THREAD_DETACH:
break;
case DLL_PROCESS_DETACH:
break;
}
return TRUE;
}
static void
_CheckODBCConnection(void)
{
// _MakeODBCConnection pFunction = NULL;
// _szMessage = pFunction();
}
extern "C" __declspec(dllexport)
RETCODE xp_test_odbc(SRV_PROC *pServerProc)
{
//_szMessage = _MakeODBCConnection();
if (FAIL == srv_paramsetoutput(pServerProc, 1, (BYTE*)_szMessage,
(ULONG)strlen(_szMessage),FALSE)) {
return XP_ERROR;
}
return XP_NOERROR;
}
Thanks,
Anil Kumar
Arizcon Corporation ( http://www.arizcon.com )|||Nope, It does not solve the problem

Extended Stored Procedure - ODBC Loopback Connection Problem

Hi,

I have a loopback connection using ODBC in the DLL initialization code
of
the SQL Server ESP Module (SQL Server 2000). The loopback connection
works
fine when the DSN is specifed with the "NT Authentication", however the
same
fails when specified with the "SQL Server user authentication". I have
tried
using both the SQLConnect and SQLDriverConnect calls, butu none of them

works. Also the same code works fine on SQL Server 2005. Is this a
known
problem with some fix, or am I doing something wrong here??

The code is as given below,
// ESPODBCLoopback.cpp : Defines the entry point for the DLL
application.
//

#include "stdafx.h"
#include <sql.h>
#include <sqlext.h>
#include <srv.h
#define XP_NOERROR 0
#define XP_ERROR 1

#define SEND_ERROR(szMessage, pServerProc) \
{ \
srv_sendmsg(pServerProc, SRV_MSG_ERROR, 20001, SRV_INFO, 1, \
NULL, 0, (DBUSMALLINT) __LINE__, szMessage, SRV_NULLTERM); \
srv_senddone(pServerProc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0); \
}

// typedef const char* (_MakeODBCConnection)(void);

static const char* _szMessage = "ODBC Working out...";

void
_MakeODBCConnection(void)
{
char szConnOut[1024];
SQLSMALLINT nOut = 0;
const char* szDSNName = "TestOdbc";
const char* szUsername = "test";
const char* szPassword = "test";
SQLHANDLE hEnvironment = NULL;
SQLHANDLE hDBConnection = NULL;

if (SQL_ERROR == SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,
&hEnvironment)) {
_szMessage = "Failed to create the environment handle";
return;
}

SQLSetEnvAttr(hEnvironment, SQL_ATTR_ODBC_VERSION,
(void*)SQL_OV_ODBC3,
SQL_IS_INTEGER);
if (SQL_ERROR == SQLAllocHandle(SQL_HANDLE_DBC, hEnvironment,
&hDBConnection)) {
SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
_szMessage = "Failed to create the database connection";
return;
}

/*------ This is where it fails ------*/
/* Tried both the with/Without database name */
if (SQL_ERROR == SQLDriverConnect(hDBConnection, GetWindow(,
(SQLCHAR*)"{DSN=TestOdbc;UID=test;PWD=test;DATABASE=test;}", SQL_NTS,
(SQLCHAR*)szConnOut, sizeof(szConnOut), &nOut, SQL_DRIVER_COMPLETE))
{
SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);
SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
_szMessage = "Failed to connect to the database";
return;
}

/*
if (SQL_ERROR == SQLConnect(hDBConnection, (SQLCHAR*)szDSNName,
SQL_NTS,
(SQLCHAR*)szUsername, SQL_NTS, (SQLCHAR*)szPassword, SQL_NTS)) {
SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);
SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
_szMessage = "Failed to connect to the database";
return;
}
*/

SQLFreeConnect(hDBConnection);
SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);
SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);
_szMessage = "ODBC Connection cycle completed successfully";
}

BOOL APIENTRY DllMain( HANDLE hModule,
DWORD ul_reason_for_call,
LPVOID lpReserved
)
{
switch (ul_reason_for_call)
{
case DLL_PROCESS_ATTACH:
_MakeODBCConnection();
break;

case DLL_THREAD_ATTACH:
break;

case DLL_THREAD_DETACH:
break;

case DLL_PROCESS_DETACH:
break;
}
return TRUE;
}

static void
_CheckODBCConnection(void)
{
//_MakeODBCConnection pFunction = NULL;
//_szMessage = pFunction();
}

extern "C" __declspec(dllexport)
RETCODE xp_test_odbc(SRV_PROC *pServerProc)
{
//_szMessage = _MakeODBCConnection();
if (FAIL == srv_paramsetoutput(pServerProc, 1, (BYTE*)_szMessage,
(ULONG)strlen(_szMessage),FALSE)) {
return XP_ERROR;
}
return XP_NOERROR;
}

Thanks,
Anil Kumar
Arizcon Corporation ( http://www.arizcon.com )Anil Kumar Saharan (aksaharan@.gmail.com) writes:
> I have a loopback connection using ODBC in the DLL initialization code
> of the SQL Server ESP Module (SQL Server 2000). The loopback connection
> works fine when the DSN is specifed with the "NT Authentication",
> however the same fails when specified with the "SQL Server user
> authentication". I have tried using both the SQLConnect and
> SQLDriverConnect calls, butu none of them works.

I don't really see why you would use SQL authentication for a loopback -
Windows authentication appears to be the best choice.

Then again, we have an SP that loops back, and in 6.5 days it used
SQL authentication, since we could not rely on Window authentication
being on.

Also, a DSN for a loopback seems to be an overkill. (Then again I have
always found DSN to be an overkill in all situations. Never understood
them.)

Anyway, there is something funny in your code:

> /*------ This is where it fails ------*/
> /* Tried both the with/Without database name */
> if (SQL_ERROR == SQLDriverConnect(hDBConnection, GetWindow(,
> (SQLCHAR*)"{DSN=TestOdbc;UID=test;PWD=test;DATABASE=test;}", SQL_NTS,
> (SQLCHAR*)szConnOut, sizeof(szConnOut), &nOut,
> SQL_DRIVER_COMPLETE))
> {

The call to GetWindow appears to be incomplete, and the above looks
like a syntax error to me. In any case, calling GetWindow in a
extended stored procedure, appears to be a really bad thing to do.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Soory for the mistake there in the code... I was trying out something
and just messed up before pasting it here... Kindly read the valid Part
to be the "SQLConnect" statement for the connection..

It works fine with Windows authentication but fails for the SQL Server
Authentication method.

Knowing that DSN is bad, ODBC is bad.. is good, Windows authentication
has its own known pitfalls, and so does SQL authentication, but that
does not solve the problem. Is there a reason for that code to fail for
SQL Server Authentication (with SQLConnect) ??

Anil
~~~~~~~~~~~~~
Work: http://www.arizcon.com/

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Anil Kumar (aksaharan@.yahoo.com) writes:
> Soory for the mistake there in the code... I was trying out something
> and just messed up before pasting it here... Kindly read the valid Part
> to be the "SQLConnect" statement for the connection..
> It works fine with Windows authentication but fails for the SQL Server
> Authentication method.
> Knowing that DSN is bad, ODBC is bad.. is good, Windows authentication
> has its own known pitfalls, and so does SQL authentication, but that
> does not solve the problem. Is there a reason for that code to fail for
> SQL Server Authentication (with SQLConnect) ??

I have to admit that I don't have much expierence of ODBC programming,
so I cannot answer questions about SQLConnect.

But two questions:
1) Can you get an error message from ODBC, telling you why the login
failed?
2) Stupid check: SQL authentication is enabled on the server?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> But two questions:
> 1) Can you get an error message from ODBC, telling you
> why the login failed?
The error is :
Failed to connect to database : [0 : HYT00 : [Microsoft][ODBC SQL Server
Driver]Timeout expired]

This is the return error for SQLConnect failure, I'm not sure whether it
is getting in some kind of deadlock or something similar due to the
loopback ..

> 2) Stupid check: SQL authentication is enabled on the
> server?
SQL Authentication is enabled, and the same DSN works fine with SQL
authentication when used from outside to connect.

Anil Kumar
~~~~~~~~~~~~
http://www.arizcon.com/

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Anil Kumar (aksaharan@.yahoo.com) writes:
>> But two questions:
>> 1) Can you get an error message from ODBC, telling you
>> why the login failed?
> The error is :
> Failed to connect to database : [0 : HYT00 : [Microsoft][ODBC SQL Server
> Driver]Timeout expired]
> This is the return error for SQLConnect failure, I'm not sure whether it
> is getting in some kind of deadlock or something similar due to the
> loopback ..

Mysterious. The most likely reason for a timeout while connecting is
that the server does not exist. But what I can see in the MDAC Books
Online, you ought to get HYT01 in that case. Unless you first started
transaction, acquired locks on the sysxlogins table, and the called your
XP, I can't see how you could block yourself. (And I don't think you
can get locks on sysxlogins easily.)

I still sort of suspect that you are connecting to the wrong server.
I don't really what is that DSN (which must be a System DSN by the way),
and if I were do to this myself, I would pass @.@.servername to the
XP. In fact, as long as you are not using a named instance, there's
no need to specify server at all.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>SQL Authentication is enabled, and the same DSN works fine with SQL
>authentication when used from outside to connect.

maybe i dont't understand what is it all about
but i would play with client network utility on the server machine
namely to check if there is an alias defined (with the same name as it
appears in DSN) on server machine ?
(and playing with protocols)