Using PowerShell to Discover Login Failures on SQL Server

 

Using PowerShell to Discover Login Failures on SQL Server

If you have PowerShell installed (even if it isn’t the SQL Server 2008 provider), you can and should check your servers each day to see if you have login failures. This is very useful to help ensure that your server is safe and secure. Whenever someone tries to log in to SQL Server and fails, the server records event number 18456 in the Windows Application log. You can use the PowerShell command-let called get-eventlog to check for that number by opening the event log and reading it, sending the results to another command-let called where-object, using the piping symbol:

   1: get-eventlog application | Where-Object {$_.EventID -eq 18456}

But this command brings nothing back if there is no failed logins – and I want a little something more. I take the “if” command to test for any issues, and report back merely that there were failed logins or not – if there are, I know I need to look in the logs anyway, and do a little more investigations about what else is going on at that same time:

   1: if (get-eventlog application | Where-Object {$_.EventID -eq 18456}) { write-host "Bad logins detected" } else { write-host "No bad logins detected."}

As always, you mileage may vary, use a test system, don't run with scissors, etc.

Published 24 February 09 09:02 by Buck Woody

Carpe Datum : Using PowerShell to Discover Login Failures on SQL Server