0

I'm seeing a ton of these security_error_ring_buffer_recorded, error 5023, events in my AWS RDS SqlExpress instance. I noticed this while trying to investigate timeout errors I started getting in my .Net app. However, I do not see any failed logins but I have a suspicion it may be related to what I am experiencing, which is occasional timeouts.

I've used the following queries to attempt to identify the root cause but to no avail:

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


DECLARE @dftrc nvarchar(256)
SELECT  @dftrc=CAST(value as nvarchar(256)) FROM  fn_trace_getinfo(default)  WHERE  property = 2
SELECT  * FROM fn_trace_gettable (@dftrc,default ) WHERE  EventClass= 20 ORDER BY starttime DESC


EXEC sp_readerrorlog 0, 1, 'Login failed' 


;WITH RingBufferConnectivity as
(   SELECT
        records.record.value('(/Record/@id)[1]', 'int') AS [RecordID],
        records.record.value('(/Record/ConnectivityTraceRecord/RecordType)[1]', 'varchar(max)') AS [RecordType],
        records.record.value('(/Record/ConnectivityTraceRecord/RecordTime)[1]', 'datetime') AS [RecordTime],
        records.record.value('(/Record/ConnectivityTraceRecord/SniConsumerError)[1]', 'int') AS [Error],
        records.record.value('(/Record/ConnectivityTraceRecord/State)[1]', 'int') AS [State],
        records.record.value('(/Record/ConnectivityTraceRecord/Spid)[1]', 'int') AS [Spid],
        records.record.value('(/Record/ConnectivityTraceRecord/RemoteHost)[1]', 'varchar(max)') AS [RemoteHost],
        records.record.value('(/Record/ConnectivityTraceRecord/RemotePort)[1]', 'varchar(max)') AS [RemotePort],
        records.record.value('(/Record/ConnectivityTraceRecord/LocalHost)[1]', 'varchar(max)') AS [LocalHost]
    FROM
    (   SELECT CAST(record as xml) AS record_data
        FROM sys.dm_os_ring_buffers
        WHERE ring_buffer_type= 'RING_BUFFER_CONNECTIVITY'
    ) TabA
    CROSS APPLY record_data.nodes('//Record') AS records (record)
)
SELECT RBC.*, m.text
FROM RingBufferConnectivity RBC
LEFT JOIN sys.messages M ON
    RBC.Error = M.message_id AND M.language_id = 1033
WHERE RBC.RecordType='Error' --Comment Out to see all RecordTypes
ORDER BY RBC.RecordTime DESC

Here is a detailed look at the 5023 event:

api_name ImpersonateSecurityContext

call_stack 0x00007FF97C080B89 0x00007FF97BFAE8AF 0x00007FF97BFB0BB5 0x00007FF97BFAD741 0x00007FF97BFACBE2 0x00007FF97C080BA4 0x00007FF97BFB34C8 0x00007FF97BE502F3 0x00007FF97B0D57BD 0x00007FF97B0D55A5 0x00007FF97B0D51FD 0x00007FF97B0F0D18 0x00007FF97B0F0DB0 0x00007FF97B0F08D7 0x00007FF97B0F1038 0x00007FF99BD213D2

calling_api_name NLShimImpersonate

error_code 5023

id 5023

I'm also see a connectivity_ring_buffer_recorded event, but far less frequent, maybe 1:1000. However, I believe it is related, please correct me if I'm wrong. Here is the important detailed information from that event:

sni_consumer_error 17830

sni_error 0

state 107

tds_flags DisconnectDueToReadError, NetworkErrorFoundInInputStream, NormalDisconnect

tds_input_buffer_error 10054

type LoginTimers

Honestly not sure how to begin troubleshooting this issue with the AWS RDS Instance. The only problem is the timeouts, but I have other processes running in parallel with no issue using identical sql login credentials and I don't see any failed logins anywhere which makes me think the error code is erroneous or I'm misapprehending the error codes and their implications.

I've tried googling this and scoured SO, but haven't found a solution, or similar problem, e.g. the queries I tried executing should have returned failed logins.

My AWS forums post link: https://forums.aws.amazon.com/thread.jspa?messageID=814125&#814125

  • Have you contacted AWS support? – Brent Ozar Nov 14 '17 at 22:16
  • @BrentOzar I cannot submit a technical support request because I'm only on the basic plan. I'm currently waiting for my "lockout" timer on AWS forums to expire. Apparently, you have to wait a few hours after activating your forum account to make a post. However, I plan on posting in the AWS support forums as soon as possible. – Jacob Barnes Nov 14 '17 at 22:30
  • On a side note, thanks for all your work. I frequent the blog posts and use the Blitz scripts regularly. – Jacob Barnes Nov 14 '17 at 22:33
  • You're welcome, glad I could help! Yeah, I would definitely post over there and hope an AWS employee replies. You're on a double-obscure-whammy here (RDS on Express) that just doesn't see a lot of market adoption. – Brent Ozar Nov 14 '17 at 22:42

Your Answer

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

Browse other questions tagged or ask your own question.