Connecting to remote sql server with windows auth using db library

Discussion:

(too old to reply)

Jens_Lambrecht

2010-01-12 13:54:01 UTC

Hello,

I habe an application (developed with MS Visual Studio 2008) which connects
to a sql server 2005 or sql server 2008 using the db library (ntwdblib.dll
ver 2000.080.0194.00).
When testing this under win 7 or win 2008 server the connection via
dbopen(...) works fine with a local sql server but fails with a remote sql
server.
My code for the connectionroutine looks like this:

m_p_loginrec = dblogin();
DBSETLSECURE(m_p_loginrec);
m_p_dbproc = dbopen(m_p_loginrec, m_p_chServer);

Is there a way to get this working with db library or will I really have to
remove db library from my project an use some ODBC API?

Thanks fpr your help.

William Vaughn (MVP)

2010-01-12 21:30:39 UTC

Permalink

Ah, I'm confused. What language are you using? If it's C++, VB.NET, C# or F#
you can (and should) use ADO.NET to access any version of SQL Server. As I
wrote in my books (published in the early '90s) DBLIB is very version
dependent and requires intimate knowledge of the TDS stream. It's also
dependent on the network library (DLL) that matches the DBLIB DLL--exactly.

No, I don't think you should switch to ODBC but I do think you should switch
to ADO.NET.
--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

Post by Jens_Lambrecht
Hello,
I habe an application (developed with MS Visual Studio 2008) which connects
to a sql server 2005 or sql server 2008 using the db library (ntwdblib.dll
ver 2000.080.0194.00).
When testing this under win 7 or win 2008 server the connection via
dbopen(...) works fine with a local sql server but fails with a remote sql
server.
m_p_loginrec = dblogin();
DBSETLSECURE(m_p_loginrec);
m_p_dbproc = dbopen(m_p_loginrec, m_p_chServer);
Is there a way to get this working with db library or will I really have to
remove db library from my project an use some ODBC API?
Thanks fpr your help.

Jens_Lambrecht

2010-01-13 14:21:03 UTC

Permalink

Sorry, I forgot to mention: The language I'm using is Visual C++.

When searching the web on this I came upon a DLL named sqlncli10.dll which
seems to be a collection of ODBC API functions and it was said that it is
possible to switch a program from using db library to using this dll with not
much effort even if this program requires the use of bulk copy.

Besides this I'm looking for a solution to my problem iwth db library if
such a solution exists.

Best regards
--
Jens Lambrecht

Post by William Vaughn (MVP)
Ah, I'm confused. What language are you using? If it's C++, VB.NET, C# or F#
you can (and should) use ADO.NET to access any version of SQL Server. As I
wrote in my books (published in the early '90s) DBLIB is very version
dependent and requires intimate knowledge of the TDS stream. It's also
dependent on the network library (DLL) that matches the DBLIB DLL--exactly.
No, I don't think you should switch to ODBC but I do think you should switch
to ADO.NET.
--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

Erland Sommarskog

2010-01-13 21:31:51 UTC

Permalink

Post by Jens_Lambrecht
Sorry, I forgot to mention: The language I'm using is Visual C++.
When searching the web on this I came upon a DLL named sqlncli10.dll
which seems to be a collection of ODBC API functions and it was said
that it is possible to switch a program from using db library to using
this dll with not much effort even if this program requires the use of
bulk copy.

The Bulk Copy API is the SQL Server Native Client ODBC driver is a carbon
on DB-Library.

The other parts - submiting queries, gettings results sets etc - will
require some more work, but it will be worth the investment.

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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Erland Sommarskog

2010-01-12 22:19:14 UTC

Permalink

Post by Jens_Lambrecht
I habe an application (developed with MS Visual Studio 2008) which
connects to a sql server 2005 or sql server 2008 using the db library
(ntwdblib.dll ver 2000.080.0194.00). When testing this under win 7 or
win 2008 server the connection via dbopen(...) works fine with a local
sql server but fails with a remote sql server.

The most likely reason is that these instances are not enabled for
remote connections. Can you connect to these instance remotely in
other ways?

Post by Jens_Lambrecht
m_p_loginrec = dblogin();
DBSETLSECURE(m_p_loginrec);
m_p_dbproc = dbopen(m_p_loginrec, m_p_chServer);
Is there a way to get this working with db library or will I really have
to remove db library from my project an use some ODBC API?

If you have to? Probably not. If you should? DEFINITELY!

Yes, DB-Library is a very nice API, I like it a lot. But Microsoft
thinks differently and has not developed it since the release of
SQL 6.5. They have announced that the version after SQL 2008 will
not accept connections from DB-Library. They may have retracted on
this, but it does not really change the picture. Developing new
applications with DB-Library today is just crazy. Yes, you should
learn ODBC (or OLE DB, but that's a more difficult API).

Using SQL Server Native Client 10, also means that you have access
to all features in SQL 2008. With DB-Library you are severly crippled.

Bill suggested that you should use ADO .Net, but if you are writing
native code that is not really an option.

Jens_Lambrecht

2010-01-13 14:29:01 UTC

Permalink

Both instances of sql server are enabled for remote connections.

Under win 7 or win 2008 server I can connect to those instances of sql
server remotely as long as I use sql server authentication and a sql server
user.

Under other versions of Windows (e. g. XP or 2003 server) I can connect to
those instances of sql server remotely using windows authentication as well.

Therefore I don't think that my problem has something to do with the sql
servers I use.

Best regards
--
Jens Lambrecht

Post by Erland Sommarskog

The most likely reason is that these instances are not enabled for
remote connections. Can you connect to these instance remotely in
other ways?

If you have to? Probably not. If you should? DEFINITELY!
Yes, DB-Library is a very nice API, I like it a lot. But Microsoft
thinks differently and has not developed it since the release of
SQL 6.5. They have announced that the version after SQL 2008 will
not accept connections from DB-Library. They may have retracted on
this, but it does not really change the picture. Developing new
applications with DB-Library today is just crazy. Yes, you should
learn ODBC (or OLE DB, but that's a more difficult API).
Using SQL Server Native Client 10, also means that you have access
to all features in SQL 2008. With DB-Library you are severly crippled.
Bill suggested that you should use ADO .Net, but if you are writing
native code that is not really an option.
--
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Lutz Uhlmann

2010-01-13 14:49:30 UTC

Permalink

I should read this earlier ...

What is the error message you got while trying to connect via db library???

Lutz

Erland Sommarskog

2010-01-13 21:29:13 UTC

Permalink

Post by Jens_Lambrecht
Both instances of sql server are enabled for remote connections.
Under win 7 or win 2008 server I can connect to those instances of sql
server remotely as long as I use sql server authentication and a sql
server user.

Hm, you can connect only with Windows authentication, but not with SQL
authentication? That's kind of odd, as the problem is usually the other
way round. But are these servers enabled for SQL authentication at all?

And this problem is only with DB-Library clients? It does not happen with
SQLCMD of Mgmt Studio.

Post by Jens_Lambrecht
Under other versions of Windows (e. g. XP or 2003 server) I can connect
to those instances of sql server remotely using windows authentication
as well.

Again, does apply only to DB-Lib clients?

Are all SQL Server instance involved named instances, or is any of them
a default instance.

Post by Jens_Lambrecht
Therefore I don't think that my problem has something to do with the sql
servers I use.

Certainly, if can be a problem with DB-Library. Microsoft has not touched
it for a long time, so it may not be compatible with everything's modern.
I think MS did a hack to support named instances, but I seem to recall
that named instances did not always work for me.

Lutz Uhlmann

2010-01-13 14:45:56 UTC

Permalink

Hi Jens!

There could be several reasons für your problem.
- remote connection not enabled on remote server
- tcp-protocoll not enabled on remote server
- wrong tcp-port
- firewall settings on remote server

check if you could establish an simple odbc data source connection to
your remote server.

Lutz

P.S.:

Hallo Jens!
Du kannst es mit deinem Problem auch mal in der Newsgroup
microsoft.public.de.sqlserver probieren!