Parts in the Series
Introduction
Shortly after publishing the first part of this series, I stumbled across additional information on how to hack a SQL Server database as well as different ways to read the event logs. There are also other events besides event ID 18456 that a DBA might want the detection code to consider that can indicate malicious port scans on our externally accessible database.
Background
When reading more about the different kinds of events SQL Server logs, I discovered that event IDs 17832 and 17836 can also be indicative of malicious activity that we may want to block as part of our brute-force attack detection. In fact, these errors are commonly logged when the database receives packets from common port scanning tools as they enumerate a list of ports and wait for responses. I don't know about other sys admins, but in my experience, any port scan on an internet-facing service from a public IP address should immediately be blocked unless part of a known pen test.
I use pfSense as my firewall on my home network (which I highly recommend because of its high level of community support and add-ons) and use the pfBlockerNG add-on package to manage several IPv4 block lists that are freely available from a number of providers. In my testing of BruteForceAttackDetection, I have inbound connections to SQL Server bypassing all of the malware block lists I'm subscribed to, so I'm getting a lot of malicious login attempts. Sure enough, I've seen many event log entries with 17832 and 17836 event IDs; sometimes a given IP address doesn't attempt a log-in attempt at all and just sends bad packets over and over again. I'm no expert on port-scanning tools; I've used NMap and Wireshark a handful of times for basic port scanning and packet sniffing so I can't say for certain what all these bad packets mean. Nonetheless I don't want them hitting my network so the latest revisions to the code have configs to optionally block them.
Of course, all of this is only possible after extracting information on the event ID from the SQL Server event logs. I discovered that SQL Server has a somewhat strange way of querying some of the pieces of the event logs with xp_readerrorlog (which at their core are actually XML). In the original code, we were only getting the event description, which gives us the IP address. Apparently, each log entry shows up as two lines in xp_readerrorlog, the first containing the event ID, state and severity. This requires a self-join of the temp table the event data is dumped into on the event timestamp to piece it all together. The additional pieces of data don't mean a whole lot as far as we're concerned, but the event ID and state give us a consistent structure to map out additional configs and provide the ability to fine tune which events are considered indicative of malicious activity and should trigger a block of the offending remote IP address.
Using the Code
First, we need to add a new table ConfigEvent and populate it with some configurations of the three event IDs we want to optionally block. For 18456, I populated the different state codes that break down failed login attempts into a few different types. For 17832 and 17836, I couldn't find a definitive list of all of the different state codes this event will throw so I just set up the code to block all of them or nothing.
CREATE TABLE ConfigEvent
(
ErrorCode INT NOT NULL,
StateCode INT NOT NULL,
Block BIT NOT NULL DEFAULT 1,
Description VARCHAR(4000),
PRIMARY KEY(ErrorCode, StateCode)
);
INSERT ConfigEvent(ErrorCode, StateCode, Block, Description)
VALUES (17832, -1, 1, N'The login packet used to open the connection is structurally invalid; _
the connection has been closed. Please contact the vendor of the client library.%.*ls')
INSERT ConfigEvent(ErrorCode, StateCode, Block, Description)
VALUES (17836, -1, 1, N'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.%.*ls')
INSERT ConfigEvent(ErrorCode, StateCode, Block, Description)
VALUES (18456, 1, 1, N'Error information is not available. _
This state usually means you do not have permission to receive the error details. _
Contact your SQL Server administrator for more information.')
INSERT ConfigEvent(ErrorCode, StateCode, Block, Description)
VALUES (18456, 2, 1, N'User ID is not valid.')
INSERT ConfigEvent(ErrorCode, StateCode, Block, Description)
VALUES (18456, 5, 1, N'User ID is not valid.')
INSERT ConfigEvent(ErrorCode, StateCode, Block, Description)
VALUES (18456, 6, 1, N'An attempt was made to use a Windows login name with SQL Server Authentication.')
INSERT ConfigEvent(ErrorCode, StateCode, Block, Description)
VALUES (18456, 7, 1, N'Login is disabled, and the password is incorrect.')
INSERT ConfigEvent(ErrorCode, StateCode, Block, Description)
VALUES (18456, 8, 1, N'The password is incorrect.')
INSERT ConfigEvent(ErrorCode, StateCode, Block, Description)
VALUES (18456, 9, 1, N'Password is not valid.')
INSERT ConfigEvent(ErrorCode, StateCode, Block, Description)
VALUES (18456, 11, 1, N'Login is valid, but server access failed. _
One possible cause of this error is when the Windows user has access to SQL Server _
as a member of the local administrators group, but Windows is not providing _
administrator credentials. To connect, start the connecting program using the _
Run as administrator option, and then add the Windows user to SQL Server as a specific login.')
INSERT ConfigEvent(ErrorCode, StateCode, Block, Description)
VALUES (18456, 12, 1, N'Login is valid login, but server access failed.')
INSERT ConfigEvent(ErrorCode, StateCode, Block, Description)
VALUES (18456, 18, 1, N'Password must be changed.')
INSERT ConfigEvent(ErrorCode, StateCode, Block, Description)
VALUES (18456, 58, 1, N'An attempt to login using SQL authentication failed. _
Server is configured for Integrated authentication only. ')
The revised code for querying the event log data is given below (see the attached script for the entire revised code in the CheckFailedLogins stored procedure); as mentioned above, this requires a self-join to tack the event ID, state and severity data to the descripition and get the entire log entry. I've also moved the code for extracting the needed data into scalar functions, mostly to keep the code clean and factored down.
WITH Logs AS
(
SELECT e.LogDate,
CASE
WHEN e.Message LIKE 'Error: 18456%' THEN 18456
WHEN e.Message LIKE 'Error: 17836%' THEN 17836
WHEN e.Message LIKE 'Error: 17832%' THEN 17832
END AS ErrorCode,
dbo.ExtractStateCode(e.Message) AS StateCode,
dbo.ExtractIPAddress(x.Message) as IPAddress,
dbo.ExtractUserID(x.Message) AS UserId,
e.Message + CHAR(13) + CHAR(10) + x.Message as Message
FROM @FailedLogins AS e
INNER JOIN @FailedLogins AS x
ON e.LogDate = x.LogDate
WHERE (e.Message LIKE 'Error: 18456%' AND x.Message LIKE 'Login%')
OR (e.Message LIKE 'Error: 17836%' AND x.Message LIKE 'Length%')
OR (e.Message LIKE 'Error: 17832%' AND x.Message LIKE 'The login%')
)
INSERT INTO @FailedLoginClientDtl
SELECT l.IPAddress,
l.LogDate,
l.UserId,
l.Message
FROM Logs l
INNER JOIN ConfigEvent e
ON e.ErrorCode = l.ErrorCode
AND (e.StateCode = l.StateCode OR e.StateCode = -1)
AND e.Block = 1
WHERE IPAddress IS NOT NULL
AND LogDate >= @LookbackDate
Points of Interest
One thing I noticed with the self-join of the event log data is that the timestamp isn't unique for events (at least not at the level of accuracy that xp_readerrorlog extracts it as, which appears to be datetime and not datetime2). The leads to some duplicating of records with identical timestamps, but from testing this appears to be mostly inconsequential. For the most part, this would duplicate an entry or two from a single IP address with 40-50+ failed login attempts in the 10 second time window I have the SQL Server Agent job running that calls CheckFailedLogins (obviously malicious!).
References
