Page 1 of 2 12 LastLast
Results 1 to 15 of 30

Thread: Login failed for user 'username"...

  1. #1
    Join Date
    Mar 2004
    Posts
    13

    Login failed for user 'username"...

    I got this problem after a running query was stop by me. When it stops, I start to have the login errors.
    I checked:
    - the ODBC System DSN: all the user logins were given the same error for all the databases, only the "sa" user was able to connect;
    -I checked the SQL server login passwords under "Security Logins".
    -The Windows domain login were OK.
    -I checked the Articles about this error in the MS Technet Knowledge database: All the connections were OK and working.
    I was able to put the system to work using the account system and the user "SA" in the ODBC DNSs.
    But the use of email reports sent by the SQL is not available with the system account and more important, to keep the interface of the users via ACCESS to the SQL server using the SA user is not good security.
    How can be fixed this problemm, to have the Windows login accepted by the SQL server?
    Thank you.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    To use windows authentication, you need to to grant login to sql server to your windows user accounts.

    Do you see windows user names DOMAIN\LOGIN in login list of SQL Server?

  3. #3
    Join Date
    Mar 2004
    Posts
    13
    Yes, of course.
    As I wrote, I checked the windows logins under the SQL "Security" Logins.

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Do you use windows or sql authentication in dsn? Can you connection to sql server with those ids in query analyzer?

  5. #5
    Join Date
    Mar 2004
    Posts
    13
    I used Windows domain logins. The query analyzer also failed to connect with the Windows login. Only was able to connect with the SQL "sa" login.
    Also there is a TEST under the "ODBC Data Source Administrator" and there also all the Windows login defined under SQL failed.

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Have to choose windows authentication when open query analyzer, can't specify id/password for windows authentication. Look at sql books online for details about authentication model.

  7. #7
    Join Date
    Mar 2004
    Posts
    13
    I don't quite follow your answer.
    You can set the Query Analyser login connection any way you want using the Windows or SQL logins if defined under the SQL Enterprise MAnager "Security-Logins".
    In this case the only one accepted by the SQL is "sa" user login.
    But it worked more than 2 years without any problem.
    This is why I am lookig the way to "repair" the corrupted link. My guess is that some of the ODBC file drivers could be corrupted?

  8. #8
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Query analyzer does not use ODBC DSN, so if you can't connect to your server using Query Analyzer then it must be the login that has the problem not the ODBC driver.

    In your server check the default database for the login account your trying to connect with, and make sure the login has access to the default database.

  9. #9
    Join Date
    Sep 2002
    Posts
    5,938
    And check the id you used in em -> security -> logins to see what type it is. Use sql authentication if it's standard type, otherwise have to use windows authentication.

  10. #10
    Join Date
    Mar 2004
    Posts
    13
    Yes, but it use the login defined under the Security Logins, which are either SQL logins and Windows logins.
    In this case the behavior is the same using the Quely Analyser or the ODBC connection Tester: only is accepted the SQL user "sa".
    What you suggest to check I already did it several time, before calling for help.

  11. #11
    Join Date
    Sep 2002
    Posts
    5,938
    What does the id look like in enterprise manager?

  12. #12
    Join Date
    Mar 2004
    Posts
    13
    Under EntManager\Security\Logins:
    Name Type
    domainName\username1 Windows User
    domainName\username2 Windows User
    domainName\username3 Windows User
    .....
    domainName\usernameN Windows User
    sa Standar

    All have Server Access "Permit"
    and default Database "master".

    I want to remind you that this definition worked more than 2 years OK until I got the problem 4 days ago.

  13. #13
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    Did your "system admins" migrate all your domain logins to use active directory?

  14. #14
    Join Date
    Mar 2004
    Posts
    13
    None.
    I am the "System Admin" also.
    There is not any change made in the Active Directory.
    The problem is only with one SQL server.
    The others SQL servers are working fine with these Windows logins in the same domain.

  15. #15
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    if u r suspecting that something is corrupted, apply sql server service pack again. BTW. what is your SQL server sp?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •