Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

security_error_ring_buffer_recorded api_name ImpersonateSecurityContext calling_api_name NLShimImpersonate error_code 5023

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.

share|improve this question
    
Have you contacted Microsoft support or opened a ticket? This looks like an internal error. –  Jon Seigel Mar 23 at 17:03
    
What is the build number of your instance and OS version? –  Shawn Melton Mar 23 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 at 18:23

1 Answer 1

I am going to assume you are looking for the definition and the root cause of these events: SQL Server 2005 SP2 added new ring buffer entries RING_BUFFER_SECURITY_ERROR (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. http://blogs.msdn.com/b/psssql/archive/2008/03/24/how-it-works-sql-server-2005-sp2-security-ring-buffer-ring-buffer-security-error.aspx. You state that you have no failed login entries in the event log nor in the errorlog. 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. http://blogs.msdn.com/b/sqlserverfaq/archive/2010/10/27/troubleshooting-specific-login-failed-error-messages.aspx

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."

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. Check if that login is directly mapped to one of the SQL Server logins by looking in the output of sys.server_principals. 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.

share|improve this answer
    
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 at 13:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.