SQL Server 暴力攻击检测：第 2 部分
4.07/5 (9投票s)
附加日志记录和数据收集
系列文章
引言
在本系列的第二部分中，我想介绍一些我一直在测试的附加功能，以使代码对开发人员更有用。据我所知，这些功能也适用于 SQL Server 2005 及更高版本的所有版本。
背景
最初的代码一直在我的家庭数据库服务器上运行，并定期阻止攻击 IP。 同时，我更深入地思考了可以使代码对开发人员更有用的附加功能。
我想到的一个用例是客户端或移动应用程序连接到 SQL Server 数据库，用户拥有自己的用户名和密码。 在用户意外多次输入错误密码的情况下，我们需要一种简单的方法在防火墙中解除阻止它们。 通常，用户身份验证 UI 包含密码重置功能，因此我们需要一种简单的方法来附加一些简单的代码以自动解除阻止它们。
此外，我认为记录阻止/解除阻止事件也很好，这样我就可以按 IP 获取统计信息，并查看是否有重复的违规者。
Using the Code
我添加了两个表来捕获有关失败登录尝试的附加数据。 BlockedClientDtl 捕获每次失败尝试使用的用户 ID，EventLog 记录由 CheckFailedLogins 存储过程完成的阻止/解除阻止操作。 我还修改了 BlockedClient 以记录特定的解除阻止日期时间值，以防我们要修改此值的计算方式（例如，按客户端 IP，可能延长重复违规者的阻止时间）。
CREATE TABLE BlockedClient
(
IPAddress VARCHAR(15) NOT NULL PRIMARY KEY,
LastFailedLogin DATETIME,
UnblockDate DATETIME,
FailedLogins INT,
FirewallRule VARCHAR(255)
);
CREATE INDEX IX_BlockedClient_UnblockDate ON BlockedClient(UnblockDate);
CREATE TABLE BlockedClientDtl
(
IPAddress VARCHAR(15) NOT NULL,
Attempt INT NOT NULL,
LogDate DATETIME,
UserId VARCHAR(255),
Message VARCHAR(512),
PRIMARY KEY(IPAddress, Attempt),
FOREIGN KEY(IPAddress) REFERENCES BlockedClient ON DELETE CASCADE
);
CREATE INDEX IX_BlockedClientDtl_UserId_LogDate ON BlockedClientDtl(UserId, LogDate);
CREATE TABLE EventLog
(
LogId BIGINT NOT NULL PRIMARY KEY IDENTITY,
LogDate DATETIME DEFAULT GETDATE(),
IPAddress VARCHAR(15),
Action VARCHAR(20),
EventDesc VARCHAR(512)
);
CREATE INDEX IX_EventLog_IP_LogDate ON EventLog(IPAddress, LogDate);
BlockedClientDtl 的想法是能够在用户请求密码重置时运行如下查询
DELETE FROM BlockedClient
WHERE EXISTS (SELECT * FROM BlockedClientDtl
WHERE BlockedClientDtl.IPAddress = BlockedClient.IPAddress
AND BlockedClientDtl.UserId = 'JDOE')
当用户 JDOE 完成密码重置时，我们可以删除与用户 ID 关联的 BlockedClient 中的所有条目（并且可以选择仅删除基于 BlockedClientDtl.LogDate 的一段时间内的条目）。
最后，是我们 CheckFailedLogins SP 的修订代码
CREATE PROCEDURE CheckFailedLogins
AS
BEGIN
SET NOCOUNT ON;
DECLARE @UnblockDate DATETIME
DECLARE @LookbackDate DATETIME
DECLARE @MaxFailedLogins INT
DECLARE @FailedLogins TABLE
(
LogDate datetime,
ProcessInfo varchar(50),
Message text
);
DECLARE @FailedLoginClientDtl TABLE
(
IPAddress VARCHAR(15),
LogDate DATETIME,
UserID VARCHAR(128),
Message VARCHAR(1000)
);
SELECT @LookbackDate = dateadd(second, -ConfigValue, getdate())
FROM Config
WHERE ConfigID = 1
SELECT @MaxFailedLogins = ConfigValue
FROM Config
WHERE ConfigID = 2
SELECT @UnblockDate = CASE WHEN ConfigValue > 0 THEN DATEADD(hour, ConfigValue, getdate()) END
FROM Config
WHERE ConfigID = 3
INSERT INTO @FailedLogins -- Read current log
exec sp_readerrorlog 0, 1, 'Login failed';
INSERT INTO @FailedLoginClientDtl
SELECT ltrim(rtrim(substring(CONVERT(varchar(1000), Message),
charindex('[CLIENT: ', CONVERT(varchar(1000), Message)) + 9,
charindex(']', CONVERT(varchar(1000), Message)) - 9 -
charindex('[CLIENT: ', CONVERT(varchar(1000), Message))))) as IPAddress,
LogDate,
CASE WHEN charindex('Login failed for user ''', CONVERT(varchar(1000), Message)) > 0 THEN
ltrim(rtrim(substring(CONVERT(varchar(1000), Message),
charindex('Login failed for user ''', CONVERT(varchar(1000), Message)) + 23,
charindex('''. Reason:', CONVERT(varchar(1000), Message)) - 23 -
charindex('Login failed for user ''', CONVERT(varchar(1000), Message))))) END AS UserId,
Message
FROM @FailedLogins
WHERE (Message like '%Reason: An error occurred while _
evaluating the password.%' -- Some filter criteria
OR Message like '%Reason: Could not find a login matching the name provided.%'
OR Message like '%Reason: Password did not match that for the login provided.%'
OR Message LIKE '%Login failed. The login is from an untrusted domain _
and cannot be used with Windows authentication.%')
AND LogDate >= @LookbackDate
INSERT INTO BlockedClient(IPAddress, LastFailedLogin, UnblockDate, FailedLogins)
OUTPUT INSERTED.IPAddress, 'Block', 'Blocked client ' + INSERTED.IPAddress + _
' after ' + CONVERT(VARCHAR(10), INSERTED.FailedLogins) + ' failed login attempts.'
INTO EventLog(IPAddress, Action, EventDesc) -- Record block event
SELECT IPAddress,
MAX(LogDate) AS LastFailedLogin,
@UnblockDate,
COUNT(*) AS FailedLogins
FROM @FailedLoginClientDtl d
WHERE NOT EXISTS (SELECT * FROM Whitelist l -- Check against whitelist
WHERE l.IPAddress = d.IPAddress)
AND NOT EXISTS (SELECT * FROM BlockedClient c -- ignore already blocked clients
WHERE c.IPAddress = d.IPAddress)
AND IPAddress <> '<local machine>' -- ignore failed logins from local machine
GROUP BY IPAddress
HAVING COUNT(*) >= @MaxFailedLogins -- Check against number of failed logins config
INSERT INTO BlockedClientDtl(IPAddress, Attempt, LogDate, UserId, Message)
SELECT IPAddress,
Attempt,
LogDate,
UserID,
Message
FROM
(
SELECT IPAddress,
ROW_NUMBER()OVER(PARTITION BY IPAddress ORDER BY LogDate) AS Attempt,
LogDate,
UserID,
Message
FROM @FailedLoginClientDtl d
WHERE EXISTS (SELECT * FROM BlockedClient c
WHERE c.IPAddress = d.IPAddress)
)AS t
WHERE NOT EXISTS (SELECT * FROM BlockedClientDtl dtl
WHERE t.IPAddress = dtl.IPAddress
AND t.Attempt = dtl.Attempt)
DELETE FROM BlockedClient -- Delete entries older than the delete config set if > 0
OUTPUT DELETED.IPAddress, 'Unblock', 'Unblocked client ' + DELETED.IPAddress + '.'
INTO EventLog(IPAddress, Action, EventDesc) -- Record unblock event
WHERE UnblockDate < getdate()
END
对于 BlockedClient 上的插入和删除操作，我使用了使用 OUTPUT 子句时可用的 INSERTED 和 DELETED 特殊表，将记录插入到 EventLog 表中，以便我可以看到阻止/解除阻止事件的历史记录。
进一步改进
我们可以通过观察 EventLog 中的模式来发挥客户端特定阻止参数的创造力。 也许每次 IP 被阻止时，都会逐步增加该 IP 的阻止时间。 无论如何，如果我们想更深入地挖掘或排除意外行为，那么了解这些事件的可见性是很有用的。
不幸的是，我们已经几乎达到了可以从 sp_readerrorlog 获取的信息的限制。 在本系列的第三部分中，我将介绍一个完全不同的实现，该实现使用仅在标准版和企业版中可用的服务代理。 通过这种方法，我们将能够收集更多关于登录事件的信息，并直接在这些事件上触发客户端阻止。 话虽如此，我想首先介绍所有可以在 SQL Server Express 版本中使用的功能，以便更多的人可以利用它们，并希望能够立即改善其数据库的安全性。
最终版本可在此处获取。