Email upon any login failure

Question

Email upon any login failure

brekher

SSCommitted

Points: 1514
More actions
August 7, 2009 at 1:00 pm

#213233

Hi,
Does anyone have any recomendations or code that would email upon login failure of any kind for Sql 2k5? I searched through existing scripts and tried setting up an alert but perhaps am missing something as alerts are not emailing me when I purposely try to login with a bogus account yet it is in the log file that "login failed for user xyz". Any help would be greatly appreciated. Thanks!

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jack Corbett SSC Guru Points: 184388 More actions · Answer 1

Do you have database mail setup? Have you set up the alert system to use database mail? Lastly, how did you setup the alert? I setup one checking for error 18456 which is login failed and it worked immediately.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 2

My recommendation to you is to take a slightly different approach. It would be better if you run a job or process periodically (say every 15 minutes) that checks for all login failures and then sends you a single email. Otherwise, a brute force attempt is going to smoke your mailbox. Actually, if you're tracking logon failures, the information is getting written to the Application Event Log of the OS. So if your organization already has tools in place to monitor these event logs, you could extract the information from there.

K. Brian Kelley
@kbriankelley

Lowell SSC Guru Points: 323482 More actions · Answer 3

Jack Corbett (8/7/2009)
Do you have database mail setup? Have you set up the alert system to use database mail? Lastly, how did you setup the alert? I setup one checking for error 18456 which is login failed and it worked immediately.

ok i'm following along on this one; i've set up database mail, and sent email in some triggers and other examples so i know that works....but where is the "Alert System" where you can tell it to watch a specific error number? is that from the OS or from SQL? is it something in Notification Services?

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!

Jack Corbett SSC Guru Points: 184388 More actions · Answer 4

Lowell (8/7/2009)
Jack Corbett (8/7/2009)
Do you have database mail setup? Have you set up the alert system to use database mail? Lastly, how did you setup the alert? I setup one checking for error 18456 which is login failed and it worked immediately.
ok i'm following along on this one; i've set up database mail, and sent email in some triggers and other examples so i know that works....but where is the "Alert System" where you can tell it to watch a specific error number? is that from the OS or from SQL? is it something in Notification Services?

SQL Server Agent -> Alerts.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jack Corbett SSC Guru Points: 184388 More actions · Answer 5

Brian probably has the best idea. Didn't even think about the brute force attack, although I did almost mention reading the SQL Server Error Log via a job.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Lowell SSC Guru Points: 323482 More actions · Answer 6

Jack Corbett (8/7/2009)
SQL Server Agent -> Alerts.

gawd after you said it, of course i know better...duh....thanks Jack; i thought it was something new.

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!

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 7

Jack Corbett (8/7/2009)
Brian probably has the best idea. Didn't even think about the brute force attack, although I did almost mention reading the SQL Server Error Log via a job.

So I take it you've never been on the receiving end of an IDS/IPS system which fires email alerts for every high or critical alert? Like when auditors/pen testers are in? On a good day we were getting 100-150 emails. When they came in, it shot to well over 2,000 in just a couple of hours. We ended up turning off IDS/IPS for their IPs. Once you feel that pain and realize that there's so much noise the alerts no longer do any good, you start thinking about event supression techniques. 🙂

K. Brian Kelley
@kbriankelley

Jack Corbett SSC Guru Points: 184388 More actions · Answer 8

Fortunately I have never had to deal with that. Of course that's why you are the security expert and I'm not.:-D

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question