2024-03-14

Troubleshooting SQL Server Network Error

 When you got below error in SQL Server error log:

Run the following query on SSMS:
;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' and RBC.Error=4014
ORDER BY RBC.RecordTime DESC

Check the column of 'RemoteHost' and find one of the most frequent IP, and you will need to collect Network Monitor between the client and SQL Server.

And please follow the steps to collect network package:

NetMon

Capturing Network Traffic via NetMon UI is documented in Microsoft Docs.

When you open NetMon before you start the capture it's very important to select the specific interface for which you would like to capture the traffic, Missing out the required interface will yield the capture to be unusable - Capturing additional interfaces will make the capture huge and you have to add filters to isolate data.

For Analysis it's very important to make sure the correct parser profile is selected "Windows" as it aligns the traffic as per Microsoft Open protocol specifications.

Following is a sample of capture where applied some important filters to capture/review Kerberos traffic between 2 clients, we can drill this down further if we want and if we know through which port specific conversation might be made in case if there are multiple applications on the same machine talking to AD.