By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,068 Members | 1,210 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,068 IT Pros & Developers. It's quick & easy.

Extended Stored Procedure - ODBC Loopback Connection Problem

P: n/a
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;DATABAS E=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 )

Jul 23 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Anil Kumar Saharan (ak*******@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;DATABAS E=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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

P: n/a
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!
Jul 23 '05 #3

P: n/a
Anil Kumar (ak*******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4

P: n/a
> 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!
Jul 23 '05 #5

P: n/a
Anil Kumar (ak*******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6

P: n/a
>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)

Jul 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.