SQL Server Authentication on Internet Facing Servers

Two years ago I blogged about a substantial, known, problem with SQL Server Authentication. As a recap, some of the key highlights covered in that post were:

And that last bullet point is the salient bit—in the sense that if hackers are trying to brute force their way to a password, there’s nothing in place that will initiate an account lock-out operation—regardless of whether the login is tried 2 times a second, or 200 times a second.

As such, my previous post outlined some of the things you can do to help protect against this problem, including:

A Recent Test

Recently, I needed to expose a SQL Server to the 'Internet' for a while from my lab. I was doing a bit of testing from an application, and therefore opened the server up on port 1433—after making sure I had strong passwords in place. Over the period of a few days I periodically checked my SQL Server Logs and was able to detect different IP addresses detecting huge numbers of brute-force attacks against the sa account. As such, within the space of a few days I ended up blocking 12 different IP addresses (manually) via the Windows Firewall.

I then, basically, let things run for a while. After a month of letting the server run like this (and after blocking 12 IPs initially, but doing nothing afterwards) I checked on the logs by running the following: 

CREATE TABLE #Entries (LogDate datetime,ProcessInfo sysname,[Text] varchar(2048) );INSERT INTO #EntriesEXEC sys.xp_readerrorlog 0,1, NULL, NULL;SELECT COUNT(*) FROM #Entries e WHERE e.Text LIKE 'Login failed for user ''sa''%';

Which returned a count of 268, 112—over the last 30 days. Which equates to an average of 8,937 attempts against the sa account daily (or around 372 attempts, on average, per hour).

At this point, with app-testing done, rather than just 'removing' my test SQL Server from being internet facing, I switched it to a different port, then let it run for three days—at which I point I rechecked and found no attempts against the sa account.

Takeaways:

Stated differently, if you’ve got a SQL Server that's publicly facing, make sure you've done everything outlined here to make it as secure as possible—including putting it on a different port if at all possible.