In Azure SQL Pool we have metrics like Failed Connections : System Errors and Failed Connections : User Errors, but how can we find out what exactly was the error?

Question

In Azure SQL Pool we have metrics like Failed Connections : System Errors and Failed Connections : User Errors, but how can we find out what exactly was the error? How to find out which session was impacted and what error was associated with which query. Basically I want to build a query monitoring dashboard but not with just numbers but the details as well.

Answer 1

Hello Nilesh Shrimant,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that you are having system errors and failed connections: User errors, and you need to know how you can find out what exactly was the error.

To get detailed information about failed connections in Azure SQL Pool, you can use several methods to identify the specific errors, impacted sessions, and associated queries. Some steps you can follow are to combining these methods below, you can build a comprehensive query monitoring dashboard that not only shows the number of failed connections but also provides detailed insights into the errors, impacted sessions, and associated queries.:

1. Use the sys.event_log Table, these logs have various events, including connection failures. You can query it to get details about failed connections.

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

This will return rows for each failed connection, including details like the cause of the failure. - https://learn.microsoft.com/en-us/answers/questions/1070919/how-to-check-azure-sql-failed-connections-in-metri

2. Extended Events can capture detailed information about connection failures. You can create a session to capture these events and then query the data.

   CREATE EVENT SESSION [FailedConnections] ON SERVER
   ADD EVENT sqlserver.error_reported(
       WHERE (error_number = 18456) -- Example for login failed
   )
   ADD TARGET package0.event_file(SET filename = 'FailedConnections.xel');
   GO
   ALTER EVENT SESSION [FailedConnections] ON SERVER STATE = START;
   GO

You can then query the event file to get details about the failures.

3. Dynamic Management Views (DMVs) like sys.dm_exec_sessions and sys.dm_exec_requests can provide information about active sessions and requests. You can join these with the event log to correlate sessions with connection failures.

   SELECT 
       es.session_id,
       es.login_name,
       er.command,
       er.sql_handle,
       el.event_time,
       el.error_number,
       el.error_message
   FROM sys.dm_exec_sessions es
   JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
   JOIN sys.event_log el ON es.session_id = el.session_id
   WHERE el.event_type = 'connection_failed';

4. Enabling audit logs can help you capture detailed information about failed logins and other security-related events. These logs can be sent to a storage account, Log Analytics workspace, or Event Hubs for further analysis.

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.