12

a couple servers I manage are recording a lot of events in the system_health XE session.

The error_code 5023 should be (System Error Codes):

ERROR_INVALID_STATE    5023 (0x139F)

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

I have no failed login event in the event security log, nor in the SQL Server log.

Ian Boyd
  • 976
  • 10
  • 20
Jens W.
  • 1,219
  • 1
  • 8
  • 16
  • Have you contacted Microsoft support or opened a ticket? This looks like an internal error. – Jon Seigel Mar 23 '14 at 17:03
  • What is the build number of your instance and OS version? –  Mar 23 '14 at 19:19
  • It is a plain SQL 2012 with SP1: 11.00.3000.00. I want to contact MS Support as soon as I am sure it isn't an error in my setup. Hoping for feedback here... – Jens W. Mar 27 '14 at 18:23

1 Answers1

5

I am going to assume you are looking for the definition and the root cause of these events.

From How It Works: SQL Server 2005 SP2 Security Ring Buffer – RING_BUFFER_SECURITY_ERROR (archive):

SQL Server 2005 SP2 added new ring buffer entries (sys.dm_os_ring_buffers) for various security errors. The reason the ring buffer entries were added was to provide the DBA with more details as to why a client is receiving a failed login or other such error.

You state that you have no failed login entries in the event log nor in the error log. Instead you can query this ring buffer directly:

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

The notification time might shed some light on the root cause.

I think you will find that the date/time of the entries will line up with login failure entries in the errorlog similar to:

"Login failed for user 'domain\user'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: ] Error: 18456 Severity: 14 State: 11."

From Troubleshooting specific Login Failed error messages (archive) :

State 11 corresponds to “Valid login but server access failure” which indicates that the login is valid but is missing certain security privileges which would grant it access to the instance.

  1. Check if that login is directly mapped to one of the SQL Server logins by looking in the output of sys.server_principals.
  2. If the login is directly mapped to one of the available logins in the SQL instance, then check if the SID of the login matches the SID of the Windows Login.

If someone dropped the login at the Windows/AD level, and added it back, it will get a new SID which won't match the SID SQL has stored in its system catalog and it will fail.

Glorfindel
  • 2,201
  • 5
  • 17
  • 26
stacylaray
  • 2,565
  • 15
  • 22
  • 1
    The problem is: new server, all accounts new and i can login with them. But yet dozens of such events - without clear/any impact. I will try to read out the ring buffer as soon as possible. – Jens W. Mar 28 '14 at 13:07
  • 4
    I've seen this same behavior on non-AD servers and they are not associated with any login failures or other errors. It is just logging in the security error ring buffer and floods system_health and no other reasons for why it occurs. – Jonathan Kehayias Jun 15 '17 at 18:26