Implementing Auditing and Threat Detection

Auditing means keeping a permanent, tamper-proof log of every action taken on a database. Think of it as a black box recorder for your data. When you enable auditing in Azure SQL Database or SQL Server, the database engine writes a record every time someone runs a query, changes a table, logs in, or fails to log in. This record includes the exact SQL command, the user’s account name, the time, and the source IP address.

Threat detection is a separate but related feature that scans those logs in real time. It looks for patterns that match known attack signatures: someone trying to brute-force a password (hundreds of failed logins in a minute), SQL injection attempts (special characters or malformed queries), or access from unusual locations (a login from a country where your company has no employees). When it finds a match, it raises an alert — often through Azure Defender for SQL, which can send an email, trigger an automated response, or integrate with security information and event management (SIEM) systems.

The central tool for this in Azure is the SQL auditing feature, which stores logs in an Azure Storage account, a Log Analytics workspace, or an Event Hub. You can choose to log server-level events (like login attempts) or database-level events (like SELECT, INSERT, UPDATE, DELETE). The logs are immutable — once written, they cannot be changed or deleted, even by a database administrator. This is crucial for compliance with regulations like GDPR, HIPAA, or SOC 2, which require you to prove who accessed what data and when.

Why does this matter? Without auditing, if a hacker steals customer credit card numbers, you have no way to trace how they got in. With auditing, you can replay the exact sequence of events. Threat detection adds a pro-active layer: it can stop an attack in progress by alerting you before the damage is done. For example, Azure Defender for SQL can identify anomalous access patterns, like a user who normally logs in from London suddenly connecting from a known malicious IP address in Eastern Europe.

The key components to understand are:

When you set up auditing in the Azure portal, you typically start by going to your SQL server or database, selecting ‘Auditing’ under Security, and turning it on. You then choose a storage destination. The simplest option is to stream logs to an Azure Storage account, but for deeper analysis, you send them to a Log Analytics workspace. Once enabled, all audit records are automatically written. You can query them using Kusto Query Language (KQL) to find, say, all failed logins in the last 24 hours.

For threat detection, you enable Azure Defender for SQL on the server or database level. This activates a set of built-in detection rules. You don’t need to write those rules yourself — Microsoft updates them based on global threat intelligence. However, you can also create custom alert rules using Azure Monitor to match specific patterns relevant to your organisation.

Importantly, auditing and threat detection are not the same thing. Auditing is passive recording; threat detection is active monitoring. You need both for a complete security posture. The exam will test that you know when to use each, how to configure them, and what the default settings are — for example, that auditing is disabled by default, and that threat detection is an add-on feature that requires Azure Defender for SQL.

An IT professional — let’s call her Priya — is the database administrator for a mid-sized healthcare company that handles patient records. The company must comply with HIPAA, which demands that every access to protected health information is logged and reviewable.

Priya’s first job is to enable auditing on all production SQL databases in Azure. She opens the Azure portal, navigates to each SQL server, clicks ‘Auditing’ under Security, and sets it to ‘ON’. She chooses to store the logs in a central Azure Storage account that is locked down with a long retention period — five years, as required by law. She also configures the audit to capture database-level events, because the business only cares about data modifications, not every login attempt.

A week later, a compliance officer asks: ‘Who accessed patient ID 4392 on 12 March?’ Priya runs a KQL query in Log Analytics: AzureDiagnostics | where TimeGenerated between (datetime(2025-03-12) .. datetime(2025-03-13)) and statement_s like ‘%4392%’. She instantly sees that user ‘jsmith’ ran a SELECT query at 2:15 PM from an IP address that belongs to the office network. She reports that it was a legitimate data analyst. Without auditing, she would have had no answer and risked a fine.

Separately, Priya enables Azure Defender for SQL. One afternoon, Azure Defender sends her an email alert: ‘Potential SQL injection attack detected on database ‘PatientsDB’ from IP 203.0.113.42’. She jumps into the portal, sees that the alert includes the time, the query that triggered it (a string with ‘OR 1=1’ — a classic SQL injection attempt), and a recommendation to review firewall rules. She checks the firewall and finds that a developer accidentally added a rule allowing access from a public IP. She removes it immediately. The attack was blocked before any data was exfiltrated.

Priya also uses vulnerability assessment, another feature of Azure Defender. On the first of every month, she runs a scan that reports missing patches, weak passwords, or misconfigured permissions. One scan reveals that a test database still uses the default admin password. She fixes it and writes a policy that all databases must pass vulnerability assessment before going into production.

In her monthly security review, she generates a report from Log Analytics that shows the top ten users by query volume and highlights any failed login attempts over 50 from a single IP. She presents this to the CISO, who uses it to justify additional security training for the team.

The concrete actions Priya takes as an IT professional are: