I have recently installed SQL server 7.0 with SP3 running under Windows
2000.  Using the Administrator account, I can create an ODBC System Data
Source which appears to connect to the server without problem.  However
when I try to use this datasource from an account which has power user
privileges, I cannot connect.  If I start from the Windows 2000 control
panel, I get the message:

Connection failed
SQL state '28000'
SQL server error 18456
[Microsoft][ODBC Server Driver][SQL server] login failed for user
<servername><username>

I get a similar message if I start from the Enterprise Manager.  I have
set authentication to Windows NT, but I get the same result if I try SQL
server authentication.

What am I doing wrong?

Brian J. Swain

try to establish a link server in SEM to connect to the ODBC data source
from SQL, you can use sa account to test it.

This posting is provided ?AS IS? with no warranties, and confers no
rights.

--------------------



| X-Mailer: Mozilla 4.75 [en]C-CCK-MCD videotron  (Windows NT 5.0; U)
| X-Accept-Language: fr-CA,en
| MIME-Version: 1.0
| Newsgroups: microsoft.public.sqlserver.server
| Subject: Connect from Windows 2000 user account
| Content-Type: text/plain; charset=us-ascii
| Content-Transfer-Encoding: 7bit
| Lines: 20
| Date: Mon, 03 Dec 2001 17:09:59 -0500
| NNTP-Posting-Host: 24.201.108.183

| X-Trace: wagner.videotron.net 1007417276 24.201.108.183 (Mon, 03 Dec 2001
17:07:56 EST)
| NNTP-Posting-Date: Mon, 03 Dec 2001 17:07:56 EST
| Path:
cpmsftngxa07!tkmsftngxs02!cpmsftngxa09!tkmsftngp01!newsfeed00.sul.t-online.d
e!t-online.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!sunqbc.risq.qc
ca!wesley.videotron.net!wagner.videotron.net.POSTED!not-for-mail
| Xref: cpmsftngxa07 microsoft.public.sqlserver.server:183808
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| I have recently installed SQL server 7.0 with SP3 running under Windows
| 2000.  Using the Administrator account, I can create an ODBC System Data
| Source which appears to connect to the server without problem.  However
| when I try to use this datasource from an account which has power user
| privileges, I cannot connect.  If I start from the Windows 2000 control
| panel, I get the message:
|
| Connection failed
| SQL state '28000'
| SQL server error 18456
| [Microsoft][ODBC Server Driver][SQL server] login failed for user
| <servername><username>
|
| I get a similar message if I start from the Enterprise Manager.  I have
| set authentication to Windows NT, but I get the same result if I try SQL
| server authentication.
|
| What am I doing wrong?
|
| Brian J. Swain
|

Thanks for your response.  In the meantime I have partly solved the
problem.  I find that I can connect from a Power User account to a
database using SQL server authentication.  The key to success is
correctly filling in the SQL server configuration window under Control
Panel/Administrative tools/Data sources (ODBC).  When SQL server
authentication has been chosen, the box "Connect to SQL server to obtain
default settings for the additional configuration options" must be
checked and the SQL account (sa) and password entered in the text boxes
which are enabled by choosing this option.  This is despite the fact
that your WINDOWS 2000 account name is presented as the default reply!

I would still prefer to connect using Windows NT authentication, but
cannot see how to follow your advice to establish a link server.
On-line books says that the way to do this from Server Enterprise
Manager is "Create a linked server definition by using the SQL Server
Enterprise Manager console tree and the linked Servers node (under
Security)"  I cannot find the linked Servers node or Security.  Can you
tell me where to look?  Since you say "you can use sa account to test
it" does this mean that it is another method to use SQL server
authentication.  If so, why can't I use Windows NT authentication?  The
Data Source administrator window says "A System data source is visible
to all users on this machine"

I would appreciate any help you can give.

Brian J. Swain