SQL Server 环形缓冲区（Ring Buffer） -- RING_BUFFER_SECURITY_ERROR 诊断安全相关错误
环形缓冲存储了大量的在过去一段时间段内的安全错误信息，有助于分析SQL Server安全问题。
例如，当你尝试创建一个SQL登录账号，并启用密码策略，但是提供的密码不匹配密码策略。然后，你将会收到一个错误消息说明密码不匹配。这个错误将会存储在环形缓冲区。当你执行下面的查询，你将会导致错误的SPID以及导致失败的API名称。如上面示例描述的，你会找到NetValidatePwdPolicy错误。
如果你转换ErrorCode的值 0x8C5（=2245）为数值型，然后使用命令行命令NET HELPMSG <number>，你将会看到导致错误发生的原因。
![clip_image001[4]](http://s3.51cto.com/wyfs02/M00/54/1A/wKioL1R4KieS3RM0AAB9hRXzjII109.jpg "clip_image001[4]")
调用的API的名称对于判断域帐号相关的安全错误是非常有用的，因为它将会调用活动目录服务相关的函数，这些文档很容易找到，对于定位问题更为容易。
![clip_image002[4]](http://s3.51cto.com/wyfs02/M01/54/1A/wKioL1R4Kiii8d5aAAGO1r2RR1k463.jpg "clip_image002[4]")
对于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，如需转载请自行联系原作者
