Technical Article

Passively detect attempts to guess passwords

,

This scripts looks for possible malicious attempts to hack your SQL Server instance. This is done by reading the current error log and searching for multiple failed login attempts from the same IP address. Please note that this requires that “Login auditing for failed logins” is enabled in the properties of your SQL Server instance.

To run this script, simply copy and paste the script into a connected query tab in Microsoft SQL Management Studio and then execute it. It will output a summary list of all failed attempts to guess passwords within the last 24 hours on your SQL Server instance.

begin

create table #errorLog (
LogDate datetime, 
ProcessInfo varchar(250), 
[Text] varchar(8000)
)


--read the current error log 
insert into #errorLog (LogDate, ProcessInfo, [Text])
exec sp_readerrorlog 0, 1 --0 = current(0), 1 = error log


--find brute force attempts to guess a password
select 
replace(right([Text],charindex(' ', reverse([Text]))-1), ']', '') as IP,
substring([Text], charindex('''', [Text]) + 1,  charindex('.', [Text]) - charindex('''', [Text]) - 2  ) as [User],
count(LogDate) as [Number of login attempts],
min(LogDate) as [Attack started],
max(LogDate) as [Attack ended],
datediff(minute, min(LogDate), max(LogDate)) as [Attack duration in minutes],
cast(cast(count(LogDate) as decimal(18,2))/isnull(nullif(datediff(minute, min(LogDate), max(LogDate)),0),1) as decimal(18,2)) as [Attack intensity - Login attempts per minute]
  
from #errorLog

where
--limit data to unsuccessful login attempts in the last 24 hours
ProcessInfo = 'Logon'
and [Text] like 'Login failed for user%'
and datediff(hour, LogDate, getdate()) <= 24 

group by
[Text]

having
count(LogDate) > 3 --filter out users just typing their passwords incorrectly

order by
[Number of login attempts] desc,
[Attack ended] desc



--clean up temp tables created
drop table #errorLog


end

Rate

4.33 (12)

You rated this post out of 5. Change rating

Share

Share

Rate

4.33 (12)

You rated this post out of 5. Change rating