SQL State 28000 - Error 18456 Logon Failed For User

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.  
sfriskAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

twoboatsCommented:
Need to make the login a user in the database
0
sfriskAuthor Commented:
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
twoboatsCommented:
Are the DSNs set up to use Windows authentication?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sfriskAuthor Commented:
How do I check for that?
0
twoboatsCommented:
Control panel -> admin tools -> data sources
0
sfriskAuthor Commented:
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
twoboatsCommented:
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
sfriskAuthor Commented:
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
twoboatsCommented:
Is the XP box a member of the domain?
0
sfriskAuthor Commented:
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
twoboatsCommented:
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
sfriskAuthor Commented:
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
twoboatsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
twoboatsCommented:
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
twoboatsCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.