Using the Mikrotik log database to suppress brute force
Good day.
In a previous publication, I explained how, easily and naturally, you can configure the collection of network traffic metadata on Mikrotik routers into a database.
Now it is time to teach our server to do elementary analysis of the received data and send commands back.
Purpose: Dynamic management of Mikrotik firewall rules to suppress network attacks with brute force password.
Tools: Fresh Linux distribution with rsyslogd v8, crond, mariadb, and the Mikrotik router itself.
Mechanics: Using a scheduled task, a SQL query is executed in the database with accumulated and replenished traffic data and returns a list of outgoing ip addresses, the script launched by bash cron creates Mikrotik commands and using the ssh connection replenishes the list of addresses for existing blocking rules.
It's about protecting open TCP ports. These can be ports on Mikrotik and forwarded to a local network.
To begin with, we denote where there may be weak points:
We write a SQL query to search for a brute-forcer.
In our organization, there are terminal servers open to the outside through non-priority ports.
In DNAT Mikrotik, I enabled the logging of the necessary rules by adding the prefix RDP_DNAT. By this prefix we will search:
This request shows the ip address (from which the attack is coming), the port to which the connection is made (the port number is changed) and the number of connection attempts, with preliminary grouping by src and a sample of lines, with more than 50 attempts in the past, from the current moment.
In my case, these addresses can be safely banned, since the number of connections from “good” clients is less, not more than 5-10 per day from one ip.
The request works fine, fast, but it is long. For further use, I propose to make a view (view) that would be less copy-paste in the future:
Check how the view works:
Fine.
Add a Mikrotik user with dsa key authorization
In the linux console, we generate the dsa key, under the user on whose behalf the scheduled task will be launched, I did from under root:
Passphrase no need to appoint. The public key /root/.ssh/id_dsa.pub is copied to Mikrotik in any available way. I brought it with the cat command, copied the text from the putty window into a text file, saved it and dragged it into the winbox files window.
I do not know why, but when performing the following operations through the winbox interface, something went wrong. When connecting from the server via ssh, Mikrotik also asked me for a password. After deleting the created user and performing all operations through the console, the dsa connection worked. Did about how described here .
In general, I received the desired entry without a password using the dsa key and ran the verification command:
Good.
Writing a bash script
The script is not complicated:
In order to transfer all the commands within one ssh connection, I needed to describe the mikrotik_cmd_list () function, in which the request is first executed with ip addresses stored in the brute_src_list variable, then this variable sequentially forms commands for Mikrotik. After calling the function, the output is routed through pipe to ssh.
Do not forget to close the access rights to the script to everyone except root and make the file executable.
The command that the script generates will add the ip address to rdp_banlist for 1 day, after that time it will be removed from the list. If you want to leave it forever, remove the timeout option.
Add a rule to the firewall
I came up with two options for how to use the rdp_banlist list:
Option one: add an rdp_banlist with an exclamation point in the NAT rules with the prefix RDP_DNAT.
Like that. That is, dnaty everything except what is in rdp_banlist.
In this embodiment, there are plus and minus.
Plus, the connections will stop right there.
The downside is that this ip will not get into the traflog database anymore and after a day, when the storage time in the blacklist passes, it will again begin to spoil.
Option two: add the rdp_banlist list with an exclamation mark to the forward firewall rule where we allow traffic to pass on TCP 3389, similar to how it was done in the first method.
Like that. We allow everything except that in a banlist.
Here, too, plus and minus.
A plus. Logs with the RDP_DNAT prefix will continue to be added to the traflog database, by which we determine the sign of the attack. As a result, when the timeout of the ban of a specific host that continues to attempt a brute force is over, it will be added to the banlist again after the next launch of the scheduled task.
The downside is that it continues to spoil the DSTNAT table, creating a new entry for each of its connections, even if it is temporary.
In general, the decision is yours, I chose both :) (in fact, in this case only the first one works), since the second one was included before and the mechanics were different there, based on the sequential entry in the lists stage1, stage2, stage3, banlist ... well, you understand. Old and not very reliable focus, can easily ban "good" clients and at the same time skip the "bad" politely timed stage1.
Assigned crontab task It
remains to add the assigned task to the crontab:
Such a record will run the script every hour at 12 minutes.
Admittedly, I just finished work on this mechanics today and with a high degree of probability something might go wrong. As circumstances will supplement and correct errors. I want todrink to sleep peacefully on New Year's holidays, therefore I am in a hurry to finish.
That's probably all.
Thank you all for your attention and Happy New Year!
References:
Documentation for mysql
Documentation for Mikrotik firewall
Thanks to Andrei Smirnov for the article about connecting using the dsa key.
In a previous publication, I explained how, easily and naturally, you can configure the collection of network traffic metadata on Mikrotik routers into a database.
Now it is time to teach our server to do elementary analysis of the received data and send commands back.
Purpose: Dynamic management of Mikrotik firewall rules to suppress network attacks with brute force password.
Tools: Fresh Linux distribution with rsyslogd v8, crond, mariadb, and the Mikrotik router itself.
Mechanics: Using a scheduled task, a SQL query is executed in the database with accumulated and replenished traffic data and returns a list of outgoing ip addresses, the script launched by bash cron creates Mikrotik commands and using the ssh connection replenishes the list of addresses for existing blocking rules.
It's about protecting open TCP ports. These can be ports on Mikrotik and forwarded to a local network.
To begin with, we denote where there may be weak points:
- Control protocols for router ssh, telnet, web, winbox
- Mail services smtp, pop, imap
- Any web services provided outside
- Remote Desktop MS RDP, VNC, etc.
- Anything else, at your discretion
We write a SQL query to search for a brute-forcer.
In our organization, there are terminal servers open to the outside through non-priority ports.
In DNAT Mikrotik, I enabled the logging of the necessary rules by adding the prefix RDP_DNAT. By this prefix we will search:
MariaDB [traflog]> select src,dport,count(dport) as'попытки подключения'from traffic where datetime>now() - interval1dayand logpref='RDP_DNAT'groupby src havingcount(dport)>50;
+-----------------+-------+---------------------------------------+
| src | dport | попытки подключения |
+-----------------+-------+---------------------------------------+
| 185.156.177.58 | 12345 | 118 |
| 185.156.177.59 | 12345 | 267 |
| 193.238.46.12 | 12345 | 318 |
| 193.238.46.13 | 12345 | 319 |
| 193.238.46.99 | 12345 | 342 |
| 194.113.106.150 | 12345 | 67 |
| 194.113.106.152 | 12345 | 167 |
| 194.113.106.153 | 12345 | 190 |
| 194.113.106.154 | 12345 | 192 |
| 194.113.106.155 | 12345 | 190 |
| 194.113.106.156 | 12345 | 216 |
| 194.113.106.158 | 12345 | 124 |
+-----------------+-------+---------------------------------------+
12 rows in set (0.06 sec)
This request shows the ip address (from which the attack is coming), the port to which the connection is made (the port number is changed) and the number of connection attempts, with preliminary grouping by src and a sample of lines, with more than 50 attempts in the past, from the current moment.
In my case, these addresses can be safely banned, since the number of connections from “good” clients is less, not more than 5-10 per day from one ip.
The request works fine, fast, but it is long. For further use, I propose to make a view (view) that would be less copy-paste in the future:
MariaDB [traflog]> createorreplaceview rdp_brute_day asselect src, dport, count(dport) from traffic where datetime>now() - interval1dayand logpref='RDP_DNAT'groupby src havingcount(dport)>50;
Query OK, 0 rows affected (0.23 sec)
Check how the view works:
MariaDB [traflog]> select src,count(dport) from rdp_brute_day;
+----------------+--------------+
| src | count(dport) |
+----------------+--------------+
| 185.156.177.58 | 11 |
+----------------+--------------+
1 row in set (0.09 sec)Fine.
Add a Mikrotik user with dsa key authorization
In the linux console, we generate the dsa key, under the user on whose behalf the scheduled task will be launched, I did from under root:
root@monix:~# ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter file inwhich to save the key (/root/.ssh/id_dsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_dsa.
Your public key has been saved in /root/.ssh/id_dsa.pub.
...Passphrase no need to appoint. The public key /root/.ssh/id_dsa.pub is copied to Mikrotik in any available way. I brought it with the cat command, copied the text from the putty window into a text file, saved it and dragged it into the winbox files window.
I do not know why, but when performing the following operations through the winbox interface, something went wrong. When connecting from the server via ssh, Mikrotik also asked me for a password. After deleting the created user and performing all operations through the console, the dsa connection worked. Did about how described here .
In general, I received the desired entry without a password using the dsa key and ran the verification command:
root@monix:/# ssh rsyslogger@192.168.0.230 /system resource print
uptime: 2w1d5h22m43s
version: 6.43.2 (stable)
...Good.
Writing a bash script
The script is not complicated:
mikrotik_cmd_list(){
brute_src_list=$(mysql --skip-column-names traflog -e 'select src from rdp_brute_day')
for src in$brute_src_listdoecho"ip firewall address-list add address=$src list=rdp_banlist timeout=1d"done
}
mikrotik_cmd_list | ssh -T rsyslogger@192.168.0.230In order to transfer all the commands within one ssh connection, I needed to describe the mikrotik_cmd_list () function, in which the request is first executed with ip addresses stored in the brute_src_list variable, then this variable sequentially forms commands for Mikrotik. After calling the function, the output is routed through pipe to ssh.
Do not forget to close the access rights to the script to everyone except root and make the file executable.
The command that the script generates will add the ip address to rdp_banlist for 1 day, after that time it will be removed from the list. If you want to leave it forever, remove the timeout option.
Add a rule to the firewall
I came up with two options for how to use the rdp_banlist list:
Option one: add an rdp_banlist with an exclamation point in the NAT rules with the prefix RDP_DNAT.
add action=dst-nat chain=dstnat comment="..." dst-address=1.2.3.4 dst-port=12345 log=yes log-prefix=RDP_DNAT protocol=tcp src-address-list=\
!rdp_banlist to-addresses=192.168.200.181 to-ports=3389Like that. That is, dnaty everything except what is in rdp_banlist.
In this embodiment, there are plus and minus.
Plus, the connections will stop right there.
The downside is that this ip will not get into the traflog database anymore and after a day, when the storage time in the blacklist passes, it will again begin to spoil.
Option two: add the rdp_banlist list with an exclamation mark to the forward firewall rule where we allow traffic to pass on TCP 3389, similar to how it was done in the first method.
add action=accept chain=forward comment="..." dst-port=3389 log=yes log-prefix=ACCEPT_RDP protocol=tcp src-address-list=\
!rdp_banlist to-ports=3389Like that. We allow everything except that in a banlist.
Here, too, plus and minus.
A plus. Logs with the RDP_DNAT prefix will continue to be added to the traflog database, by which we determine the sign of the attack. As a result, when the timeout of the ban of a specific host that continues to attempt a brute force is over, it will be added to the banlist again after the next launch of the scheduled task.
The downside is that it continues to spoil the DSTNAT table, creating a new entry for each of its connections, even if it is temporary.
In general, the decision is yours, I chose both :) (in fact, in this case only the first one works), since the second one was included before and the mechanics were different there, based on the sequential entry in the lists stage1, stage2, stage3, banlist ... well, you understand. Old and not very reliable focus, can easily ban "good" clients and at the same time skip the "bad" politely timed stage1.
Assigned crontab task It
remains to add the assigned task to the crontab:
root@monix:/root# echo '12 * * * * root /usr/share/traflog/scripts/rdp_brute.sh >/dev/null 2>&1' >> /etc/crontabSuch a record will run the script every hour at 12 minutes.
Admittedly, I just finished work on this mechanics today and with a high degree of probability something might go wrong. As circumstances will supplement and correct errors. I want to
That's probably all.
Thank you all for your attention and Happy New Year!
References:
Documentation for mysql
Documentation for Mikrotik firewall
Thanks to Andrei Smirnov for the article about connecting using the dsa key.