SQL Server ODBC Driver Ignores Authentication Setting

From: Spalding (msj@coloradotech.edu)
Date: 10/17/02 

From: "Spalding" <msj@coloradotech.edu>
Date: Thu, 17 Oct 2002 12:38:26 -0700

We are running MSSQL as our data warehouse with MSAccess
as our front-end. Because some users are not on our LAN we
set their ODBC connections with SQL Server Authentication,
but the ODBC Authentication Setting is being ignore,
resulting in the user not getting to login with error
18456.

I have found the articles below and follow it do upgrade
MDAC to version 2.7. This did not take care of the
problem. Any pointers/ideas/suggestions will be greatly
appreciated.

Thanks

Spalding

The information in this article applies to:
Microsoft ODBC Driver for SQL Server, version 3.6
Microsoft SQL Server 2000 (all editions)
Microsoft Data Access Components version 2.6

SYMPTOMSWhen you use the SQL Server ODBC driver version
2000.080.0194, which was released with SQL Server 2000 and
Microsoft Data Access Components (MDAC) 2.6 RTM, the
authentication settings for ODBC data source names (DSNs)
are ignored. The driver attempts to log directly into SQL
Server using NTLM authentication (integrated security)
without prompting for a user ID and password.

This can cause authentication failures, messages that
database objects are not available, or unintentional
modification of the wrong objects when copies of objects
are maintained in multiple databases.
RESOLUTIONTo resolve this problem, obtain the latest
service pack for Microsoft Data Access Components 2.6. For
additional information, click the following article number
to view the article in the Microsoft Knowledge Base:
Q300635 INFO: How to Obtain the Latest MDAC 2.6 Service
Pack
Hotfix
The English version of this fix should have the following
file attributes or later:

   Date Version Size File
name
   -------------------------------------------------------- 

----
   09/22/2000    2000.80.207.0    471,119 bytes    
Sqlsrv32.dll	
   09/22/2000    2000.80.207.0     90,112 bytes    
Sqlsrv32.rll		
   09/22/2000    2000.80.207.0     28,742 bytes    
Odbcbcp.dll	
WORKAROUND
When possible, use an earlier version of the SQL Server 
ODBC driver such as the MDAC 2.5 version (3.70.0820). 
STATUSMicrosoft has confirmed that this is a problem in 
the Microsoft products that are listed at the beginning of 
this article. This problem was first corrected in 
Microsoft Data Access Components 2.6 Service Pack 1.
MORE INFORMATIONThe SQL Server ODBC driver allows you to 
configure datasources to use either Microsoft Windows NT 
authentication (integrated security) or SQL Server 
authentication to log into the database.
The 2000.080.0194 version of the driver ignores the 
authentication specified in the data source name (DSN), 
and attempts to log into the database with NT 
authentication.
If the Windows NT account does not have permission to log 
on to the server, authentication errors are thrown. If the 
account has login permissions, but a different database is 
specified as the default database, "invalid object" errors 
can be thrown. If multiple copies of objects are kept in 
separate databases, unintentional modification of the 
wrong objects can occur.
This problem can cause unexpected behavior in numerous 
applications. For example, when you use Microsoft Access 
to link or query tables in a SQL Server database, no login 
dialog box is presented (because integrated security does 
not prompt for a login), and tables from the default 
database for the NT account are presented. No opportunity 
is given to change the database or login.
When you use Microsoft Project with a project that has 
been saved to a SQL Server database, a connection error is 
thrown followed by a dialog box that requires the user to 
clear the Use Trusted Connection check box and enter a 
valid user ID and password. 
First Published: Nov 14 2000 10:40AM 
Keywords: kbMDAC kbODBC kbGrpDSMDAC kbDSupport 
kbSQLServ2000 kbQFE kbMDAC260sp1Fix kbbug authentication 
ntlm integrated security login log on sql server odbc 
driver invalid object  
.

Relevant Pages

  • Re: Linking tables access - sql server 2005
    ... Another advantage of this method is that you don't need an ODBC setting on the local computer, ... Create a linked table to SQL Server without using a DSN ... Name of the table that you are linking to on the SQL Server database ... Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Need ODBCDirect-ion
    ... Just as a side note, ODBC, or so called "jet" direct is simply a way to ... You need experience with sql server. ... "Upsizing to Microsoft SQL Server" White Paper Available in Download ... Choosing Database Tools White Paper Available in Download Cente ...
    (microsoft.public.access.modulesdaovba)
  • Re: Supporting a variety of database types in an app?
    ... Ralph, thank you for your insightful response. ... To get experience with MS SQL Server and client/server development. ... My rationale for using a file-based database location/selection method: ... ODBC API, but modified it to enhance performance). ...
    (microsoft.public.vb.database)
  • Re: Trusted Connection
    ... HOWEVER, for one of my users, I will create an ODBC DSN ... for my test database ON THEIR COMPUTER, ... So, when I set up the ODBC DSN connection for this user, ... >> Our SQL Server was set up NOT by me, ...
    (microsoft.public.sqlserver.security)
  • RE: [ODBC SQL Server Driver][Shared Memory]ConnectionWrite (send()
    ... the ODBC source using named pipe, but I don't know how to enable named pipes ... If it is not working I am going to use ado to connect to sql server. ... Microsoft Online Community Support ...
    (microsoft.public.sqlserver.connect)