Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL State 28000 - Error 18456 Logon Failed For User

Posted on 2007-06-26
15
Medium Priority
?
50,533 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
  • 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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

783 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