SQL Server error log filtering (ERRORLOG)

Source: Internet
Author: User
Tags filter log server error log

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.