SQL Server 环形缓冲区(Ring Buffer) -- RING_BUFFER_SECURITY_ERROR 诊断安全相关错误
环形缓冲存储了大量的在过去一段时间段内的安全错误信息,有助于分析SQL Server安全问题。
例如,当你尝试创建一个SQL登录账号,并启用密码策略,但是提供的密码不匹配密码策略。然后,你将会收到一个错误消息说明密码不匹配。这个错误将会存储在环形缓冲区。当你执行下面的查询,你将会导致错误的SPID以及导致失败的API名称。如上面示例描述的,你会找到NetValidatePwdPolicy错误。
如果你转换ErrorCode的值 0x8C5(=2245)为数值型,然后使用命令行命令NET HELPMSG <number>,你将会看到导致错误发生的原因。
调用的API的名称对于判断域帐号相关的安全错误是非常有用的,因为它将会调用活动目录服务相关的函数,这些文档很容易找到,对于定位问题更为容易。
对于SQL Server 2005:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SELECT
CONVERT
(
varchar
(30), GETDATE(), 121)
as
runtime,
DATEADD (ms, -1 * ((sys.cpu_ticks / sys.cpu_ticks_in_ms) - a.[Record
Time
]), GETDATE())
AS
Notification_time,
a.* , sys.ms_ticks
AS
[
Current
Time
]
FROM
(
SELECT
x.value(
'(//Record/Error/ErrorCode)[1]'
,
'varchar(30)'
)
AS
[ErrorCode],
x.value(
'(//Record/Error/CallingAPIName)[1]'
,
'varchar(255)'
)
AS
[CallingAPIName],
x.value(
'(//Record/Error/APIName)[1]'
,
'varchar(255)'
)
AS
[APIName],
x.value(
'(//Record/Error/SPID)[1]'
,
'int'
)
AS
[SPID],
x.value(
'(//Record/@id)[1]'
,
'bigint'
)
AS
[Record Id],
x.value(
'(//Record/@type)[1]'
,
'varchar(30)'
)
AS
[Type],
x.value(
'(//Record/@time)[1]'
,
'bigint'
)
AS
[Record
Time
]
FROM
(
SELECT
CAST
(record
as
xml)
FROM
sys.dm_os_ring_buffers
WHERE
ring_buffer_type =
'RING_BUFFER_SECURITY_ERROR'
)
AS
R(x)) a
CROSS
JOIN
sys.dm_os_sys_info sys
ORDER
BY
a.[Record
Time
]
ASC
|
对于SQL Server 2008:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SELECT
CONVERT
(
varchar
(30), GETDATE(), 121)
as
runtime,
dateadd (ms, (a.[Record
Time
] - sys.ms_ticks), GETDATE())
as
[Notification_Time],
a.* , sys.ms_ticks
AS
[
Current
Time
]
FROM
(
SELECT
x.value(
'(//Record/Error/ErrorCode)[1]'
,
'varchar(30)'
)
AS
[ErrorCode],
x.value(
'(//Record/Error/CallingAPIName)[1]'
,
'varchar(255)'
)
AS
[CallingAPIName],
x.value(
'(//Record/Error/APIName)[1]'
,
'varchar(255)'
)
AS
[APIName],
x.value(
'(//Record/Error/SPID)[1]'
,
'int'
)
AS
[SPID],
x.value(
'(//Record/@id)[1]'
,
'bigint'
)
AS
[Record Id],
x.value(
'(//Record/@type)[1]'
,
'varchar(30)'
)
AS
[Type],
x.value(
'(//Record/@time)[1]'
,
'bigint'
)
AS
[Record
Time
]
FROM
(
SELECT
CAST
(record
as
xml)
FROM
sys.dm_os_ring_buffers
WHERE
ring_buffer_type =
'RING_BUFFER_SECURITY_ERROR'
)
AS
R(x)) a
CROSS
JOIN
sys.dm_os_sys_info sys
ORDER
BY
a.[Record
Time
]
ASC
|
本文转自UltraSQL51CTO博客,原文链接: http://blog.51cto.com/ultrasql/1584055,如需转载请自行联系原作者