Login faield for user 'sa'

Question

Login faield for user 'sa'

SkyBVI

SSCoach

Points: 15128
More actions
May 4, 2011 at 8:04 am

#235645

Yesterday, i had to change the sa password on a dev server.
From since then, I am getting errors in sql logs that
Login failed for user 'sa' ; client local machine. (every minute, 2 errors)
error 18456, severity 14, state 8.
How can I fix this.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

SKYBVI (5/4/2011)
Yesterday, i had to change the sa password on a dev server.
From since then, I am getting errors in sql logs that
Login failed for user 'sa' ; client local machine. (every minute, 2 errors)
error 18456, severity 14, state 8.
How can I fix this.
Regards,
Sushant

every minute sounds like a job was created to try and do something...send emails, logging some performance stuff or something..

did you check all the jobs on the dev server to see what they are doing? maybe the password needs to be updated?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Another possibility is an app is trying to do something against that instance. Somewhere there is a connection string with sa in it that needs to be changed too.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SkyBVI SSCoach Points: 15128 More actions · Answer 3

No Job is supposed to run every minute.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

SkyBVI SSCoach Points: 15128 More actions · Answer 4

SQLRNNR (5/4/2011)
Another possibility is an app is trying to do something against that instance. Somewhere there is a connection string with sa in it that needs to be changed too.

How to find that.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Documentation (what apps are connecting to the server and manually check each of them)

Set up a server side trace

Set up a logon trigger at the sql server level to capture the macinename and ip address of the source of the problem.

You should also check scheduled tasks at the server level (not sql jobs but os level scheduled tasks)

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Pouliot Philippe SSCommitted Points: 1912 More actions · Answer 6

Try with SQL Profiler or Minotor, or in the Error log u will see [CLIENT : IP]

find the hostname of the IP with ping -a.

SkyBVI SSCoach Points: 15128 More actions · Answer 7

Pouliot Philippe (5/4/2011)
Try with SQL Profiler or Minotor, or in the Error log u will see [CLIENT : IP]
find the hostname of the IP with ping -a.

Error says Client : < local machine>

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 8

Maybe this can help out.

Failedconnectiontracker is using sqlserver event notifications to capture all login info.

We have a dba database on every sqlserver instance to host all sysadmin/dba systems stuff.

In the script you'll see this database called "ddbaserverping"

Just create your own dba database and replace ddbaserverping with your own dbname.

I hope it helps.

Johan

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

ALZDBA (5/4/2011)
Maybe this can help out.
Failedconnectiontracker is using sqlserver event notifications to capture all login info.
We have a dba database on every sqlserver instance to host all sysadmin/dba systems stuff.
In the script you'll see this database called "ddbaserverping"
Just create your own dba database and replace ddbaserverping with your own dbname.
I hope it helps.
Johan

Great Stuff Johan

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SkyBVI SSCoach Points: 15128 More actions · Answer 10

ALZDBA (5/4/2011)
Maybe this can help out.
Failedconnectiontracker is using sqlserver event notifications to capture all login info.
We have a dba database on every sqlserver instance to host all sysadmin/dba systems stuff.
In the script you'll see this database called "ddbaserverping"
Just create your own dba database and replace ddbaserverping with your own dbname.
I hope it helps.
Johan

Great Work...

Thanks a lot..

That saved a lot of time

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Lowell SSC Guru Points: 323518 More actions · Answer 11

did you find out what it was? was it a job or a script or application that was running?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SkyBVI SSCoach Points: 15128 More actions · Answer 12

I get this wehn i run tht script after craeating new db :--

Table [T_DBA_FailedConnectionTracker] Created

trustworthy switched to ON

Msg 2714, Level 16, State 21, Line 2

There is already an object named 'N_Failed_Login_Notification' in the database.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

SkyBVI SSCoach Points: 15128 More actions · Answer 13

and the created table T_DBA_FailedConnectionTracker is empty

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

SkyBVI SSCoach Points: 15128 More actions · Answer 14

Ok

i drop the event notification:-

DROP EVENT NOTIFICATION N_Failed_Login_Notification ON SERVER

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP