Post reply

sql mail reporting failed logins

  • August 23, 2005 at 1:12 pm

    #90629

    Hi everyone.

    Got a bit of a fast ball hoping you could help me out on.

    Need to set up SQL mail to report failed login attempts. I have SQL mail working on my test environment for job failures already.

    Not sure on how to set up the requirement ( Alerts ? ) to email me the failed login attempts.

    Any help on what I need to configure in the Alerts ?

    Any advice on good or bad proceedures appreciated,

     

    Thanks everyone.

  • August 23, 2005 at 11:10 pm

    #584472

    Hiya, pretty straightforward if you want to use std sql agent alert mechanism.

    Create a new alert and select error number 18456.

    select all databases or limit only to those you want to catch failed logins for.

    Ensure you enable the alert (tick box)

    then on the response tab choose email against the operator name you wish to email (assumes you have setup an operator under SQL Agent 'operators'

    Set an appropriate delay between responses (tries to reduce getting spammed by a rogue logon attempt which keeps failing)

    note if you want to just trap certain failures for example failures for login sa then under the alert general tab you can add the value 'sa'  in the error message contains this text field.

    Then test away

    hope this helps

    Derek

  • September 7, 2005 at 4:03 am

    #588025

    Thanks Derek

    Still a bit stuck with this. Set up alert as defined above . All set up alert to monitor 014 and 016 events.

    Doing some testing I try to log into query analyzer to check if I get alerted of failed SQL login attempts.

    when logging in to QA get error 18456 , level 16 , state 1 error, which sound then fire alerts as defined above.

    However nothing is written to event log , and no alert is fired.

     

    Have I missed something in setting these up ?

    cheers

  • September 13, 2005 at 9:37 pm

    #589592

    Hmm, not sure.  ok can you check the properties of the alert you have created and ensure that

    enabled is ticked.

    under response you have selected email for a valid operator.

    Also make sure you have tested mail sent to this operator to ensure sqlagent mail is working.  You can do this test from EM, sql agent, operators.  select the operator and perform a test by clicking test button.

    Also make sure you do not have the /n option specified for sql server startup as this stops sql server logging events to the Windows event log and the agent alert mechanism uses this log for alerting.  Normally by default sql server logs events to the windows event log but the /n option will prevent this.

     

    Also make sure the alert you have created is set for the error 18456 

     

Viewing 4 posts - 1 through 3 (of 3 total)

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