How to Prevent Brute-force Login Attacks on MSSQL
Introduction
If you are running Microsoft SQL Server, you may be aware that your SA account is subject to a brute force attack. Nearly every SQL server connected to the Internet is under constant attack. Once a hacker is able to gain access to a SA (DBA) account, or even a normal user account, it can gain full access to the file system on the server and even the files on the network it is connected to.
By detecting logins that fail and block their IP address for some time, you will most likely have them move on to attack another server instead. We can create firewall rules that stop the attackers after a few attempts.
3 Steps to Prevent Brute-force Attacks on MSSQL on Windows
Step 1. Open Login Auditing
fiber_manual_record Enable auditing of service login authentication so that the SQL log will record relevant login information.
fiber_manual_record Configure Log files size and count
Step 2. Analyze log files
Microsoft SQL Server logs failed login attempts in SQL Server Logs, which practically is the ERRORLOG file in your SQL Server Log directory. An failed login attempt is for example:
2021-09-16 00:21:04.95 Logon Error: 18456, Severity: 14, State: 8. 2021-09-16 00:21:04.95 Logon Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: 213.252.0.12]
Step 3. Add abnormal IP to firewall
Add the analyzed dangerous IP source addresses, such as 1.1.1.1 and 1.1.1.2, to the local firewall.
In order to protect your SQL Server from these brute force attacks, you need to block this IP address. “OK, that’s easy”, you might think. But what if there are thousands of log lines? Let’s use PowerShell to automate parsing this log and filtering IP’s to block.
In a nutshell, you’re going to use PowerShell to:
fiber_manual_record parse SQL Server ERRORLOG log file
fiber_manual_record get all IP addresses responsible for failed login attempts
fiber_manual_record filter out your own IP addresses (you don’t want to lock yourself out)
fiber_manual_record add those IP’s to the Windows Defender Firewall with Advanced Security. If it’s not listed yet, that is.
Note: We will develop a small tool for our customers to use, please pay attention to updates here.
How to Prevent Brute Force Login Attacks on SQL Server on Linux
The method to prevent brute force login attacks on SQL Server on Linux systems is similar to that on Windows. Moreover, Linux is more convenient because there is a powerful and free fail2ban tool available.
There is a script on Github（https://github.com/ToulisDev/fail2ban-action-mssql） to add banned ips from fail2ban to your MS SQL server using ban-action from fail2ban. (Tested on Ubuntu Server)
For more information about the use of Fail2ban, you can visit How to Prevent SSH Brute-Force Attacks on Linux Using Fail2ban.
