Login faield for user 'sa'

Question

Post reply

Login faield for user 'sa'

Viewing 11 posts - 16 through 25 (of 25 total)

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

SkyBVI SSCoach Points: 15128 More actions · Answer 1

Lowell (5/4/2011)
did you find out what it was? was it a job or a script or application that was running?

select * from T_DBA_FailedConnectionTracker

order by tsRegistration desc

I get :-

<ApplicationName>.Net SqlClient Data Provider</ApplicationName>

using 'sa' and trying every minute to connect to master db

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Track down the ip address from that log table you just created and find out what application resides there. An application is using .Net 3.5 to connect to the database, you need to find the source machine and connection string that is doing it.

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

SQLRNNR (5/4/2011)
Track down the ip address from that log table you just created and find out what application resides there. An application is using .Net 3.5 to connect to the database, you need to find the source machine and connection string that is doing it.

This was in the failedLogin data....

where is the ip adrress??

<EVENT_INSTANCE>

<EventType>AUDIT_LOGIN_FAILED</EventType>

<TextData>Login failed for user 'sa'. [CLIENT: <local machine>]</TextData>

<HostName>servername</HostName>

<ApplicationName>.Net SqlClient Data Provider</ApplicationName>

<ServerName>servername</ServerName>

<DatabaseName>master</DatabaseName>

</EVENT_INSTANCE>

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

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

I'm glad you managed to use the failedconnectiontracker 😉

So it must be a local process, non sqlagent direct related.

( i mean a sqlagent job has recognisable text sqlagent in the application name)

It can be e.g. an exe started by sqlagent, or any other stuff that builds its own connection.

Double check if there are scheduled tasks that connect to your instance and are using the sa account.

Of course that is to be avoided !

I'll be out of the office for the remainder of the week, but I'm sure our peers will be supporting you on this thread.

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

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

SQLRNNR (5/4/2011)
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

Thanks.

It's is one of the things we install on every instance, but only enable if we get alerts for failed logins or for those instances where we would expect sampling.

Of course there is also a ConnectionTracker event notification. If you search "T_DBA_ConnectionTracker" at SSC you'll find the script.

We record every connecting client/application_name/user/ipaddress combination once a day on every instance to follow up if our licensing still is ok (cal/proc based choice)

You can also find its origin in my little article called "http://www.sqlservercentral.com/articles/Administration/64974/"http://www.sqlservercentral.com/articles/Administration/64974/

And in my thread having troubles with one of its first versions having performance problems:

http://www.sqlservercentral.com/Forums/Topic851938-1281-1.aspx

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

SkyBVI SSCoach Points: 15128 More actions · Answer 6

I founded out the application whcih was trying to connect every minute.

I went into its (application) database connection settings and updated the 'sa' password and everything seems to be fixed now..No more failed logins.

Thanks a lot every1.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

That's good to hear. Glad you found and fixed it. I would recommend getting that application off of using the sa account.

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

Lowell SSC Guru Points: 323518 More actions · Answer 8

change it again. if you typed in the password in a config file, it's in clear text in that file and a security risk of the highest order.

create a new user with the name of the application as the SQL Login name, and give it a new password.

then change the config file agian.

that way in the future, when you see Login Failed for "AppScanningEveryMinute", you have a better clue about what is going on.

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 9

Lowell (5/5/2011)
change it again. if you typed in the password in a config file, it's in clear text in that file and a security risk of the highest order.
create a new user with the name of the application as the SQL Login name, and give it a new password.
then change the config file agian.
that way in the future, when you see Login Failed for "AppScanningEveryMinute", you have a better clue about what is going on.

Agreed

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

SkyBVI

SSCoach

Points: 15128

May 5, 2011 at 10:01 am

#1320518

.

Regards
Sushant Kumar
MCTS,MCP

SkyBVI SSCoach Points: 15128 More actions · Answer 11

Lowell (5/5/2011)
change it again. if you typed in the password in a config file, it's in clear text in that file and a security risk of the highest order.
create a new user with the name of the application as the SQL Login name, and give it a new password.
then change the config file agian.
that way in the future, when you see Login Failed for "AppScanningEveryMinute", you have a better clue about what is going on.

Excellent thought !

Regards
Sushant Kumar
MCTS,MCP