SQL Server 错误日志过滤（ERRORLOG）
一、背景
有一天我发现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: <2013.05.03>
-- Description: <读取、过滤log.trc文件>
-- Blog: <http://www.cnblogs.com/gaizai/>
-- =============================================
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] <= '2013-05-14'
AND e.trace_event_id = 20
ORDER BY StartTime DESC
（Figure9：Default Trace返回的错误日志）
4. 为了让默认跟踪Default Trace和xp_readerrorlog返回的数据进行对比，我们再次执行xp_readerrorlog，使用参数尽量与Default Trace保持一致。
--Script7：对比 EXEC xp_readerrorlog 0,1,'Login failed for user ''sa''','','2013-05-13 12:00:00','2013-05-14','DESC'
（Figure10：xp_readerrorlog返回的错误日志）
对比Figure9与Figure10的信息，发现返回的记录数基本上是相同的。
四、补充说明
1. xp_enumerrorlogs和xp_readerrorlog除了可以查看【SQL Server错误日志】还可以查看【SQL Server 代理错误日志】。在SSMS中可以直接查看代理错误日志，如Figure11所示，它的路径和文件如Figure12所示，xp_enumerrorlogs值为2的时候表示查询SQL Server 代理错误日志列表，如Figure13所示。
--Script5：获取[SQL Server 代理]错误日志列表 EXEC xp_enumerrorlogs 2
（Figure11：SQL Server Agent日志）
（Figure12：SQL Server Agent ErrorLog文件信息）
（Figure13：SQL Server 代理错误日志列表）
值得注意的是【SQL Server 代理错误日志】并没有SQLAGENT.0这个文件，Figure13中的当前 = Figure14中的SQLAGENT.OUT = Figure15中的存档#10记录；如果要确认这一点，你可以通过日志文件的内容和文件的大小进行确认。
2. 由于我的数据库需要外网访问，所以没有办法做很多的改动，比如换数据库的端口，已经有太多的运用程序在使用了。（这个可以通过解决方案设计解决：SQL Server 数据库帐号密码安全设计）
3. 由于是sa无法在【是否允许连接到数据库引擎】选择【拒绝】选项，如Figure14所示。有没好的办法可以防止sa的暴力破解呢？虽然这样不会造成数据库创建链接，但是这样会造成：消耗网络流量；消耗数据库服务器的性能；消耗SQL Server日志容量；消耗ERRORLOG日志文件带来的磁盘IO（虽然可以通过Figure15那样设置不记录登陆信息，但是这样日志记录就没有意义了）；
（Figure14：帐号设置）
（Figure15：设置日志记录）
北京-宋沄剑提到把sa修改掉，修改后只是提示信息变为：“Login failed for user 'sa'. 原因: 找不到与所提供的名称相匹配的登录名。 [客户端: 60.190.118.153]”，问题还是存在。（修改之前错误信息是：Login failed for user 'sa'. 原因: 评估密码时出错。 [客户端: 60.190.118.153]）
4. 使用sp_helptext是无法找到系统存储过程xp_enumerrorlogs和xp_readerrorlog的源代码的，这个是为什么呢？
--查看存储过程或者视图源码 exec sp_helptext 'xp_enumerrorlogs' exec sp_helptext 'xp_readerrorlog'
（Figure16：sp_helptext）
5. 关于ERROR文件记录内容的详细解析可以参考：SQLSERVER errorlog讲解
6. 如果不想在log.trc中看到有关Login failed for user 'sa'.的错误日志，可以修改默认跟踪吗？在sp_trace_setevent (Transact-SQL)中提到：
1) 如果将 on 设置为 1，并且 column_id 为 NULL，则将事件设置为 ON 并清除所有列。如果 column_id 不为 NULL，则将该事件的列设置为 ON。
2) 如果将 on 设置为 0，并且 column_id 为 NULL，则将事件设置为 OFF 并清除所有列。如果 column_id 不为 NULL，则将列设置为 OFF。
（Figure17：修改默认跟踪错误）
7. 关于使用xp_readerrorlog读取ERROR文件记录内容，有没方法一次性读取所有的ERROR文件的数据呢？使用UNION ALL？
五、参考文献
sql server xp_readerrorlog SQL语句查看错误日志
在SQL Server 2005中使用xp_ReadErrorLog读取错误日志
在sql server 2008中使用xp_ReadErrorLog读取错误日志
Using xp_ReadErrorLog in SQL Server 2005
SQL Server 存储过程 sp_helptext的不足以及解决方案
fn_trace_gettable (Transact-SQL)
