question

manoj123 avatar image
manoj123 asked

error messsage saying login failed for sa account

Hi I have Error message saying login failed for sa account? But before that the job which owner is sa ran successfully. This is sql server 2000 enterprise edition. Can you please tell me how to troubleshoot it? Thanks
login
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

where are you getting the error message?
0 Likes 0 ·
Thanks for ur answer Fatherjack Job is workig fine everything is working great but There is some error message on the event log I am tryijng to invetigate this error message. Message Login failed for user 'sa'. These message keep on reteating for some hours mostly after the log backup. but not always. Thanks
0 Likes 0 ·
Shawn_Melton avatar image
Shawn_Melton answered
If you are viewing this message in the SQL Server ERRORLOG the full format of that message should include the IP address of the client that is attempting the connection. Something similar to this:

Login failed for user 'sa'. Reason: 
Password did not match that for the login provided. [CLIENT: 10.0.0.12]
I usually will take that IP and if I am in an active directory domain use NSLOOKUP to identify the hostname of the client. Then start looking at Windows Scheduled Task, and if it happens to be the server SQL Server is running on comb through jobs looking for any rogue script or task that is setup. It is a bit more cumbersome to figure out if the client is the SQL Server instance. You can setup a trace for Audit Login and grab the application name to determine what type of client is being used. This is fairly easy to capture if the failed login is occurring during a set time.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for the reply I ran the trace with audit login it doesn;t have enough info the info that i found is Applliaton Name :: IIS , ClientProcessID --3432 Hostname WEB0001 LoginName: sa SPID : 83 ServerName : SQL001 , Starttime : 2013-03-04 15:49:50.247 TextData: Login failed for user 'sa' What will be my next process
0 Likes 0 ·
The information you just provided means WEB0001 server has an IIS application trying to connect to your instance of SQL Server. You will likely find a connection string configured in IIS that is using the "sa" login to access the SQL001 instance.
0 Likes 0 ·
Great that's the explanation that i need to close my ticket Thanks Shawn.
0 Likes 0 ·
raadee avatar image
raadee answered
What does that job do? Do you have other jobs that are configured with SA as owner that are running? Did you change the SA password recently? I remember some strange issues with this a while ago, you could just create a new job that does exactly the same thing with sa as owner and try to run it.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Rade Job is working without any error but there is error mesaage in the sql server log saying that login faild for sa. I am trying to find the reasons for this
0 Likes 0 ·
elliswhite avatar image
elliswhite answered
On Connecting through Windows Login you can quickly fix the error by following steps:- First Step to remove this error is to login into SQL Server through Windows Authentication then and then make a right click on Server Node of the SQL Server management Studio and select properties Go to Security Tab and go to Server Authentication field. Select SQL Server and Windows Authentication Mode. And in Login auding select Failed logins only. You should be able to login with your SQL Server username. If you are not able to login with your SQL Server username after following above steps, it is quite possible your username is incorrect, please check again.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.