Analysing SQL Server Failed logon attempts generates some interesting findings.

Read How to manage Failed Logon attempts  and Powershell and ErrorLogs  for some background information

I’ve implemented  a daily report on Failed Logon attempts . If  there is something obvious, I analyse and feedback to the relevant application\technology owner. Otherwise I log the failed logon attempts onto a service call. The expectation is for an application owner to verify the failed logon attempt .

 Some things I’ve discovered:

1)       Legacy Scheduled jobs –  the application was decommissioned but components still existed

2)       Aspects of current applications not working properly.

3)       Logon attempts by individuals , who shouldn’t be accessing

This Powershell script – iterates through a list of SQL Server Instances. It  creates a System.Data.Table , and places the results of every  failed logon attempt in the last 24 hrs.

The script pipes the System.Data.Table into a html file.

Read SQL Server – Send email using Powershell with attachment  to incorporate email with this report

                                              
set-location D:\health\SQL_Server
$isodate=Get-Date -format s 
$isodate=$isodate -replace(":","")
$basepath=(Get-Location -PSProvider FileSystem).ProviderPath


$serverpath=$basepath + "\config\instances.txt"
$outputfile="\logs\sql_server_health_SQL_Server_Login_Failed_" + $isodate + ".html"
$outputfilefull = $basepath + $outputfile


$dt = new-object "System.Data.DataTable"
foreach ($instance in get-content $serverpath)
{
    $instance
    $cn = new-object System.Data.SqlClient.SqlConnection "server=$instance;database=msdb;Integrated Security=sspi"
    $cn.Open()
    $sql = $cn.CreateCommand()
    $sql.CommandText = "DECLARE @sqlStatement1 VARCHAR(200)
		SET @sqlStatement1 = 'master.dbo.xp_readerrorlog'
            BEGIN		
            CREATE TABLE #Errors_t2 (LogDate DATETIME,ProcessInfo NVARCHAR(50),vchMessage varchar(2000))
		INSERT #Errors_t2 EXEC @sqlStatement1
		SELECT @@servername as Server , LogDate, RTRIM(LTRIM(vchMessage)) as Message FROM #Errors_t2 WHERE 
		([vchMessage] like '%Login failed%'
		AND DATEDIFF(hh, LogDate, GETDATE()) <= 24
            )
	      DROP TABLE #Errors_t2
	       END"
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()

    
}

$dt | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | ConvertTo-Html -head $reportstyle -body "
SQL Server Login Failed in the last 24 hrs 
" | Set-Content $outputfilefull  

 Read More

How to create a SQL Server Security Audit - SQL Server DBA

Powershell sql server security audit - SQL Server DBA

SQL Server - Database Server Security Audit Process - SQL Server ...

SQL Server – Find current user and sysadmin