-
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?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|