I. BACKGROUND
One day I found that the error log for the SQL Server server contains very many login error messages about the SA User: Login failed for user ' sa '. Reason: An error occurred while evaluating the password. [Client: XX. Xx. Xx. XX] ". But I have been disabling the SA user for a long time, how can there be so many SA user login information? I guess someone was violently hacking the password of the SA user in our database; Are there any good solutions to this attack?
I looked up some information and didn't find a good solution for the moment. I only think of a temporary way to relieve the pressure, that is, from the error message to log the client IP address of the SA user, and then set up a firewall to filter out these IP.
So how do you solve the IP statistics now? Using SSMS is not statistically possible, because there are too many errors in the error log. SSMS Open the error log as shown in the following figure Figure1 and Figure2;
(Figure1:sql Server log)
(Figure2:sa login information)
After searching for data, there are two ways to filter the error log:
1. Use SQL Server system stored procedure xp_readerrorlog to filter;
2. Use the default trace to filter;
Second, Xp_readerrorlog implementation error log filtering
(a) The basic operation of the error log can be referred to: SQL Server error log shrinkage (ERRORLOG). First understand the path and size of the error log file, you can find the file in a Figure3 way to see the size.
(Figure3:sql Server errorlog file information)
In addition to the way Figure3 directly finds the error log, we can also view the information by executing the stored procedure Exec xp_enumerrorlogs return table, as shown in Figure4. The xp_enumerrorlogs stored procedure also provides parameters, the default value is 1 (if no arguments are supplied to indicate that the passed parameter is 1), and 2 indicates the query SQL Server agent error log list, as shown in Figure13.
--SCRIPT1: Get [SQL Server] Error log list
exec xp_enumerrorlogs
exec xp_enumerrorlogs 1
(Figure4:sql Server error log list)