Query Audit Logs for Failed Logins Using T-SQL

Connect to the master database to run below queries:

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

Query the sys.event_log view

SELECT database_name, start_time, end_time, event_category,
	event_type, event_subtype, event_subtype_desc, severity,
	event_count, description
FROM sys.event_log
WHERE start_time >= '2022-03-25 12:00:00'
    AND end_time <= '2022-03-30 12:00:00';

Query login failures for users

The following query returns connection failures that are failed logins for users that occurred between 10:00 and 11:00 on March 25, 2022 (UTC).

SELECT database_name, start_time, end_time, event_category,
	event_type, event_subtype, event_subtype_desc, severity,
	event_count, description
FROM sys.event_log
WHERE event_type = 'connection_failed'
    AND event_subtype = 4
    AND start_time >= '2022-03-25 10:00:00'
    AND end_time <= '2022-03-25 11:00:00';

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

To check for failed connections in Azure SQL, you can use a combination of tools provided by Azure, such as Azure Monitor, Azure SQL Diagnostics, Log Analytics, and Kusto Query Language (KQL). Unfortunately, T-SQL queries won’t directly show failed connections, but you can leverage Diagnostic Logs and Log Analytics for this purpose.

Step 1: Enable Diagnostic Logs

1. Navigate to Azure Portal:
   • Go to your Azure SQL Database or SQL Server resource.
2. Enable Diagnostics Settings:
   • In the Monitoring section, click on Diagnostic Settings.
   • Click on Add Diagnostic Setting and select the logs you want to monitor, such as:
     • SQLSecurityAuditEvents (to capture failed logins and connection errors).
     • Errors (to capture database errors).
     • Timeout (for connection timeouts).
   • Choose a destination for the logs (Log Analytics workspace, Storage Account, or Event Hub).

Step 2: Query Failed Connections in Log Analytics

After enabling diagnostics, you can query connection failures in Log Analytics using Kusto Query Language (KQL).

Example Query for Failed Logins or Connection Timeouts:

AzureDiagnostics
| where ResourceType == "SQLDatabase" and Category == "SQLSecurityAuditEvents"
| where Message contains "login failed" or Message contains "timeout" or Message contains "connection"
| project TimeGenerated, Resource, client_ip_s, DatabaseName_s, Message

This query filters logs for:

• Login failed messages (authentication issues).
• Timeout or connection errors (network-related or performance-related).

Step 3: View Metrics Using Azure Monitor

Azure Monitor can provide insights into failed connection metrics. Here’s how to view it:

1. Navigate to Azure Portal.
2. Select your SQL Database or SQL Server resource.
3. In the Monitoring section, click on Metrics.
4. Choose the SQLServerConnections or Failed Connections metric to view graphical data on failed connection attempts.

Step 4: SQL Insights for Azure SQL

If you’ve enabled SQL Insights, you can use pre-configured dashboards to visualize performance and connection failures.

Step 5: Check for Firewall Rules or Connectivity Issues

Sometimes failed connections are caused by misconfigured firewall rules. Check firewall settings:

• Go to Networking in the Azure SQL Server resource.
• Verify that the IP address or range of the clients are allowed through the firewall.