Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL State 28000 - Error 18456 Logon Failed For User

Posted on 2007-06-26
15
Medium Priority
?
48,919 Views
Last Modified: 2010-02-16
I have a SQL database (SQL 2000) that has an Access2000 front end to it.  Everything works ok for me, but for others it is not.  When they click on the table or the report, they get "Connection Failed - SQLState: "28000" SQL Server Error 18456.  Login failed for user...."  I have read the previous posts on this error and have tried most of them.  I am missing something.  Under the ODBC connection on the client, I have the User DSN and System DSN set up for both of the databases on the SQL server.  I also have the Access database set up under User DSN.  The user is set up as a user on the server where the SQL database resides, with the same permissions that I have.  Under the database in Enterprise Manager, the user is set up under "Logins".  I have not deleted all of the table links and the ODBC connection, I'm not sure how to do this.

The user is able to get into the Access program by unchecking Trusted Connection and typing in her password, but this is a pain.  How do I get this to be automatic.  I have compared my settings to hers and they seem the same.  
0
Comment
Question by:sfrisk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
15 Comments
 
LVL 14

Expert Comment

by:twoboats
ID: 19367902
Need to make the login a user in the database
0
 

Author Comment

by:sfrisk
ID: 19371451
They are a user in the database.  I went into SQL Enterprise Manager, then on the database, users and the application database role and public boxes are checked for that user.  I should add that prior to this, the user was not logging into the domain on a Windows 2000 computer and could get everything to run ok without having to reenter the password.  I bought a Windows XP computer and she is now logging into the domain and that's where the problems started.  Maybe it's not an issue with SQL but with active directory????
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19399092
Are the DSNs set up to use Windows authentication?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:sfrisk
ID: 19402718
How do I check for that?
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19403015
Control panel -> admin tools -> data sources
0
 

Author Comment

by:sfrisk
ID: 19403182
Under the user dsn, I have "with SQL server authentication using a logon ID and password" checked, then connect to SQL server to obtain default settings is checked.  The user logon iD is typed in, the password doesn't seem to save.  I've tried using the "with Windows NT authentication" but when I test the data source, it fails.  Am I missing something with the settings?
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19403318
Okay, so that's the root cause of the problem.

Do you have admin rights on the box to allow changes to be saved, when you modify the settings?

If you check the Windows Auth, and test it, what error message do you get?
0
 

Author Comment

by:sfrisk
ID: 19404078
I have admin rights to the box, I also gave the user admin rights to it and I'm getting the same error, where it will allow the user into the database, but they have to uncheck "trusted connection" and then type in the password.  Under the dsn settings, I set the user up with SA to see what would happen.  It looks to me like it's failing where it's checking the user name and password on the client system because I'm getting the error above with the our domain name/user name, not SA.  I don't understand why it's prompting to uncheck the trusted connection and then works ok when the password is typed in.  Frustrating.  

If I check the windows auth, I get "Microsoft SQL Server - running connectivity tests.... Attemption connection.  Login failed for user 'Domain/User"

TESTS FAILED!

0
 
LVL 14

Expert Comment

by:twoboats
ID: 19404706
Is the XP box a member of the domain?
0
 

Author Comment

by:sfrisk
ID: 19405311
It wasn't.  I added it and the same thing.  I'm going to work on it later today.  Thanks for all of your help so far, I really appeciate it.
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19405640
No problem.

Is the windows user in question a domain user? And have you added the sql login as exactly the same, including the domain reference?

Is the login on sql set to use windows authentication?


0
 

Author Comment

by:sfrisk
ID: 19405675
The windows user is a domain user.  I'm pretty sure that she's set up exactly the same, including the domain reference.  She logs in as I do, but with a different user name, password (obviously).  Where do I check to see if the login on sql is to use windows authentication.  I don't know a lot about SQL.
0
 
LVL 14

Accepted Solution

by:
twoboats earned 2000 total points
ID: 19405683
No problem.

Is the windows user in question a domain user? And have you added the sql login as exactly the same, including the domain reference?

Is the login on sql set to use windows authentication?


0
 
LVL 14

Expert Comment

by:twoboats
ID: 19405704
No problem.

Is the windows user in question a domain user? And have you added the sql login as exactly the same, including the domain reference?

Is the login on sql set to use windows authentication?


0
 
LVL 14

Expert Comment

by:twoboats
ID: 19405906
No problem.

Is the windows user in question a domain user? And have you added the sql login as exactly the same, including the domain reference?

Is the login on sql set to use windows authentication?


0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question