Troubleshoot SQL Security Error Ring Buffer Recorded - System Health

When monitoring sql security error events recorded through the System Health Extended Events , there’s a regular event recorded : security_error_ring_buffer_recorded. How can I troubleshoot?

These are some general guidelines and tools for troubleshooting these security errors. Some are easy to track down but there are also some esoteric security errors /

1) Identify the  meaning of Error Code with net helpmsg  .example  net helpmsg 5023

ERROR_INVALID_STATE 5023 (0x139F)

The group or resource is not in the correct state to perform the requested operation.

The error message description can be sometimes very general. If the explanation is not obvious , you’ll need to dig deeper

2)      First look in the Event Viewer and SQL Server Error logs. Are there any failed logins?

You can also look in the SQL Server default trace for Failed Login Events, use this query:

DECLARE @dftrc nvarchar(256)
 
SELECT  @dftrc=CAST(value as nvarchar(256)) FROM  fn_trace_getinfo(default)  WHERE  property = 2
 
 SELECT  * FROM fn_trace_gettable (@dftrc,default ) WHERE  EventClass= 20 ORDER BY starttime DESC

3)      If there aren’t any failed logins, open SQL Server Profiler. Select some Security Audit Events. There are many options available in the Security Events section.

4)      Focus on error messages , particularly UserError messages.

5)      Ring Buffers offer a large amount of information. To interrogate the RING BUFFER SECURITY ERROR use this query.

SELECT CONVERT (varchar(30), GETDATE(), 121) as [RunTime],
dateadd (ms, rbf.[timestamp] – tme.ms_ticks, GETDATE()) as [Notification_Time],
cast(record as xml).value('(//SPID)[1]', 'bigint') as SPID,
cast(record as xml).value('(//ErrorCode)[1]', 'varchar(255)') as Error_Code,
cast(record as xml).value('(//CallingAPIName)[1]', 'varchar(255)') as [CallingAPIName],
cast(record as xml).value('(//APIName)[1]', 'varchar(255)') as [APIName],
cast(record as xml).value('(//Record/@id)[1]', 'bigint') AS [Record Id],
cast(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
cast(record as xml).value('(//Record/@time)[1]', 'bigint') AS [Record Time],
tme.ms_ticks as [Current Time]
from sys.dm_os_ring_buffers rbf
cross join sys.dm_os_sys_info tme
where rbf.ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR' 
ORDER BY rbf.timestamp ASC

Once you’ve identified the error code use the SPID to track the errors either through Profiler, default trace or other custom SQL scripts

Read More on connectivity issues

SQL Server – Troubleshoot connectivity issues with Connectivity ...