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:-
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)', 'varchar(30)') AS [ErrorCode], x.value('(//Record/Error/CallingAPIName)', 'varchar(255)') AS [CallingAPIName], x.value('(//Record/Error/APIName)', 'varchar(255)') AS [APIName], x.value('(//Record/Error/SPID)', 'int') AS [SPID], x.value('(//Record/@id)', 'bigint') AS [Record Id], x.value('(//Record/@type)', 'varchar(30)') AS [Type], x.value('(//Record/@time)', '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.