4

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:-

enter image description here

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

http://blogs.msdn.com/b/sqlserverfaq/archive/2010/10/27/troubleshooting-specific-login-failed-error-messages.aspx

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

  • did you ever get any further with this? I too am see it on my two new 2014 SP1 instances. – Pixelated Jun 16 '15 at 8:25
  • No unfortunately not, I've been researching online but cannot determine exactly what the msgs relate to – dbafromthecold Jun 16 '15 at 10:20
  • In my case I noticed that the SQLErrorCode is always 'x_cse_Success' which maybe does suggest the records are just noise and can be safely ignored? Although like you mention, I don't quite understand why MS would log this amount of noise to a ring buffer called 'RING_BUFFER_SECURITY_ERROR' for simply no reason. Looking at my low volume dev instance it looks like each record actually relates to a Windows domain login. – Pixelated Jun 16 '15 at 10:27
2

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)

enter image description here

  • 1
    Hi Kin, thanks for the response. The logs are a bit misleading as they initially appear to be errors. I find it odd that there would be such verbose logging for something that should be ignored. It's also odd that it's only occurring for SQL authenticated accounts (as far as I can see anyway). – dbafromthecold Oct 31 '14 at 16:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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