Share via

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?

Nilesh Shrimant 0 Reputation points Microsoft Employee
Nov 19, 2024, 2:21 PM

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.

Azure SQL Database
{count} votes

1 answer

Sort by: Most helpful
  1. Sina Salam 12,816 Reputation points
    Nov 19, 2024, 8:38 PM

    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

    1. 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.

    1. 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';
    
    1. 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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.