Click on the banner to learn about and purchase my database training on Azure

SQL Server - How to Avoid Brute Force Attacks on Your Database

Views: 4.168 views
This post is the 13 part of 20 in the series. Security and Audit
Reading Time: 15 minutes

Hey guys!
In this article today I will demonstrate how brute force attacks occur on SQL Server and how to try to defend against such attacks.

What is brute force attack

Click to view this content.
Brute force attack is the simplest and most time-consuming technique to break into systems and databases. It consists of using password databases to test each of these passwords or systematically checking all possible keys and passwords until one can successfully log in to the destination.

This type of attack can be used when it is not possible to take advantage of other weaknesses in an encryption system (if any) that would make the task easier, as the time required to test all possible passwords can be more than a few seconds (3 characters) for thousands of years, depending on the number of characters in the password and the complexity of the characters used.

SQL Server brute force attack

Click to view this content.
By storing virtually all customer and company data as a whole, databases are potentially one of the most sought after targets by attackers trying to steal information or simply gain privileged access to that bank for some other purpose. A classic example of this is trying to access the bank with a user with administrative powers to be able to stop the SQL Server service and thus execute a malicious command such as the WannaCry ransomware, which requires banks not to be in use to be able to encrypt. the data.

To be able to identify if your SQL Server instance is experiencing attacks, the first thing you have to do is enable connection failure auditing (already enabled by default), as shown in the print below:

This can also be changed using T-SQL commands:

Once you have made sure that login failures are already being audited, any unsuccessful connection attempts due to the incorrect password or user that do not exist will be logged into SQL Server ERRORLOG, which you can access as follows:

And then view the logs:

In addition, you can also use the Extended procedures sp_readerrorlog and xp_readerrorlog to be able to view SQL Server log contents using command line.

To learn more about how these two procedures work, I suggest you study the code I will show later in this article and read the excellent article as well. ERRORLOG - The Basics, which explains all about SQL Server ERRORLOG.

How to identify brute force attack in SQL Server

Click to view this content.
Now that I've shown how to enable connection failure auditing and also where and how we can query this information, let's start working with this data to achieve our goal: How to identify brute force attack in SQL Server.

For a basic use, let's use xp_readerrorlog to read the current log file (0) and filter out login failures:


Let's now execute this command again, but storing the return of this SP in a temporary table and identifying the IP and user of this message:


If you want to analyze not only the last log file but all of it, you will need sp_enumerrorlogs to list the existing logs and thus loop through the files. I took the opportunity to further refine the search and bring only failed attempts with incorrect password and nonexistent login (there are other types, such as user without permission in the default database):


With this data, we can now begin to identify which users and sources of user attacks:


Important: An interesting point that we can take into account is to create an exception list with IP's that cannot be blocked, even in cases where there are many failed connections, such as the company's own fixed IP, application server IP, etc.

How to monitor for possible brute force invasions

Click to view this content.
As this subject is extremely important, you can not count on the luck of you remember to be following this type of event in your environment. If he begins to suffer a possible attack, you should act as soon as possible and nothing better than a warning to make you aware when this kind of behavior begins to suggest in the instances you administer.

To achieve this goal, I will use SQL Server Database Mail, which will allow us to send emails through the database when there are a certain number of connection failures and the Stored Procedure stpExports_Table_HTML_Output, which allows you to store in a variable the contents of a table in the database in the form of an HTML table, ideal for emailing.

The script below will fetch last-minute login failure events and email these logs (if they exceed the limit set for not sending too much email and turning SPAM).

If you need more information on how to enable and configure this feature, I suggest you read my article. SQL Server - How to enable and configure Database mail to send and monitor database emails (sp_send_dbmail).

Tracking Code:


How to Avoid Possible Brute Force Invasions

Click to view this content.
We have now reached the end of this article, where I will discuss possible solutions to this very common problem, which is brute force attack. The alternative that can be implemented in SQL Server, is to create an IP list to block and execute that list through some external script, such as PowerShell or even the Windows task scheduler, to create rules in the Windows Firewall to block these IP's when they reach a certain number of unsuccessful connection attempts.

To export the list of IP's to a file on the server, we can use several methods such as xp_cmdshell, OLE Automation or SQLCLR, but I will choose xp_cmdshell in this example, as it is the simplest to implement. Once the file has been exported in bat format with the commands to be executed, just schedule the execution of this script in the Windows task scheduler and configure the execution to use a user with Administrator permission.

Another viable alternative is to let SQL Server itself execute the commands to add the IP's to the Firewall instead of exporting a bat file with these commands. For this to be possible, the user who starts the SQL Server service must be a local Administrator of the machine, which turns out to be not so recommended from a security point of view.

An important point to be demonstrated again is about the list of exceptions, to prevent important IP's from being blocked during any test or temporary error.

To finish this theme, I will make available the complete script that:

  • identify the logs
  • categorize by IP and user
  • stores a history of retries
  • fetch connection failure occurrences since last procedure execution
  • implemented exception list
  • send alert email
  • generates a .bat file on the disk with commands to block the IP's in Windows Firewall with more than N connection failures

Script source code:

IP blocking script generated (Run as Administrator):

Security in SQL Server - SERIOUS MATTER!

After demonstrating some ways to mitigate the brute force invasion problem by identifying and blocking the IP's that are trying to break into the Windows Firewall environment, we need to be serious about security. Unfortunately it is very common to see totally sloppy environments in the database security where application users have sysadmin permission, users who access the database as sysadmin, users with elevated privileges and weak passwords, xp_cmdshell and dynamic query being common practices in environments and many other very worrying scenarios.

Regarding the scenario of this post, which is the brute force attack, the vast majority of cases occur because the database is published to the Internet, making it very easy for potential attackers to enter and the possibility of someone trying to attack your instance. In many cases, this occurs when the bank server is the same as the application server, which is not a good practice for either the bank or the application.

The bank ends up disputing server resources (CPU, Memory, Disk, Network) with the application and has yet to be visible and exposed to the Internet, without the possibility of creating Whitelist (only the addresses in the list have access to the server), since This would prevent users from accessing the application. Not to mention that if the application server is long, database data can be accessed as well, which is a catastrophic scenario.

Another point that should be raised, too, is that even when the brute force attack is performed on the environment, even if it is unsuccessful it still manages to damage the company because as it keeps trying the thousands of passwords to break into the bank, This process ends up consuming a lot of server resources unnecessarily.

Also, lots of ransomware, with WannaCry (which devastated the internet last year) are usually preceded by brute force attacks to gain access to the database so that the attacker can take the databases offline so that Ransonware can then encrypt the data (mdf) and log (ldf) files.

What to do then to solve this?

Well, the FIRST step for this is to make the bank invisible to the Internet. If you have to separate the bank from the application and set up 1 server for each one, do it then, creating a VPN to make the communication between the 2 servers (if they are on different networks, like Azure). If the company is unable to afford a server for each one and the application is accessed over the Internet, through several IP's and there is no possibility of making a whitelist to limit the IP's that will access the server, the way is to implement the solution I made available above and hope that the worst doesn't happen, since the script in this article will block only trying to access the bank, but there are N other types of attacks to access the server, such as RDP, SSH attacks, etc.

The second step is disable and rename user SA and any other standard SQL Server users, as these users are the main targets for brute force attacks, since the attacker already knows the login and only needs the password.

The third step is to avoid using SQL Server authentication logins as much as the bank that has to control and manage these passwords. In addition, the hash of these passwords is stored in the database and can be easily broken and validated without generating any exception in the SQL Server log, as I demonstrated in the article. SQL Server - How to identify fragile passwords, empty or equal to username. Use Windows AD authentication wherever possible, eliminating the need to enter passwords, and SQL Server has no hash or password information.

The FOURTH step is to implement the monitoring of this article and always keep an eye on this report, blocking in the Windows (or Azure) Firewall the IP's that have many connection failures. This work must be CONSTANT and not just in a timely manner. He left the database available for internet, so now he can handle it. If possible, use this list of IP's to block these addresses at the organization level, in the general firewall of the network and not just in the bank server. You can also take lists of Range of IPs by Country and try to release only the IPs of Brazil (most comprehensive Whitelist).

Step FIVE is to periodically review the password for SQL Server logins in your environment. It is very important that these users passwords be changed at least 1X PER YEAR. Also, these users' passwords must be large and complex (I always use passwords with 50 characters or more, letters, numbers, and symbols). In addition, it is important to have an annual (at least) access review policy. sysadmin is for DBA only and db_owner should not even be used (save very rare exceptions).

Well guys, that was the article about Brute Force Attack on SQL Server. I hope you enjoyed this Security line, which is an area that I should invest a lot of time studying and writing articles this year and it is the theme of my talk on MVPConf Latam 2019.

A big hug and see you in the next post.