Content

Introductory

When we run websites, our server and the websites and services that run on it are often vulnerable to external attacks, which MySQL / MariaDB our database server is no exception. If on our server a Fail2Ban protection software is also available - such as a ISPConfigAs part of your server environment, you can learn how to make your server more secure against attacks on your database server of type "Access denied for user root @ ip address (using password: YES / NO)".

Problem detection

As part of the operation of the server, one regularly checks and analyzes various log files from which one can obtain useful information about the state of the server. Because this article describes how to make your database server more secure, we look at the MySQL error log. root-Kent:

sudo cat /var/log/mysql/error.log | grep "Access denied"

For the sake of example, I have filtered the type of error described here.

As you can see, the error log contains some attempts to log in to the database server as root. There are two types of errors here:

2022-06-04  3:25:14 1551015 [Warning] Access denied for user 'root'@'xxx.xxx.xxx.xxx' (using password: NO)
2022-06-04  3:25:14 1551016 [Warning] Access denied for user 'root'@'xxx.xxx.xxx.xxx' (using password: YES)

One is when no password is used and the other is when a password is attempted. The IP address and the ip address of the attacker. There are a total of 11 items in today’s error log.

In the next section, you will find a solution to this using Fail2Ban protection software.

Configuring Fail2Ban

Fail2Ban is great for preventing these types of attacks. The software comes with a lot of filters, all you have to do is set up and enable the required instances.

Overview of the mysqld-auth filter

The solution to this problem has been specifically developed by the mysqld-auth filter. Consider:

sudo nano /etc/fail2ban/filter.d/mysqld-auth.conf

The contents of the file are as follows:

# Fail2Ban filter for unsuccesful MySQL authentication attempts
#
#
# To log wrong MySQL access attempts add to /etc/my.cnf in [mysqld]:
# log-error=/var/log/mysqld.log
# log-warning = 2
#
# If using mysql syslog [mysql_safe] has syslog in /etc/my.cnf

[INCLUDES]

# Read common prefixes. If any customizations available -- read them from
# common.local
before = common.conf

[Definition]

_daemon = mysqld

failregex = ^%(__prefix_line)s(?:\d+ |\d{6} \s?\d{1,2}:\d{2}:\d{2} )?\[\w+\] Access denied for user '[^']+'@'<HOST>' (to database '[^']*'|\(using password: (YES|NO)\))*\s*$

ignoreregex =

# DEV Notes:
#
# Technically __prefix_line can equate to an empty string hence it can support
# syslog and non-syslog at once.
# Example:
# 130322 11:26:54 [Warning] Access denied for user 'root'@'127.0.0.1' (using password: YES)
#
# Authors: Artur Penttinen
#          Yaroslav O. Halchenko

You can also see here that it was designed to monitor the error message written above. You can also run a test with the filter a fail2ban-regex using the command:

sudo fail2ban-regex /var/log/mysql/error.log /etc/fail2ban/filter.d/mysqld-auth.conf

The output for me is:

Running tests
=============

Use   failregex filter file : mysqld-auth, basedir: /etc/fail2ban
Use      datepattern : Default Detectors
Use         log file : /var/log/mysql/error.log
Use         encoding : UTF-8


Results
=======

Failregex: 11 total
|-  #) [# of hits] regular expression
|   1) [11] ^(?:\[\])?\s*(?:<[^.]+\.[^.]+>\s+)?(?:\S+\s+)?(?:kernel: \[ *\d+\.\d+\]\s+)?(?:@vserver_\S+\s+)?(?:(?:(?:\[\d+\])?:\s+[\[\(]?mysqld(?:\(\S+\))?[\]\)]?:?|[\[\(]?mysqld(?:\(\S+\))?[\]\)]?:?(?:\[\d+\])?:?)\s+)?(?:\[ID \d+ \S+\]\s+)?(?:\d+ |\d{6} \s?\d{1,2}:\d{2}:\d{2} )?\[\w+\] Access denied for user '[^']+'@'<HOST>' (to database '[^']*'|\(using password: (YES|NO)\))*\s*$
`-

Ignoreregex: 0 total

Date template hits:
|- [# of hits] date format
|  [26] {^LN-BEG}ExYear(?P<_sep>[-/.])Month(?P=_sep)Day(?:T|  ?)24hour:Minute:Second(?:[.,]Microseconds)?(?:\s*Zone offset)?
`-

Lines: 26 lines, 0 ignored, 11 matched, 15 missed
[processed in 0.00 sec]

|- Missed line(s):
[...]

In the results, we can see that you also noticed the 11 items in the log file above, so the filter works, all you have to do is turn on jail.

Create and start the mysqld-auth jail

To create a new jail, open /etc/fail2ban/jail.local file to edit:

sudo nano /etc/fail2ban/jail.local

and if it doesn't already have this jail, add the following to the end of the file:

[mysqld-auth]
enabled   = true
port      = 3306
filter    = mysqld-auth
logpath   = /var/log/mysql/error.log
findtime  = 3600
maxretry  = 2
bantime   = 86400

where the lines mean:

• [mysqld-auth]: Jail's name. This can be essentially anything - inside the square brackets, of course - but it's a good idea to use the name of the filter so you don't get bogged down later if you have a lot of jails.
• enabled: Enable Jail true value.
• port: disables MySQL connections on this port if necessary. The default port for MySQL is 3306. If you use a different custom port for database connections, specify it.
• filter: The unattended name of the filter file already viewed above, which is read from /etc/fail2ban/filter.d/.
• logpath: this log file is analyzed in Fail2Ban
• findtime: The time window, in seconds, within which it monitors the number of incidents. We can adjust this to our own needs, adapting to our own circumstances. For example, if a series of faster access attempts (attempts from the same ip address) occur in shorter times, you can give a lower value, but if you try to randomly scatter from the same ip address during the day, enter a larger time window.
• maxretry: This is the number of attempts allowed. If the number of hits reaches this, the block will be blocked. Adjust this to suit your needs.
• bantime: When disabled, disables the ip address in the firewall for the specified port.

Once you have the settings, save the file and restart the Fail2Ban service:

sudo systemctl restart fail2ban.service

Control

If you have restarted Fail2Ban, you can check if the jail works a fail2ban-client command:

fail2ban-client status mysqld-auth

You haven't caught anything here yet (shortly after midnight), but there may be ip addresses on your blacklist later:

Clarify MySQL external connection

Therefore, attempts to log in to phpMyAdmin with an incorrect password will not be blocked because they are localhost connections. An example of this is the following log file, which I just generated myself with some incorrect phpMyAdmin logins:

I came up with this example because it is also a fairly common case, as phpMyAdmin can be used by many people on a server and is available to anyone, as long as it is not particularly hidden. If you also want to block invalid login attempts in the phpMyAdmin interface, see another description.

Disable MySQL external connections

If you do not use any externally connected applications, you can disable the whole thing so that you can only connect to our databases from localhost.

To do this, open the main configuration file of your MySQL / MariaDB server:

• For MySQL: /etc/mysql/my.cnf
• For MariaDB: /etc/mysql/mariadb.conf.d/50-server.cnf

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Then let's look at the following section at the beginning:

[...]
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address            = 127.0.0.1
[...]

And remove the comment character (#) from the "bind-address" line.

Then save and restart the database server:

sudo systemctl restart mysqld

It is no longer possible to connect to our databases from the outside.

Conclusion

We've done a lot of work with the Fail2Ban security tool, now we've augmented our filters again for protection. With this Fail2Ban jail option, we have made our server one degree more secure, in addition to still being able to connect to our databases from the outside, allowing us to perform backups or any SQL commands from our home computer, for example. However, if you do not want to use this option, you can completely disable remote access to your MySQL / MariaDB server.