Protect SQL Server from hackers

Question

Today, my SQL Server 2008 is attacked by hacker from IP address in China [4300 Login failure in last 7 hours]. I contacted GoDaddy support and below is what they told me.

EXEC sp_readerrorlog 0, 1, 'Login failed' [and result 4300+ failed login and still increasing]

As for the SQL Server brute force attack against your server you would need to determine the best method for resolving that on the server. On some systems the best solution is to simply update the firewall on the server to block access to all connections to the port for SQL Server, 1433, so that only a specific IP can access that port, however the security of your server would be up to you and as such we would not be able to guide you on changing the scope of a windows firewall rule. You may wish to review Microsoft's knowledge base for information on using and modifying the windows firewall on your server.

How can I update the firewall on the server to block access to all connections to the port for SQL Server, 1433, so that only my own IP address can access the SQL Server?

Here is what i have done so far (Next day): I disabled the SA account, since all the attack was on SA username.

I tried to add a inbound rule in firewall for Port number 1433 to block anonymous IP address. I tried to add a inbound rule in firewall for Program(sqlserver.exe) to block anonymous IP address.

but no WIN yet. login failed attempts is continue to increasing. Can someone guide me how to block anonymous IP on the firewall to access the SQL Server.

Answer 1

This really isn't an SQL Server issue - it seems more of a firewall issue.

If you are in a situation where your SQL Servers are directly accessible from dudes in china - I'd be sorting out why no one in your outfit is handling networking/firewalls etc

It sounds quite possible that you need to be worried about more servers than just the one you've noticed getting tickled!

Answer 2

For a public facing SQL Server I recommend the 'sa' user should ideally either be disabled or renamed to something that would be very difficult to guess, for example 'sa_followed_by_a_long_randomly_generated_value'. Naturally the password for this account should also be long and complex to be very difficult to guess.

An important side effect worth mentioning here is that all invalid login attempts are logged - for me the attacker would keep guessing at a rate of at least 2 times per second, here is a snippet from the errorlog file:

2022-04-11 01:59:00.16 Logon       Error: 18456, Severity: 14, State: 5.
2022-04-11 01:59:00.16 Logon       Login failed for user 'sa'. Reason: Could not find a login matching the name provided. [CLIENT: 111.42.90.52]
2022-04-11 01:59:00.77 Logon       Error: 18456, Severity: 14, State: 5.
2022-04-11 01:59:00.77 Logon       Login failed for user 'sa'. Reason: Could not find a login matching the name provided. [CLIENT: 111.42.90.52]

The default SQL Server configuration is to retain all logs - this means eventually the disk where the errorlog is located will eventually fill up completely, which usually happens to be on the same disk as the OS. This happened to me after a few months of not monitoring my server's disk space. To this end I highly recommend looking into reducing the number of errorlog files SQL Server can use. I have it configured to be 6 and once those 6 errorlog files are full they will be re-used (overridden) capping the amount of disk space the attackers can fill up.

Lastly, whilst I realise this question was regarding SQL Server running on Windows - in case anyone running SQL Server on Ubuntu / Linux comes across this thread I'd like to mention that whilst I had capped the errorlogs I still wasn't thrilled to have my server constantly being annoyed by the continuous invalid login attempts even if they were never guessing with a correct username.

To solve this issue I created a script which I have set to run at the start of every hour (so it runs 24 times a day) - this script scans the current errorlog file and firewalls all IPv4 addresses that have invalid login attempts for the 'sa' user. When the address is blocked the request will no longer reach SQL Server.

If interested in this script I have hosted it publicly as a GitHub gist here:

https://gist.github.com/xan1000/9b196d553eb6b2350b15b630551bf5d3

Within the gist I have included setup instructions as well as how to set the script to run at the start of every hour as a cronjob.

P.S: At the time of writing - after a few days of auto blocking the attacking IPv4 addresses I have 3 different addresses being blocked:

<username>@<machine-name>:~$ sudo iptables -S
-P INPUT ACCEPT
-P FORWARD ACCEPT
-P OUTPUT ACCEPT
-A INPUT -s 146.255.185.154/32 -j DROP
-A INPUT -s 112.6.217.72/32 -j DROP
-A INPUT -s 111.42.90.52/32 -j DROP

Answer 3

Short of the using outside methods such as network firewalls and ACLs, you can use Windows Firewall on the local machine. I've found just opening the port to be troublesome - plus it does not limit unwanted traffic coming in through that port. I've had success using Program Rules in Windows Firewall.

Start by creating a new inbound rule in Windows Firewall with Advanced Security.

1. In the New Inbound Rule Wizard, select the "Program" radio button and click Next.
2. On the next screen, select "This program path:" and enter the location for SQL Server on the machine - for example, %ProgramFiles%\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr.exe
3. Select "Allow the connection" and click Next.
4. Make no changes on the Users step, click Next.
5. Leave the default settings on the Profile page, click Next.
6. On the Name page, enter "SQL Server Connectivity (Program Rule)", and click Finish on the wizard.

Find the rule in the Inbound Rules tab. Open the Properties dialog for the rule and go to the Scope tab. You can stipulate what server IPs, subnets, etc. of computers you want to access the box in the Remote IP address box.