SQL Server Brute Force Attack Detection: Part 4 – DEVELOPPARADISE

SQL Server Brute Force Attack Detection: Part 4

Parts in the Series


For the last part of this series, we will go over an alternate implementation of the login monitor that utilizes Powershell and the Window Task Scheduler API. This method is both more secure and more effective at protecting the database server against malicious login attempts while continuing to use built-in functionality in Windows and T-SQL for managing the firewall rules.


The original article in this series introduced a way to implement protection against brute force login attacks entirely in T-SQL. While simple to implement, it introduces a vulnerability by making the SQL Server service account a local administrator (a requirement to interact with the Windows firewall via xp_cmdshell and netsh). This is problematic as it gives any user account in the sysadmin server role the ability to run shell commands within an elevated security context, greatly increases the attack surface area of the server, should the database be compromised (I admittedly didn’t have a full understanding of how SQL Server executes xp_cmdshell). There are also plenty of reasons to prevent regular users that have sysadmin-level access to the database from having administrator access to the OS.

Windows Task Scheduler happens to have a rich API that allows triggering any program off of specific events in the event log as well as passing event data as parameters. In this implementation, I’ve incorporated all of the code for detecting failed logins and creating firewall rules into PowerShell scripts. This allows us to isolate the elevated security context needed for interacting with the firewall to within Task Scheduler and remove the dependency on extended stored procedures used in T-SQL. The implementation also continues to provide functionality for unblocking clients via T-SQL, such as through 3rd-party code employing a user password reset API that would need to unblock the client IP in the firewall.

Using the Code

Windows Task Scheduler API

Admittedly, this is the first time I’ve worked with event-based triggers in Task Scheduler. Task Scheduler provides the same UI for creating a custom event filter as is provided by the Event Viewer for custom views. This is very handy as the query syntax can be cumbersome to work with.

SQL Server Brute Force Attack Detection: Part 4

A feature not provided by the UI (but reasonably well-documented) is the ability to reference the event data using attributes in the XML that defines the task. After setting up a task triggered by event IDs 17828, 17832, 17836 and 18456, I exported it, then edited the generated XML file. The key changes are the addition of <ValueQueries> and <Value name=> nodes that specify XPath queries that extract data from the event data XML, then assign the data to named variables that can be passed to the Powershell script.

  <Triggers>     <EventTrigger>       <Enabled>true</Enabled>       <Subscription>&lt;QueryList&gt;&lt;Query Id="0"         Path="Application"&gt;&lt;Select Path="Application"&gt;*        [System[(Level=2 or Level=4 or Level=0) and (EventID=17828 or         EventID=17832 or EventID=17836 or EventID=18456)]]&lt;/Select&gt;        &lt;/Query&gt;&lt;/QueryList&gt;</Subscription>       <ValueQueries>         <Value name="EventData">Event/EventData/Data</Value>         <Value name="EventID">Event/System/EventID</Value>       </ValueQueries>     </EventTrigger>   </Triggers>  ...    <Actions Context="Author">     <Exec>       <Command>powershell.exe</Command>       <Arguments>-command "&amp; {. ./LoginMonitor.ps1; On-FailedLogin        '$(EventID)' '$(EventData)'}"</Arguments>       <WorkingDirectory>%ProgramData%/SQL Server Login Monitor/</WorkingDirectory>     </Exec>   </Actions> </Task>

At first, I struggled a little with the syntax. Apparently, the API only supports a limited version of XPath1.0 and doesn’t allow referencing unnamed elements by index, particularly the <EventData><data> tags (I’m also not the greatest at parsing XML). After some testing, I was able to extract the event data as a comma-separated string, which could be easily split into an array in Powershell. This along with the event ID are all we need to examine a failed login attempt and block the offending IP address based on methodology covered in previous segments.


The PowerShell code is contained in a single script file LoginMonitor.ps1 with two functions called by each of the tasks. Writing this script involved a fair amount of manual inspection of the event data included in each of the four events. Below is an example of the data elements from the 18456 login failure event:

<EventData>   <Data>sa</Data>   <Data>Reason: An error occurred while evaluating the password.</Data>   <Data>[CLIENT:]</Data>   <Binary>   184800000E0000000A000000530051004C005300450052005600450052000000070000006D00610073007400650072000000   </Binary> </EventData>

SQL Server includes all of the event data within <EventData> tags, but omits some detail that is provided in xp_readerrorlog. The IP address of the client is consistent and enclosed in [CLIENT:] (the same as log entries obtained from xp_readerrorlog).

The Extract-EventData function parses the event data:

function Extract-EventData {     [cmdletbinding()]     param     (         [parameter(position = 0, Mandatory=$true)]         [int]         $EventID,         [parameter(position = 1, Mandatory=$true)]         [string]         $EventData     )      [string] $UserID = ''     [string] $Message = ''     [string] $IPAddress = ''      $EventDataArray = $EventData.Split(',')      if($EventID -eq 18456)     {         if($EventDataArray.Length -gt 0)         {             $UserID = $EventDataArray[0].Trim()         }         if($EventDataArray.Length -gt 1)         {             $Message = $EventDataArray[1].Trim()         }     }# For some reason the event description is not provided for these events     elseif($EventID -eq 17828)     {         $Message = 'The prelogin packet used to open the connection is structurally invalid;          the connection has been closed. Please contact the vendor of the client library.'     }     elseif($EventID -eq 17832)     {         $Message = 'The login packet used to open the connection is structurally invalid;          the connection has been closed. Please contact the vendor of the client library.'     }     elseif($EventID -eq 17836)     {         $Message = 'Length specified in network packet payload did not match number of bytes read;          the connection has been closed. Please contact the vendor of the client library.'     }     else     {         return     }      # Use Regex to extract client IP address     $Regex = [Regex]::new('(?<=/[CLIENT: )(.*)(?=/])')     foreach ($data in $EventDataArray)     {         $Match = $Regex.Match($data)         if ($Match.Success)         {             $IPAddress = $Match.Value.Trim()             break         }     }      $UserID     $Message     $IPAddress     return }

Next, the extracted fields are passed to the Log-FailedLogin function that logs the event data to the database using the stored procedure LogFailedLogin which determines whether the client should be blocked or not. If the client is flagged to be blocked, the SP returns the name of the firewall rule signaling the PowerShell function to create the firewall rule.

function Log-FailedLogin {     [cmdletbinding()]     Param     (         [parameter(position = 0, Mandatory=$true)]         [System.Data.SQLClient.SQLConnection]         $Connection,         [parameter(position = 1, Mandatory=$true)]         [int]         $EventID,         [parameter(position = 2, Mandatory=$true)]         [string]         $IPAddress,         [parameter(position = 3, Mandatory=$true)]         [AllowEmptyString()]         [string]         $UserID,         [parameter(position = 4, Mandatory=$true)]         [AllowEmptyString()]         [string]         $Message     )      $Command = New-Object System.Data.SQLClient.SQLCommand     try     {         $Command.Connection = $Connection         $Command.CommandText = 'EXEC dbo.LogFailedLogin @EventID, @IPAddress, @UserID, @Message'          $Command.Parameters.AddWithValue('@EventID', $EventID)         $Command.Parameters.AddWithValue('@IPAddress', $IPAddress)         $Command.Parameters.AddWithValue('@UserID', $UserID)         $Command.Parameters.AddWithValue('@Message', $Message)          $Reader = $Command.ExecuteReader([System.Data.CommandBehavior]::SingleRow)          $FirewallRule = ''          try         {             if($Reader.Read())             {                 #SP will return a result if a firewall rule needs to be created.                 $FirewallGroup = $Reader.GetString(0)                 $FirewallRule = $Reader.GetString(1)                 New-NetFirewallRule -Direction Inbound -DisplayName $FirewallRule                  -Name $FirewallRule -Group $FirewallGroup -RemoteAddress $IPAddress -Action Block             }         }         finally         {             $Reader.Close()             $Reader.Dispose()         }                  return $FirewallRule     }     finally     {         $Command.Dispose()     } }

The LogFailedLogin SP is provided below. A key change is to the previously used “look-back time” parameter in the Config table that determined how far back to scan the event logs for failed logins. In this implementation, since we’re tracking failed login attempts as they come in, we need a way to clear out clients if the user enters a bad password once or twice, then successfully authenticates. Otherwise, they could enter a bad password a third time (assuming a 3 login threshold) sometime in the future, then unexpectedly get blocked. I’ve set this parameter to default to 15 minutes and the ClientStatus table includes a column CounterResetDate used to determine when to remove the record under these circumstances.

CREATE PROCEDURE [dbo].[LogFailedLogin] (     @EventID int,     @IPAddress VARCHAR(100),     @UserID VARCHAR(128),     @Message VARCHAR(1000) ) AS BEGIN     SET NOCOUNT ON;     DECLARE @LogDate DATETIME = GETDATE();     DECLARE @FailedLoginThreshold INT;     DECLARE @FirewallGroup VARCHAR(100) = 'SQL Server Login Monitor'     DECLARE @FirewallRules TABLE     (         FirewallGroup VARCHAR(100),         FirewallRule VARCHAR(255)     )          IF @IPAddress = '<local machine>' -- Ignore login failures on local machine         RETURN;      IF NOT EXISTS (SELECT * FROM ConfigEvent                    WHERE EventID = @EventID                                     AND Block = 1)     BEGIN         RETURN;     END      IF EXISTS (SELECT * FROM ConfigMsgFilter -- Check event message against exclusions                WHERE CHARINDEX(FilterText, @Message) > 0)     BEGIN         RETURN;     END      IF @UserID = '' SET @UserID = NULL;      SELECT @FailedLoginThreshold = ConfigValue     FROM Config     WHERE ConfigID = 2;      INSERT INTO EventLog(IPAddress, Action, EventDesc)     VALUES(@IPAddress, 'Login Failure', @Message);      MERGE INTO ClientStatus t USING     (         SELECT @IPAddress,           @LogDate,             DATEADD(MINUTE, ConfigValue, @LogDate)         FROM Config         WHERE ConfigID = 1     )AS s(IPAddress, LogDate, CounterResetDate)     ON t.IPAddress = s.IPAddress     WHEN MATCHED THEN       UPDATE SET t.LastFailedLogin = s.LogDate,         t.FailedLogins = t.FailedLogins + 1,         t.CounterResetDate = CASE WHEN t.Blocked = 0 THEN s.CounterResetDate END     WHEN NOT MATCHED THEN         INSERT(IPAddress, LastFailedLogin, CounterResetDate)         VALUES(s.IPAddress, s.LogDate, s.CounterResetDate);     /*     Updates a client if it needs to be blocked and outputs to the @FirewallRules     table variable that the SP can return to signal a firewall needs to be created.     */     UPDATE ClientStatus     SET Blocked = 1, CounterResetDate = NULL     OUTPUT @FirewallGroup, @FirewallGroup + ' - ' + INSERTED.IPAddress     INTO @FirewallRules(FirewallGroup, FirewallRule)     WHERE IPAddress = @IPAddress       AND Blocked = 0       AND FailedLogins >= @FailedLoginThreshold       AND NOT EXISTS (SELECT * FROM WhiteList WHERE WhiteList.IPAddress = ClientStatus.IPAddress);      INSERT INTO ClientStatusDtl(IPAddress, LogDate, UserID, Message)     VALUES(@IPAddress, @LogDate, @UserID, @Message);      -- Log when whitelisted client is ignored.     INSERT INTO EventLog(IPAddress, Action, EventDesc)     SELECT IPAddress,         'Ignored',         'Ignoring client ' + IPAddress + ' after ' + CONVERT(varchar(10), FailedLogins)         + ' failed login attempt' + CASE WHEN FailedLogins > 1 THEN 's' ELSE '' END + '.          Client is whitelisted.'     FROM ClientStatus c     WHERE IPAddress = @IPAddress         AND EXISTS (SELECT * FROM WhiteList w WHERE w.IPAddress = c.IPAddress)         AND FailedLogins >= @FailedLoginThreshold;     -- Return firewall group/rule to add to firewall     SELECT FirewallGroup, FirewallRule     FROM @FirewallRules; END

To unblock clients and reset those not yet blocked, another scheduled task is run every 15 seconds and executes the following function.

function Clear-BlockedClients {     $ConnectionString = Get-DBConnectionString      $Connection = New-Object System.Data.SQLClient.SQLConnection     $Command = New-Object System.Data.SQLClient.SQLCommand      try     {         $Connection.ConnectionString = $ConnectionString         $Connection.Open()         $Command.Connection = $Connection         $Command.CommandText = 'EXEC dbo.ResetClients'              # ResetClients deletes records in ClientStatus that need to be unblocked         # or counters reset. Returns a result set of firewall rules to delete.         $Reader = $Command.ExecuteReader([System.Data.CommandBehavior]::SingleResult)         try         {             while($Reader.Read())             {                 $FirewallRule = $Reader.GetString(0)                 Remove-NetFirewallRule -Name $FirewallRule             }         }         finally         {             $Reader.Close()             $Reader.Dispose()         }     }     finally     {         $Command.Dispose()         $Connection.Close()         $Connection.Dispose()     } }

Most of the logic is handled in the ResetClients stored procedure, which first makes the changes to ClientStatus table, then returns a result set of firewall rules for the script to iterate through and remove by name.

CREATE PROCEDURE ResetClients AS BEGIN     SET NOCOUNT ON;     DECLARE @DeletedClients TABLE     (       IPAddress VARCHAR(100),         Action VARCHAR(20),         EventDesc VARCHAR(512),         FirewallRule VARCHAR(255),         LogDate DATETIME     );     DELETE FROM ClientStatus     OUTPUT DELETED.IPAddress,       CASE           WHEN DELETED.FirewallRule IS NULL THEN 'Reset Counter'             ELSE 'Unblock'         END,         CASE           WHEN DELETED.FirewallRule IS NULL THEN 'Failed login counter reset for client '             ELSE 'Unblocked client '         END + DELETED.IPAddress + '.',         DELETED.FirewallRule,         COALESCE(DELETED.LastFailedLogin, DELETED.CounterResetdate)     INTO @DeletedClients(IPAddress, Action, EventDesc, FirewallRule, LogDate)     WHERE (UnblockDate < GETDATE() AND FirewallRule IS NOT NULL) -- Clients to unblock       OR CounterResetDate < GETDATE(); -- Clients to reset counters on      INSERT INTO EventLog(IPAddress, Action, EventDesc)     SELECT IPAddress, Action, EventDesc     FROM @DeletedClients     ORDER BY LogDate;      DELETE FROM EventLog -- Purge EventLog if needed.     WHERE LogDate < (SELECT DATEADD(DAY, -ConfigValue, GETDATE())                                      FROM Config                                      WHERE ConfigID = 5                                      AND ConfigValue > 0)      SELECT FirewallRule -- Return list of firewall rules to delete.     FROM @DeletedClients     WHERE FirewallRule IS NOT NULL; END

Managing the Block List with T-SQL

In part 2, I added code to log the user ID of login attempts. This allowed manually deleting firewall rules by user ID which could be included as part of an application password reset API. We can provide the same semantics with views and an INSTEAD OF trigger. The views are first defined on our based tables:

CREATE VIEW BlockedClient AS     SELECT IPAddress,         LastFailedLogin,         UnblockDate,         CounterResetDate,         FailedLogins,         FirewallRule     FROM ClientStatus     WHERE Blocked = 1 GO  CREATE VIEW BlockedClientDtl AS     SELECT * FROM ClientStatusDtl GO

Next, we create an INSTEAD OF trigger on BlockedClient to flag the corresponding record in ClientStatus for removal the next time our scheduled task runs and clears out firewall rules.

CREATE TRIGGER trg_BlockedClient_D     ON BlockedClient     INSTEAD OF DELETE AS BEGIN   UPDATE ClientStatus   SET UnblockDate = GETDATE(), Blocked = 0   WHERE EXISTS (SELECT * FROM DELETED WHERE DELETED.IPAddress = ClientStatus.IPAddress); END

Similar to the previous implementation, an application can run the following code to unblock a user account after they’ve completed a password reset:

CREATE PROCEDURE UnblockUser(@UserID VARCHAR(128)) AS BEGIN     SET NOCOUNT ON;     DELETE FROM BlockedClient     WHERE IPAddress IN (SELECT IPAddress                         FROM BlockedClientDtl                         WHERE UserID = @UserID); END GO

We can go a step further and create a security role that provides the minimum permissions to execute the above code so a dedicated user account for password resets and unblocks can be used. Since a lot of the code in the LoginMonitor database is intended to be run only by the scheduled tasks, it’s important to keep permissions clamped down. Deleting a record from ClientStatus, for example, will leave an orphaned firewall rule.

CREATE ROLE [UnblockUsers] GRANT EXECUTE ON [UnblockUser] TO [UnblockUsers] GRANT SELECT ON [BlockedClientDtl] TO [UnblockUsers] GRANT DELETE ON [BlockedClient] TO [UnblockUsers] GRANT SELECT ON [BlockedClient] TO [UnblockUsers]  ALTER ROLE [UnblockUsers] ADD MEMBER [<password reset user>]

Points of Interest

The code has a few other enhancements that can be customized as needed and add heuristic-like functionality to determine how long to block a client IP (or permanently block if desired). The calculation of ClientStatus.UnblockDate is encapsulated in a UDF and I’ve created one additional parameter in the Config table that penalizes repeat offenders.

CREATE FUNCTION [dbo].[GetUnblockDate] (     @IPAddress VARCHAR(100),     @LastFailedLogin DATETIME ) RETURNS DATETIME AS BEGIN   DECLARE @UnblockDate DATETIME;     DECLARE @BlockHours INT;     DECLARE @BlockCnt INT;     DECLARE @RepeatBlockPenaltyHours INT;      SELECT @BlockHours = ConfigValue     FROM Config     WHERE ConfigID = 3;      IF @BlockHours > 0 -- If a parameter for block hours has been set calculate unblock date,     BEGIN              -- otherwise ignore and return null (block permanently).         SELECT @BlockCnt = Blocks         FROM ClientStatistics         WHERE IPAddress = @IPAddress;          -- Get hours per block penalty for repeat offenders if we want to extend the block time         SELECT @RepeatBlockPenaltyHours = CASE WHEN ConfigValue < 0 THEN 0 ELSE ConfigValue END         FROM Config         WHERE ConfigID = 4;         /*         Calculate total block hours. Consider adding other logic based on calculations from EventLog         such as number of failed login attempts per unit time. Some brute force software will attempt         hundreds of logins per minute which could be calculated          using lead/lag functions and perhaps used         to apply longer or permanent blocks (set @UnblockDate = null).         */         SET @BlockHours = @BlockHours + @BlockCnt * @RepeatBlockPenaltyHours;          SET @UnblockDate = DATEADD(hour, @BlockHours, @LastFailedLogin);     END      RETURN @UnblockDate; END

Another minor change is to the selection of events to block. The previous implementation provided filtering of 18456 by state code (to ignore things like password expirations), however this information doesn’t flow through to the Windows event logs and we only have the message text to go off of. To maintain this functionality I’ve added another table ConfigMsgFilter with some preloaded messages to ignore.

While testing the code, I noticed that some malicious clients still managed to get 20-30 login attempts in despite the 3-login theshold. This happens because the processing time of the scheduler task appears to take anywhere from 1-4 seconds and the IP was generating several attempts per second. Overall, this seems to be a non-issue, but I would be interested in any tips to speed up the execution of the task (the script itself seems to run much faster, the extra time appears to be overhead in Task Scheduler). The OnFailedLogin task XML has the thread priority set to 4 (highest priority for a background task, default is 7) which seems to improve the execution time by a second or two.

Contributing to the Code

The source code is hosted on Github and I encourage anyone to contribute, report bugs or submit feature requests. I’m somewhat of a novice with PowerShell and any help refactoring code or improving functionality is welcomed.


  • Test with older versions of Powershell
  • Test with SQL Server login (non-trusted connection)
  • Create version of script for SQL Server 2005 (need to check support for indexed views and remove MERGE)
  • Improve installer to prompt for connection string parameters saved in config.xml


  • 4/23/2018 – Initial release tested on SQL Server 2017 Enterprise running on Windows Server 2016 Datacenter
  • 4/30/2018 – Added support for IPSec-based block rules if Windows firewall is turned off (e.g. when using 3rd-party firewall provided by AV software); updated download link to point to latest release on Github