SQL Server ring buffers (ring buffer)--ring_buffer_security_error diagnostic security phase

SQL Server Ring buffer--ring_buffer_security_error diagnosing security-related errors

The ring buffer stores a large number of security error messages over a period of time that can help you analyze SQL Server security issues.

For example, when you try to create a SQL login account and enable the password policy, the provided password does not match the password policy. You will then receive an error message stating that the password does not match. This error will be stored in the ring buffer. When you execute the following query, you will result in the wrong spid and the name of the API that caused the failure. As the above example describes, you will find a netvalidatepwdpolicy error.

If you convert ErrorCode value 0x8c5 (=2245) to numeric type and then use command line command net HELPMSG <number>, you will see the cause of the error.

650) this.width=650; "title=" clip_image001[4] "style=" border-top:0px;border-right:0px;border-bottom:0px; border-left:0px; "alt=" clip_image001[4] "src=" http://s3.51cto.com/wyfs02/M00/54/1C/ Wkiom1r4kaptc-1faacy92hegmg587.jpg "height=" "border=" 0 "/>

The name of the API that is called is useful for determining the security errors associated with a domain account because it will invoke Active Directory service-related functions, which are easy to find and easier to locate.

650) this.width=650; "title=" clip_image002[4] "style=" border-top:0px;border-right:0px;border-bottom:0px; border-left:0px; "alt=" clip_image002[4] "src=" http://s3.51cto.com/wyfs02/M01/54/1C/ Wkiom1r4kathyzpiaastodcm6im910.jpg "height=" 213 "border=" 0 "/>

For SQL Server 2005:

select convert  (varchar),  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 ( Selectx.value (' (//record/error/errorcode) [1] ',  ' varchar (+) ')  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 (+) ')  as [type],x.value (' (//record/@time) [1] ',  ' bigint ')   as [record time]from  (select cast  (record as xml)  FROM sys.dm_os_ Ring_bufferswhere ring_buffer_type =  ' Ring_buffer_security_error ')  as r (x))  across join sys.dm_os_sys_ Info sysorder by a.[record time] asc

For SQL Server 2008:

select convert  (varchar),  getdate (),  121)  as runtime,dateadd  (ms,  ( A.[record time] - sys.ms_ticks),  getdate ())  as [notification_time],a.*   sys.ms_ticks as [current time]from (Selectx.value (' (//record/error/errorcode) [1] ',  ' varchar (+) ')  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 (in) ')  AS  [Type],x.value (' (//record/@time) [1] ',  ' bigint ')  AS [Record Time]FROM  (select  cast  (Record as xml)  from sys.dm_os_ring_bufferswhere ring_buffer_type =   ' Ring_buffer_security_error ') as r (x))  aCROSS JOIN sys.dm_os_sys_info sysORDER BY a.[Record  Time] asc

This article is from the "Dripping Stone Wear" blog, please be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1584055

SQL Server ring buffers (ring buffer)--ring_buffer_security_error diagnostic security phase