Click here to Skip to main content
13,592,857 members
Click here to Skip to main content
Add your own
alternative version

Stats

4.7K views
30 downloads
4 bookmarked
Posted 23 Apr 2018
Licenced CPOL

SQL Server Brute Force Attack Detection: Part 4

, 30 Apr 2018
Rate this:
Please Sign up or sign in to vote.
Using Powershell and Windows Task Scheduler API to detect malicious login attempts on a remotely accessible SQL Server database

Parts in the Series

Introduction

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.

Background

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.

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.

PowerShell

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: 218.64.216.85]</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.

To-Do

  • 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

History

  • 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

References

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ryan G Conrad
Database Developer
United States United States
I am a database developer working for a SaaS company providing health benefits management software. I have about 8 years of experience working in several SQL database platforms including Microsoft SQL Server, IBM DB2, MySQL, Postgresql, SQLite and possibly others. I particularly enjoy database and query performance tuning.

You may also be interested in...

Pro

Comments and Discussions

 
-- There are no messages in this forum --
Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01-2016 | 2.8.180618.1 | Last Updated 30 Apr 2018
Article Copyright 2018 by Ryan G Conrad
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid