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.

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?

Yes, of course.
As I wrote, I checked the windows logins under the SQL "Security" Logins.

Do you use windows or sql authentication in dsn? Can you connection to sql server with those ids in query analyzer?

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.

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.

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?

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.

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.

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.

What does the id look like in enterprise manager?

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.

Did your "system admins" migrate all your domain logins to use active directory?

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.

if u r suspecting that something is corrupted, apply sql server service pack again. BTW. what is your SQL server sp?

check your server network utility and see whether anybody disabled TCP/IP and Named pipes protocols.

if so just enable it and recycle the sql services.

SQL7.0 SP4.
Reinstalling is my last option (maybe the only one). But I was trying to know if there was any specific repair for it that someone applied before.
Thank you guys anyway for your time and answers.

jtabio, you have windows accounts only expect sa from your list of sql logins. That means those users should connect to sql server with windows authentication. They have to logon windows with their account then choose windows authentication in either odbc dsn or other sql tools to connect to sql server, can't input their account/password there.

Mr rmiao, if you read all my answers
carefully you will understand that what you are point to is not related to this problem. Thanks anyway.

This is from your fist post: 'How can be fixed this problemm, to have the Windows login accepted by the SQL server?'. And my response was that to have the Windows login accepted by the SQL server, you have to use windows authentication to connect to sql server.

Yes, but read please the whole first post and you will see that I am referring that using the Windows logins
is not possible to connect.
If you follow the whole secuence of postings, you will realize that a whole set of possible checkings were already made in relation to Windows logins in the SQL server.

I know you checked those accounts in sql, but my point is use which authentication model to connect to sql for those accounts.

Re-Posting

check your server network utility in your server and see whether anybody disabled TCP/IP and Named pipes protocols.

if so just enable it and recycle the sql services.

TCP/IP and Named pipes protocols are OK.
I did that before sending the 1st posting.
Also checked the SQL ErrorLog file for any possible problem in the ports. All is OK.

Can you login to the server box and try to connect to sql server using windows authentication?

That is the problem: not able to connect to the SQL with any windows logins. Only accepts the "sa" login.

How did you connect windows account to sql server? Keep in mind that you can't type id/password for windows account, it's different from connect with sa. Have to choose windows authentication.

Mr rmiao,
Thanks, but I can't understand what you are talking about.

1. Please see attached document....
2. Please see the server properties

1. Open Enterprise Manager
2. Click on your server name
3. Right click on your server name
4. Click Properties
5. Click on the "Security" tab
6. Select "SQL Server and Windows" as authentication.
7. restart the sql service

When you connect to sql server with query analyzer, you'll get login screen. You put sql server name there and have a choice of 'connect using'. When connect with sa, you choose 'sql server authentication'. Put sa as login name and type password then click on ok. When connect with windows account, you HAVE to logon windows with that account first, open query analyzer and choose 'windows authentication' then click on ok. Same for odbc dsn. By the way, can you tell us how did you do when try to connect to sql server with windows account in query analyzer?