归元

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_enumerrorlogsEXEC 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_readerrorlogEXEC xp_readerrorlog 0EXEC 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_eventSELECT*FROM fn_trace_geteventinfo(1) tg INNERJOIN sys.trace_events te ON tg.[eventid]= te.[trace_event_id]INNERJOIN 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@tracefileNVARCHAR(MAX) SET@tracefile= (SELECTLEFT([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 LEFTJOIN sys.trace_subclass_values sv ON gt.[eventclass]= sv.[trace_event_id]AND sv.[subclass_value]= gt.[objecttype]INNERJOIN sys.trace_events e ON gt.[eventclass]= e.[trace_event_id]INNERJOIN sys.trace_categories c ON e.[category_id]= c.[category_id]WHERE gt.[spid]>50AND 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 =20ORDERBY 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里一些未公开的扩展存储过程

SQL Server 存储过程 sp_helptext的不足以及解决方案

SQLSERVER errorlog讲解

Read Default Trace

fn_trace_gettable

fn_trace_gettable (Transact-SQL)

sp_trace_setevent

ObjectType Trace Event Column

 

 

 

 

作者:听风吹雨
出处: http://www.cnblogs.com/gaizai/
邮箱:gaizai@126.com
版权:本文版权归作者和博客园共有
转载:欢迎转载,必须保留原文链接
格言:不喜欢是因为不会 && 因为会所以喜欢

 

 

sql

评论

© 归元 | Powered by LOFTER