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><QueryList><Query Id="0"
Path="Application"><Select Path="Application">*
[System[(Level=2 or Level=4 or Level=0) and (EventID=17828 or
EventID=17832 or EventID=17836 or EventID=18456)]]</Select>
</Query></QueryList></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>
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()
}
}
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
}
$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())
{
$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>'
RETURN;
IF NOT EXISTS (SELECT * FROM ConfigEvent
WHERE EventID = @EventID
AND Block = 1)
BEGIN
RETURN;
END
IF EXISTS (SELECT * FROM ConfigMsgFilter
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);
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);
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;
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'
$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)
OR CounterResetDate < GETDATE();
INSERT INTO EventLog(IPAddress, Action, EventDesc)
SELECT IPAddress, Action, EventDesc
FROM @DeletedClients
ORDER BY LogDate;
DELETE FROM EventLog
WHERE LogDate < (SELECT DATEADD(DAY, -ConfigValue, GETDATE())
FROM Config
WHERE ConfigID = 5
AND ConfigValue > 0)
SELECT FirewallRule
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
BEGIN
SELECT @BlockCnt = Blocks
FROM ClientStatistics
WHERE IPAddress = @IPAddress;
SELECT @RepeatBlockPenaltyHours = CASE WHEN ConfigValue < 0 THEN 0 ELSE ConfigValue END
FROM Config
WHERE ConfigID = 4;
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
