System Health Extented Event - Security_Ring_Buffer_Error_Recorded

Question

I've recently upgraded a Production SQL instance from 2005 SP4 to 2012 SP2 and have noticed a large amount of the following entries in the system_health extended event:-

I've been looking into this and have found the following related articles/questions:-

SQL Server 2012: Security_error_ring_buffer_recorded: ImpersonateSecurityContext

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/65e792ff-8ef5-4ff7-b8b9-e71b262d8bb7/sql-2012-sp1-systemhealth-securityerrorringbufferrecorded-events?forum=sqldatabaseengine

Link

However, these point to the issue being related to failed logins. I've checked the SQL error log and no failed logins are being recorded.

I have been investigating this on a development instance and have noticed that the entries are generated whenever a SQL authenticated login connects to the SQL instance.

Has anyone come across this before? I'd like to get to the bottom of why these entries are being recorded.

If anyone wants to see this happening on their own (development) instance, log into SQL using a SQL authenticated login and then run:-

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] DESC

You should see entries that correspond with the time that you logged in.

It would be good to know if someone else can replicate what I'm seeing.

Thanks,

Andrew

Answer 1

Based on my understanding, this is just a noise that you can ignore.

This event is a part of sqlserver.security_error_ring_buffer_recorded event. It is detailed logging telling you what it is doing with the security errors (e.g. failed logins) to make sure they don't get lost.

The API ImpersonateSecurityContext (provided by SSPI) allows a server to impersonate the client's security context - meaning : the server can authenticate the client and use the client's security context for local access checks directly or by passing the context to another server on the same machine.

I would safely ignore those.

BTW, I am able see the same output on SQL Server 2012 + RTM + (Build11.0.2100.60)