Azure SQL Database Server - SQL Audit to Log Analytics Workspace - How can I view Failed Logins ?

Aleksey Vitsko 25 Reputation points

I have Azure SQL Database server where I enabled SQL Audit. It writes to Log Analytics Workspace as audit destination. According to docs, it should capture FAILED_DATABASE_AUTHENTICATION_GROUP events.

I have requirement to capture and view failed logins.

Now I go to log analytics workspace, go to Logs section and run KQL query:

SQLSecurityAuditEvents
| where Succeeded == false
| project TimeGenerated, LogicalServerName, EventTime, Succeeded, ClientIp, SessionServerPrincipalName, ServerPrincipalName, DatabaseName, ApplicationName, ConnectionId, IsServerLevelAudit, EventId, Type, _ResourceId

But it doesn't return any results. Before this, I on purpose generated some failed logins by typing wrong password from SQL admin account when log in to Azure SQL DB server.

How can I view failed login attempts ?

0 comments No comments
Sign in to comment

Answer accepted by question author and recommended by moderator

TP 157.6K Reputation points • Volunteer Moderator

Hi,

As a starting point, you may use below query in the Log Analytics Workspace that your audit is pointing to:

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents"

You might want to refine it more, for example, only "DATABASE AUTHENTICATION FAILED" entries:

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents" and action_name_s == "DATABASE AUTHENTICATION FAILED"

Below is screenshot showing failed login attempt using invalid user name/password:

👁 User's image

Please click Accept Answer and upvote if the above was helpful.

Thanks.

-TP

  1. Aleksey Vitsko 25 Reputation points

    this KQL worked. Thank you! Accepted, don't see the upvote button

Sign in to comment

2 additional answers

  1. Jose Benjamin Solis Nolasco 8,561 Reputation points • Volunteer Moderator

    Welcome to Microsoft Q&A,

    Hello @Aleksey Vitsko I hope you are doing well,

    Your KQL query is structurally correct and targets the right table (SQLSecurityAuditEvents). Since the query returns zero results despite purposefully generating failed logins, you are likely encountering the architectural distinction between Database-level and Server-level auditing in Azure SQL.

    You must enable Server-level Auditing. Server-level policies automatically apply to all databases on the logical server, specifically including master, which is required to capture server-admin login failures.

    1. Navigate to your Azure SQL Server resource (the logical server, not the database) in the Azure Portal.
    2. Under the Security menu on the left, select Auditing.
    3. Toggle Enable Azure SQL Auditing to ON at this server level.
    4. Select your Log Analytics workspace and click Save.

    Once Server-level auditing is enabled, generate a few more failed logins and wait 5–10 minutes for Log Analytics ingestion. You can optimize your query by filtering on the specific ActionId for failed logins (DBAF) and expanding the AdditionalInformation column to see the exact SQL Error Code:

    Code snippet

    SQLSecurityAuditEvents
| where Succeeded == false
| where ActionId == "DBAF" or ActionName == "DATABASE AUTHENTICATION FAILED"
| project TimeGenerated, LogicalServerName, DatabaseName, ClientIp, ServerPrincipalName, ApplicationName, ActionName, AdditionalInformation
| order by TimeGenerated desc

    😊 If my answer helped you resolve your issue, please consider marking it as the correct answer. This helps others in the community find solutions more easily.

    1. Aleksey Vitsko 25 Reputation points

      Hi @Jose Benjamin Solis Nolasco thanks for participating. Server-level audit is enabled. The query you provided did not bring any results. Sorry man I have to accept another answer :)

    2. Jose Benjamin Solis Nolasco 8,561 Reputation points • Volunteer Moderator

      @Aleksey Vitsko I'm glad you was able to resolve the issue, dont hesitate if you have any question our comunity will always be avalaible to help. Thanks for support others user accepting the answer will let others know this helps.

    Sign in to comment

  2. AI answer

    Failed login attempts are audited and sent to the SQLSecurityAuditEvents table only when:

    1. Auditing is enabled on the Azure SQL server or database.
    2. The default auditing policy (which includes FAILED_DATABASE_AUTHENTICATION_GROUP) is in effect, or an equivalent custom policy is configured.
    3. Auditing is configured to send SQLSecurityAuditEvents to the Log Analytics workspace (via Diagnostic Settings with SQLSecurityAuditEvents enabled).

    From the provided configuration details:

    • The default auditing policy includes FAILED_DATABASE_AUTHENTICATION_GROUP, which captures failed logins.
    • When auditing is configured to a Log Analytics workspace, a Diagnostic Setting is created with the SQLSecurityAuditEvents category enabled.
    • In Log Analytics, failed logins are represented by records in SQLSecurityAuditEvents where Succeeded == false.

    To view failed login attempts in Log Analytics:

    1. Confirm that auditing is enabled and sending to Log Analytics:
      • In the Azure portal, open the SQL server or SQL database.
      • Go to Auditing under Security.
      • Verify that auditing is ON and that Log Analytics is selected as a destination.
      • Ensure the Log Analytics workspace shown there is the same workspace being queried.
    2. Confirm the Diagnostic Setting for SQLSecurityAuditEvents exists:
      • In the Azure portal, open the SQL server.
      • Go to Diagnostic settings.
      • Verify there is a diagnostic setting that sends logs to the same Log Analytics workspace and that the SQLSecurityAuditEvents category is checked.
    3. Query the correct workspace and table:
      • In the Log Analytics workspace that is configured in the auditing/diagnostic settings, open Logs.
      • Run a basic query to verify any audit data is arriving: 
         SQLSecurityAuditEvents
 | take 10
      • If records appear, refine to failed logins: 
         SQLSecurityAuditEvents
 | where Succeeded == false
 | project TimeGenerated, DatabaseName, ServerPrincipalName, SessionServerPrincipalName, ClientIp, ApplicationName, ActionId, ActionName, EventTime
    4. Ensure the failed logins being generated are actually server/database authentication failures:
      • The default auditing groups capture database-level authentication events (FAILED_DATABASE_AUTHENTICATION_GROUP).
      • Generate a failed login by attempting to connect to the audited Azure SQL logical server/database with an incorrect password or invalid user, then wait a short period and re-run the query above.

    If SQLSecurityAuditEvents returns no rows at all, the issue is with the auditing/diagnostic configuration (wrong workspace, category not enabled, or auditing not actually on). If the table has data but none with Succeeded == false, then no failed login events are being generated for the audited server/database.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.
Sign in to answer

Your answer