Brute force attack on SQL Server

It's time to get outdoors and Explore

Brute force attack on SQL Server 

If your business needs the SQL Server to be accessible on public network, you may be very vulnerable for brute force attacks.

Following query will help you identify the failed login attempts and you can further understand where the attacks coming from by inspecting IP Addresses.

EXEC sys.sp_readerrorlog @p1 = 0, -- int -- 0 = current, 1 = Archive #1, 2 = Archive
@p2 = 1, -- int -- 1 or NULL = error log, 2 = SQL Agent log
@p3 = 'failed', -- varchar(255) -- String one we want to search for
@p4 = 'login' -- varchar(255) -- String two we want to search to further refine the results
2017-06-22 08:01:40.070 Logon Login failed for user 'mssqla'. Reason: Could not find a login matching the name provided. [CLIENT: 221.195.76.237]
2017-06-22 08:01:41.570 Logon Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: 221.195.76.237]
2017-06-22 08:01:43.100 Logon Login failed for user 'mssqla'. Reason: Could not find a login matching the name provided. [CLIENT: 221.195.76.237]
2017-06-22 08:01:44.660 Logon Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: 221.195.76.237]
2017-06-22 08:01:46.190 Logon Login failed for user 'mssqla'. Reason: Could not find a login matching the name provided. [CLIENT: 221.195.76.237]
2017-06-22 08:02:08.610 Logon Login failed for user 'hbv7'. Reason: Could not find a login matching the name provided. [CLIENT: 221.195.76.237]
2017-06-22 08:02:09.930 Logon Login failed for user 'bwsa'. Reason: Could not find a login matching the name provided. [CLIENT: 221.195.76.237]
2017-06-22 08:02:11.220 Logon Login failed for user 'ps'. Reason: Could not find a login matching the name provided. [CLIENT: 221.195.76.237]
2017-06-22 08:02:12.520 Logon Login failed for user 'uep'. Reason: Could not find a login matching the name provided. [CLIENT: 221.195.76.237]

You could take the following steps to Mitigate the attack.

  1. Evaluate whether you really need your SQL Server to be accessible in public.If your  business absolutely needs it  , think about whitelisting the IP's and granting access to only those IP's.
  2. Disable Accounts like "sa" .
  3. Disable SQL Server Browser, makes it harder to identify the SQL Service
  4. Perform a complete and through analysis of users, permissions, and passwords on each databases
  5. Provision least privileged accounts
  6. Implement IPS and IDS Layer and put the SQL Server behind IPS / IDS. Implementing IPS and IDS could be harder and might incur additional latency but its worth evaluating the option.
  7. Have explicit firewall rules

The best safeguard is to make the SQL Server accessible in private network and deny the public access.

Queries to Investigate

DROP TABLE IF EXISTS #temptable;
CREATE TABLE #temptable
(
    [LogDate] DATETIME,
    [ProcessInfo] NVARCHAR(12),
    [Text] NVARCHAR(3999)
);

INSERT INTO #temptable
(
    [LogDate],
    [ProcessInfo],
    [Text]
)
EXEC sys.sp_readerrorlog @p1 = 0,        -- int -- 0 = current, 1 = Archive #1, 2 = Archive
                         @p2 = 1,        -- int -- 1 or NULL = error log, 2 = SQL Agent log
                         @p3 = 'failed', -- varchar(255) -- String one we want to search for
                         @p4 = 'login';  -- varchar(255) -- String two we want to search to further refine the result

SELECT REVERSE(SUBSTRING(REVERSE(Text), 2, 13)) AS [IPAddress],
       COUNT(*) AS FailedAttempts
FROM #temptable
WHERE Text LIKE 'Login failed for user%'
GROUP BY REVERSE(SUBSTRING(REVERSE(Text), 2, 13));

Failed_Login_Attempts.PNG

Posted in Microsoft Technologies, SQL Server on Jun 22, 2017


Comments

Please sign in to comment!