SQL Server 错误日志过滤（ERRORLOG）

tonwei139

　　一、背景
　　有一天我发现SQL Server服务器的错误日志中包括非常多关于sa用户的登陆错误信息：“Login failed for user 'sa'. 原因: 评估密码时出错。[客户端: XX.XX.XX.XX]”。可是我很久之前就已经禁用了sa用户，怎么还会有那么多的sa用户登陆信息呢？我猜想是有人在暴力破解我们数据库的sa用户的密码；关于这种攻击，大家有没好的解决方案呢？
　　我查找了一些资料，暂时没有找到好的解决方案。我只想到一个暂时缓解压力的办法，那就是从错误信息中统计出登陆sa用户的客户端IP地址，再设置防火墙把这些IP过滤掉。
　　那现在如何解决IP的统计呢？使用SSMS是根本无法进行统计，因为错误日志的记录太多了。SSMS打开错误日志的方式如下图Figure1和Figure2所示；

　　（Figure1：SQL Server 日志）

　　（Figure2：sa登陆信息）
　　经过资料的查找，发现有两种方式可以对错误日志进行过滤：
　　1. 利用SQL Server系统存储过程xp_readerrorlog进行过滤；
　　2. 利用默认跟踪（Default Trace）进行过滤；
　　
　　二、xp_readerrorlog实现错误日志过滤
　　(一) 关于错误日志的基本操作可以参考：SQL Server错误日志收缩（ERRORLOG）。首先了解错误日志文件的路径和大小，可以通过Figure3的方式找到文件，查看大小。

　　（Figure3：SQL Server ErrorLog文件信息）
　　除了Figure3直接找到错误日志的方式之外，我们还可以通过执行存储过程EXEC xp_enumerrorlogs返回表的形式进行查看信息，如Figure4所示。xp_enumerrorlogs存储过程还提供参数，默认值为1（如果没有提供参数表示传入的参数为1），2的时候表示查询SQL Server 代理错误日志列表，如Figure13所示。



--Script1：获取[SQL Server]错误日志列表
EXEC xp_enumerrorlogs
EXEC xp_enumerrorlogs 1

　　（Figure4：SQL Server 错误日志列表）
　　(二) 接下来了解系统存储过程：xp_readerrorlog，它一共有7个参数，分别是：
　　1. 存档编号（0~99）
　　2. 日志类型（1为SQL Server日志，2为SQL Server Agent日志）
　　3. 查询包含的字符串
　　4. 查询包含的字符串
　　5. LogDate开始时间
　　6. LogDate结束时间
　　7. 结果排序，按LogDate排序（Desc、Asc）
　　(三) 接着讲解xp_readerrorlog系统存储过程的运用：
　　1. 如果你想查询当前SQL Server错误日志文件（当前正在写入错误信息的文件）的内容，请执行SQL脚本：EXEC xp_readerrorlog，存档编号的默认值为0，它相当于打开文件ERRORLOG（路径可参考Figure3），如果想读取其它的历史错误日志文件，直接填写对应的存档编号就可以了（存档编号可以参考Figure4），下面3条SQL语句的执行效果是一样的：



--Script2：查询当前SQL Server日志信息
EXEC xp_readerrorlog
EXEC xp_readerrorlog 0
EXEC xp_readerrorlog 0,1

　　（Figure5：当前SQL Server错误日志）
　　查询存档编号为n(n Between 0 And 99)的SQL Server日志信息：Exec xp_readerrorlog n，n为什么只能0~99？可参考：SQL Server错误日志收缩（ERRORLOG）
　　2. 我们继续学习其它参数的使用，查看SQL Server日志历史存档为1文件中，发生的时间为2013-05-09至2013-05-10之间的错误，排序方式为时间的倒排序，为了满足上面的要求，执行下面的SQL脚本：



--Script3：查看SQL Server日志存档为1，时间范围为XX，按照时间反排序
EXEC xp_readerrorlog 1,1,NULL,NULL,'2013-05-09','2013-05-10','DESC'

　　（Figure6：错误日志时间过滤）
　　3. 查看SQL Server日志历史存档为1文件中，错误内容里面包含字符串：'Login failed for user ''sa'''，并且包括字符串：'192.168.1.5'，发生的时间为2013-05-09至2013-05-10之间的错误，排序方式为时间的倒排序，为了满足上面的要求，执行下面的SQL脚本：



--Script4：查看SQL Server日志存档为，包含XX字符串，并且包含%%字符串，时间范围为XX，按照时间反排序
EXEC xp_readerrorlog 1,1,'Login failed for user ''sa''','192.168.1.5','2013-05-09','2013-05-10','DESC'

　　（Figure7：错误日志字符串+日期过滤）
　　
　　三、Default Trace实现错误日志过滤
　　1. 关于默认跟踪（Default Trace）基础知识可以参考： SQL Server 默认跟踪（Default Trace）
　　2. 要过滤错误日志，那首先就要知道在默认跟踪中那个类型trace_event_id是代表错误日志的，在Read Default Trace中描述了关于trace_event_id的信息：If you are interested in what the default trace has been setup to capture you can run this (Note you cannot edit the default trace!)。



--Script5：trace_event
SELECT *  
FROM fn_trace_geteventinfo(1) tg   
INNER JOIN sys.trace_events te ON tg.[eventid] = te.[trace_event_id]  
INNER JOIN sys.trace_columns tc ON tg.[columnid] = tc.[trace_column_id]
WHERE te.name like '%login%'

　　（Figure8：trace_event_id信息）
　　通过上面Script5的SQL脚本，我们知道需要监控EventName为：Audit Login Failed，trace_event_id为20的信息，如Figure8所示。另外查看方式：sp_trace_setevent，在这里你也可以找到关于Login Failed描述所对应的Event number。
　　3. 下面我们来实现使用fn_trace_gettable读取log.trc文件的方式来过滤错误日志：



--Script6：返回登陆错误信息
-- =============================================
-- Author:        
-- Create date:   
-- Description:   
-- Blog:        
-- =============================================
DECLARE @tracefile NVARCHAR(MAX)
SET @tracefile = (SELECT LEFT([path],LEN([path])-CHARINDEX('\',REVERSE([path])))+ '\log.trc' FROM sys.traces WHERE [is_default] = 1)
SELECT
gt.[ServerName]
,gt.[DatabaseName]
,gt.[SPID]
,gt.[StartTime]
,gt.[ObjectName]
,gt.[objecttype] [ObjectTypeID]--http://msdn.microsoft.com/en-us/library/ms180953.aspx
,sv.[subclass_name] [ObjectType]
,e.[category_id] [CategoryID]
,c.[Name] [Category]
,gt.[EventClass] [EventID]
,e.[Name] [EventName]
,gt.[LoginName]
,gt.[ApplicationName]
,gt.[TextData]
FROM fn_trace_gettable(@tracefile, DEFAULT) gt
LEFT JOIN sys.trace_subclass_values sv ON gt.[eventclass] = sv.[trace_event_id] AND sv.[subclass_value] = gt.[objecttype]
INNER JOIN sys.trace_events e ON gt.[eventclass] = e.[trace_event_id]
INNER JOIN sys.trace_categories c ON e.[category_id] = c.[category_id]
WHERE gt.[spid] > 50
AND gt.[databasename] = 'master'
AND e.category_id = 8 --category 8表示安全
AND gt.[starttime] >= '2013-05-13 12:00:00'
AND gt.[starttime]