How to check, Azure SQL failed connections in metric

Question

How to check logs of 'Azure SQL failed connections in metric'. we can see connections failed in SQL Database metrics without any impact on adf pipelines, But we are not sure what are the connections failed and what is the error. Coud you please tell me how to check this issue.

Answer 1

You can also examine the sys.event_log table:

SELECT *  
FROM sys.event_log WHERE event_type = 'connection_failed'  

This will return a row for each failed connection, where you can find a cause like below:

<g class="gr_ gr_31 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="31" id="31">MACOffice</g> 2022-09-11 18:55:00.0000000 2018-09-11 19:00:00.0000000 connectivity connection_failed 7 blocked_by_firewall 2 1 Client IP address is not allowed to access the server NULL

In addition, you can use extended events or examine the ring buffer as shown below:

 ;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  

Answer 2

Hi,
To see details of failed logins you should enable Audit logs. Keep in mind that whatever endpoint you choose for the logs will result in additional cost. Audit logs tend to be with high volume.

Please "Accept the answer" if the information helped you. This will help us and others in the community as well.