代码改变世界

SQL Server 错误日志过滤(ERRORLOG)

2013-06-24 15:54  听风吹雨  阅读(19646)  评论(6编辑  收藏  举报

一、背景

  有一天我发现SQL Server服务器的错误日志中包括非常多关于sa用户的登陆错误信息:“Login failed for user 'sa'. 原因: 评估密码时出错。[客户端: XX.XX.XX.XX]”。可是我很久之前就已经禁用了sa用户,怎么还会有那么多的sa用户登陆信息呢?我猜想是有人在暴力破解我们数据库的sa用户的密码;关于这种攻击,大家有没好的解决方案呢?

  我查找了一些资料,暂时没有找到好的解决方案。我只想到一个暂时缓解压力的办法,那就是从错误信息中统计出登陆sa用户的客户端IP地址,再设置防火墙把这些IP过滤掉。

  那现在如何解决IP的统计呢?使用SSMS是根本无法进行统计,因为错误日志的记录太多了。SSMS打开错误日志的方式如下图Figure1和Figure2所示;

clip_image001

(Figure1:SQL Server 日志)

clip_image003

(Figure2:sa登陆信息)

经过资料的查找,发现有两种方式可以对错误日志进行过滤:

1. 利用SQL Server系统存储过程xp_readerrorlog进行过滤;

2. 利用默认跟踪(Default Trace)进行过滤;

 

二、xp_readerrorlog实现错误日志过滤

(一) 关于错误日志的基本操作可以参考:SQL Server错误日志收缩(ERRORLOG)。首先了解错误日志文件的路径和大小,可以通过Figure3的方式找到文件,查看大小。

clip_image004

(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

clip_image005

(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

clip_image006

(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'

clip_image008

(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'

clip_image010

(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%'

clip_image011

(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

clip_image013

(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'

clip_image015

(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

clip_image016

(Figure11:SQL Server Agent日志)

clip_image017

(Figure12:SQL Server Agent ErrorLog文件信息)

clip_image018

(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那样设置不记录登陆信息,但是这样日志记录就没有意义了);

clip_image019

(Figure14:帐号设置)

clip_image020

(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'

clip_image021

(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。

clip_image022

(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