SQL server errors reported on sys.database_connection_stats

Question

I have noticed a very high number of total_failure_count and connection_failure_count on the view sys.database_connection_stats . Its a SQL server database running on azure. I would like to know what can be done to resolve the issue. Its an unusual high number of connection failure_count > 300. We are also experiencing transient errors with the application.

On checking sys.event_log I can see the description of the error as login failed for user.

Answer 1

If you have the "Allow Azure services and resources to access this server" checkbox set off on the Azure SQL firewall settings, that may explain those failed conenctions.

Those failed connections may be related to Microsoft services trying to gather statistics and management information from your logical server:

Please considere to enable threat detection in order to have probably more information and take action in case of anomalous activities.

You can also try to get more detailed information using Extended Events or by enabling Auditing. See the following query to teh ring buffer to gather more info like RemoteHost:

;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