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.
8. Have explicit firewall rules
The best safeguard is to make the SQL Server accessible in private network and deny the public access.