To Retrieve User Access Details from Azure SQL Database Using Log Analytics for last one Month.

Pre requisites

the prerequisites for running a query to retrieve user access from an Azure SQL Database using Log Analytics:

Prerequisites

1. Azure Subscription:
   • You need an active Azure subscription with access to both the Azure SQL Database and Azure Log Analytics.
2. Azure SQL Database:
   • Ensure that you have an existing Azure SQL Database with auditing enabled. Auditing must be set up to send logs to a Log Analytics workspace.
3. Log Analytics Workspace:
   • Set up a Log Analytics workspace within the same subscription where the audit logs from your Azure SQL Database will be stored.
4. Configure SQL Auditing:
   • Enable SQL Auditing for your Azure SQL Database or Server and direct the logs to your Log Analytics workspace:
     • In the Azure Portal, go to your SQL Database or SQL Server.
     • Under Security, select Auditing.
     • Choose Log Analytics as the destination and select your Log Analytics workspace.
5. Permissions:
   • Ensure you have appropriate permissions:
     • Contributor or Owner role on the Azure SQL Database and Log Analytics workspace to set up and view audit logs.
     • Read access to the Log Analytics workspace to run and retrieve queries.
6. Azure Monitor Log Analytics:
   • Familiarize yourself with the Log Analytics query language (Kusto Query Language – KQL), as you will use it to run queries to retrieve user access data.
7. Access to Azure Portal:
   • Ensure you have access to the Azure Portal to navigate to both the Log Analytics workspace and the SQL Database for running and analyzing queries.
8. Time for Data Collection:
   • Make sure that sufficient time has passed since enabling auditing to allow logs to be collected in the Log Analytics workspace.

Query

Below is the Query Details:

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents"
| where TimeGenerated >= ago(30d)
|where action_name_s contains "Authentication"

Explanation

This query is written in Kusto Query Language (KQL) and is used to retrieve SQL security audit events from the Azure Log Analytics workspace. Here’s a detailed explanation of each part of the query:

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents"
| where TimeGenerated >= ago(30d)
| where action_name_s contains "Authentication"

Step-by-Step Breakdown

1. AzureDiagnostics:
   • This is the name of the table in the Log Analytics workspace that stores diagnostic logs for various Azure resources, including SQL databases.
   • It contains different logs and events, such as security audits, performance data, and other metrics from Azure resources.
2. | where Category == "SQLSecurityAuditEvents":
   • The | where clause is used to filter the data.
   • Category == "SQLSecurityAuditEvents" filters the data to include only those entries related to SQL security audit events. These events typically include login attempts, access attempts, authorization changes, and other security-related activities on the Azure SQL Database.
3. | where TimeGenerated >= ago(30d):
   • This filter ensures that only logs generated within the last 30 days are retrieved.
   • TimeGenerated is a timestamp field that indicates when each event was recorded.
   • ago(30d) means “30 days ago.” Therefore, this filter restricts the results to events that occurred in the past 30 days.
4. | where action_name_s contains "Authentication":
   • This filter further narrows down the results to include only those events where the action_name_s field contains the word “Authentication.”
   • The action_name_s field represents the action that was performed, such as login attempts, access attempts, or other authentication-related events.
   • This line ensures that only authentication-related activities (e.g., login attempts, successful or failed authentications) are included in the results.

What the Query Does

The query retrieves authentication-related security events from an Azure SQL Database for the last 30 days. Specifically, it filters the AzureDiagnostics table to include only:

• Events categorized under SQLSecurityAuditEvents
• Events that occurred within the past 30 days
• Events where the action involved an authentication process (e.g., login attempts).

Use Case

This query is useful for auditing and monitoring authentication activities on your Azure SQL Database, helping to track who is attempting to log in, whether the attempts were successful or failed, and providing an overall view of authentication events for security and compliance purposes.