Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vaaccess
Starting Member
23 Posts |
 Posted - 2005-08-12 : 09:04:15
|
| After reviewing the SQL Logs on a production server that sees a lot of login activity, I discovered a "pattern" of SA Login Failures. The failures are 1-3 a day and usually happen around a general period of time.My guess is that someone has an old SA registered in EM.But, I'd like to confirm it and fix it so I don't see the errors and so I can rule out foul play.The problem is, the logs that are generated just tell me when the login failure occurred. Is there any way to have it tell me the IP address that the login failed from? Any other options?The server sees a tremendous amount of traffic, so whatever I do needs to be as transparent as possible.Thanks. |
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-08-12 : 10:40:09
|
| you should be able to create a profiler trace for failed login attempts from just sa and you should be able to capture the computer name it is coming from I believe. I have'nt used profiler for security before.====================================================Regards,Sean RoussyThank you, drive through |
 |
|
|
vaaccess
Starting Member
23 Posts |
Posted - 2005-08-12 : 10:55:17
|
| Ahhh...Good idea, I'll give that a shot, that should do exactly what I need it to do. :) Thanks. |
|
|
|
vaaccess
Starting Member
23 Posts |
Posted - 2005-08-12 : 11:16:55
|
| Well, crap...The output from the trace is as useless as the error log from what I can tell.It doesn't report a HostName when the trace gets a failed login hit. :( There is a ClientProcessID, however. But, without the HostName that won't help, will it??? |
|
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-08-12 : 11:24:12
|
| one of the eventclasses has a field for NTUserName====================================================Regards,Sean RoussyThank you, drive through |
|
|
|
vaaccess
Starting Member
23 Posts |
Posted - 2005-08-12 : 11:48:08
|
| Yeah, that comes back empty, too. I actually chose to bring in all data columns just to be sure I wasn't missing any relevant data.FYI:Under Events, I only selected Security Audit -> Audit Login FailedUnder Data Columns, I selected everything.Filters I left untouched. I'd be curious to know if I'm mis-using Profiler in this scenario... |
|
|
|
cbot
Starting Member
3 Posts |
Posted - 2005-08-12 : 17:31:35
|
| vaaccess,you're actually not doing anything wrong, it's just that Profiler can't give you any of the info you need. Search Books Online for the phrase "Security Audit Event Category", and you will see in the first result of the search (the page titled "Security Audit Data Columns") that the Event class of "Audit Logon Failed" only returns Event Class and Success. I ran into this same problem a while back, and unfortunately wasn't able to come up with a good solution. If the failures keep happening at a consistent time, you could arguably try sniffing the network traffic, but that seems a little drastic (I never went that far!)Hopefully someone else will have some better ideas! |
|
|
|
|
|
|
|
|
SA Login Failure Audit