SQL Server 暴力攻击检测：第 4 部分

• 下载源代码 - 57.1 KB

系列文章

• 第 1 部分 - 初始设置
• 第 2 部分 - 附加日志记录和数据收集
• 第 3 部分 - 更多配置和审计工具
• 第四部分 - 使用 PowerShell 和 Windows 任务计划程序 API

引言

在本系列的最后一部分，我们将介绍一种利用 PowerShell 和 Windows 任务计划程序 API 的登录监视器替代实现方法。这种方法更安全、更有效地保护数据库服务器免受恶意登录尝试的攻击，同时继续使用 Windows 和 T-SQL 的内置功能来管理防火墙规则。

背景

本系列文章的初稿介绍了一种完全在 T-SQL 中实现针对暴力登录攻击防护的方法。虽然实现简单，但它通过将 SQL Server 服务帐户设为本地管理员（这是通过 xp_cmdshell 和 netsh 与 Windows 防火墙交互的要求）引入了安全漏洞。这存在问题，因为它允许 sysadmin 服务器角色中的任何用户帐户在提升的安全上下文中运行 shell 命令，极大地增加了服务器的攻击面，一旦数据库被泄露（我承认我没有完全理解 SQL Server 如何执行 xp_cmdshell）。此外，还有许多理由阻止拥有数据库 sysadmin 级别访问权限的常规用户拥有操作系统管理员访问权限。

Windows 任务计划程序碰巧拥有丰富的 API，它允许基于事件日志中的特定事件触发任何程序，并将事件数据作为参数传递。在此实现中，我已将检测登录失败和创建防火墙规则的所有代码都整合到 PowerShell 脚本中。这使我们能够将与防火墙交互所需的提升安全上下文隔离到任务计划程序内部，并消除对 T-SQL 中使用的扩展存储过程的依赖。该实现还继续提供通过 T-SQL 解锁客户端的功能，例如通过需要解锁客户端 IP 的第三方代码使用的密码重置 API。

Using the Code

Windows 任务计划程序 API

坦白说，这是我第一次接触任务计划程序中的事件驱动触发器。任务计划程序提供与事件查看器中创建自定义视图的相同 UI 来创建自定义事件过滤器。这非常方便，因为查询语法可能很麻烦。

UI 未提供的功能（但有 很好的文档记录）是能够通过定义任务的 XML 中的属性来引用事件数据。在设置了由事件 ID 17828、17832、17836 和 18456 触发的任务后，我导出它，然后编辑生成的 XML 文件。关键的更改是添加了 <ValueQueries> 和 <Value name=> 节点，这些节点指定了 XPath 查询，用于从事件数据 XML 中提取数据，然后将数据分配给可以传递给 PowerShell 脚本的命名变量。

  <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 "& {. .\LoginMonitor.ps1; On-FailedLogin 
      '$(EventID)' '$(EventData)'}"</Arguments>
      <WorkingDirectory>%ProgramData%\SQL Server Login Monitor\</WorkingDirectory>
    </Exec>
  </Actions>
</Task>

起初，我对语法有些挣扎。显然，API 只支持 XPath1.0 的一个有限版本，并且不允许按索引引用未命名的元素，特别是 <EventData><data> 标记（我也不是 XML 解析的最佳手）。经过一些测试，我能够将事件数据提取为逗号分隔的 string，然后在 PowerShell 中可以轻松地将其分割成数组。这以及事件 ID 是我们检查登录失败尝试并根据前面部分涵盖的方法阻止相关 IP 地址所需的一切。

PowerShell

PowerShell 代码包含在一个名为 LoginMonitor.ps1 的单个脚本文件中，其中包含由每个任务调用的两个函数。编写此脚本涉及手动检查每个四个事件中包含的事件数据的相当多的工作。下面是 18456 登录失败事件的数据元素示例

<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 在 <EventData> 标记中包含所有事件数据，但省略了 xp_readerrorlog 中提供的一些详细信息。客户端的 IP 地址是一致的，并且包含在 [CLIENT:] 中（与从 xp_readerrorlog 获取的日志条目相同）。

Extract-EventData 函数解析事件数据

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
}

接下来，提取的字段将传递给 Log-FailedLogin 函数，该函数使用存储过程 LogFailedLogin 将事件数据记录到数据库，该过程确定客户端是否应被阻止。如果客户端被标记为阻止，SP 将返回防火墙规则的名称，从而指示 PowerShell 函数创建防火墙规则。

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()
    }
}

下面提供了 LogFailedLogin SP。一个关键的更改是修改了 Config 表中先前使用的“回溯时间”参数，该参数决定了要扫描事件日志以查找失败登录的时间范围。在此实现中，由于我们正在跟踪传入的失败登录尝试，因此我们需要一种方法来清除客户端，如果用户输入一次或两次错误密码，然后成功进行身份验证。否则，他们将来可能会输入第三次错误的密码（假设登录阈值为 3 次），然后意外地被阻止。我已经将此参数的默认值设置为 15 分钟，并且 ClientStatus 表包含一个名为 CounterResetDate 的列，用于确定在这种情况下何时删除记录。

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

为了解锁客户端并重置尚未被阻止的客户端，另一个计划任务每 15 秒运行一次并执行以下函数。

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()
    }
}

大部分逻辑由 ResetClients 存储过程处理，该过程首先对 ClientStatus 表进行更改，然后返回一个防火墙规则的结果集供脚本遍历并按名称删除。

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

使用 T-SQL 管理阻止列表

在 第二部分 中，我添加了代码来记录登录尝试的用户 ID。这允许按用户 ID 手动删除防火墙规则，这些规则可以作为应用程序密码重置 API 的一部分。我们可以通过视图和 INSTEAD OF 触发器提供相同的语义。首先在我们的基本表上定义视图

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

接下来，我们在 BlockedClient 上创建一个 INSTEAD OF 触发器，以在下次我们的计划任务运行时将相应的记录标记为从 ClientStatus 中删除，并清除防火墙规则。

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

与以前的实现类似，应用程序可以运行以下代码，在用户完成密码重置后解锁用户帐户

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

我们可以更进一步，创建一个安全角色，提供执行上述代码所需的最低权限，以便可以使用专用用户帐户进行密码重置和解锁。由于 LoginMonitor 数据库中的大量代码仅打算由计划任务运行，因此保持权限受限非常重要。例如，从 ClientStatus 中删除一条记录将导致一个孤立的防火墙规则。

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>]

关注点

代码还有一些其他增强功能可以根据需要进行自定义，并添加启发式功能来确定阻止客户端 IP 的时长（或永久阻止，如果需要）。ClientStatus.UnblockDate 的计算封装在一个 UDF 中，我在 Config 表中创建了一个额外的参数，该参数会惩罚重复违规者。

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

另一个小改动是选择要阻止的事件。以前的实现通过状态码过滤 18456（以忽略密码过期等情况），然而这些信息不会流向 Windows 事件日志，我们只能通过消息文本来判断。为了保持此功能，我添加了另一个名为 ConfigMsgFilter 的表，其中包含一些预加载的消息以供忽略。

在测试代码时，我注意到尽管有 3 次登录阈值，但一些恶意客户端仍然进行了 20-30 次登录尝试。这是因为计划任务的处理时间似乎需要 1-4 秒，并且 IP 地址每秒生成多次尝试。总的来说，这似乎不是问题，但我对任何加快任务执行速度的技巧都很感兴趣（脚本本身似乎运行得更快，额外的时间似乎是任务计划程序中的开销）。OnFailedLogin 任务 XML 将线程优先级设置为 4（后台任务的最高优先级，默认值为 7），这似乎能将执行时间缩短一到两秒。

为代码做贡献

源代码 托管在 Github 上，我鼓励任何人贡献、报告错误或提交功能请求。我对 PowerShell 来说算是个新手，任何重构代码或改进功能的帮助都受到欢迎。

待办事项

• 使用旧版本 PowerShell 进行测试
• 使用 SQL Server 登录（非受信任连接）进行测试
• 为 SQL Server 2005 创建脚本版本（需要检查索引视图支持并删除 MERGE）
• 改进安装程序，提示输入保存在 config.xml 中的连接字符串参数

历史

• 2018/4/23 - 初始版本，在 SQL Server 2017 Enterprise 上运行于 Windows Server 2016 Datacenter 测试
• 2018/4/30 - 添加了对 IPSec 阻止规则的支持，以防 Windows 防火墙关闭（例如，当使用 AV 软件提供的第三方防火墙时）；更新了下载链接以指向 Github 上的最新版本

参考文献

• 从 Windows 事件触发 PowerShell 脚本
• 如何：在 Windows 事件上运行 PowerShell 脚本
• 通过 PowerShell 访问 SQL Server
• XPath 1.0