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:

SQL Server Authentication (i.e., username and password checks handled by SQL Server) is a known weaker and less-secure form of authentication.
Username/Password checks are trivial to execute from a performance perspective.
Hackers can therefore, effectively, mount RIDICULOUS numbers of brute-force attempts against a SQL Server with SQL Server Authentication enabled (i.e., a server running in ‘Mixed Mode’)
Because Microsoft has, effectively, deprecated SQL Server Authentication (because they refuse to address any security concerns or problems with it—other than to tell administrators to favor using Windows Authentication instead), there’s absolutely no functionality in place within SQL Server to prevent these kinds of attacks from happening.

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:

Not using SQL Server Authentication whenever and wherever possible.
Not putting SQL Servers on port 1433 when they’re 'internet facing.'
Ensuring that you’re using strong doses of cryptographic entropy (i.e., long pass-phrases) whenever SQL Server Authentication has to be used.

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 #Entries
EXEC 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:

If you have a publicly accessible SQL Server on the Internet on port 1433, it'll get pummeled.
The averages I quoted above don't quite tell the whole story—as mostly what I saw when looking at the logs was fairly long periods of almost 'quiet' with short periods here and there where the server was getting hit as much as 10 times a second with login requests.

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.

Comments

Plain text