This post is a part of the "SC-500: Implementing End-to-End Security Controls for Cloud and AI Workloads" Exam Prep Hub.
This topic falls under these sections:
Secure storage, databases, and networking (25–30%)
--> Implement security for databases
--> Configure database auditing for Azure SQL Database and Azure SQL Managed Instance
Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.
Overview
Database auditing records database activity so that organizations can investigate security incidents, identify unauthorized access, support compliance requirements, and understand how data is being used.
For the SC-500 exam, database auditing primarily involves configuring and managing auditing for:
- Azure SQL Database
- Azure SQL Managed Instance
- SQL databases hosted in Azure
- Microsoft Entra authentication and database activity
- Audit destinations and retention
- Audit logs and monitoring
Auditing is different from authentication and authorization:
- Authentication determines who or what is connecting.
- Authorization determines what the principal is allowed to do.
- Auditing records what happened, who performed the action, when it occurred, and other relevant details.
A user might be correctly authenticated and authorized to read a table, but auditing can record that the user actually performed the read operation.
Why Database Auditing Is Important
Database auditing supports several security and governance objectives.
Detecting suspicious activity
Audit records can help identify:
- Repeated failed login attempts
- Access to sensitive tables
- Unexpected changes to database objects
- Changes to permissions or roles
- Unusual administrative activity
- Attempts to access data outside normal business patterns
Supporting compliance
Many regulatory and organizational standards require organizations to maintain evidence of access to sensitive data. Audit logs can help demonstrate:
- Who accessed data
- Which operations were performed
- When the operations occurred
- Whether privileged users changed security settings
- Whether sensitive data was accessed or modified
Investigating security incidents
When an incident occurs, audit logs can help security teams reconstruct events and determine:
- Which account was used
- Which database was accessed
- Which commands were executed
- Whether data was read, changed, or deleted
- Whether permissions were modified
- The approximate time sequence of activity
Establishing accountability
Auditing helps associate database activity with a user, application, service principal, or managed identity. This is especially important when multiple applications or administrators access the same database.
Azure SQL Auditing
Azure SQL auditing tracks database events and writes audit records to a configured destination.
Auditing can be configured at different scopes, depending on the service:
- Azure SQL logical server
- Individual Azure SQL Database
- Azure SQL Managed Instance
- SQL databases hosted by the managed instance
The exact configuration experience and available settings can vary between Azure SQL Database and Azure SQL Managed Instance.
For Azure SQL Database, auditing can generally be configured at the server or database level. A database-level configuration can provide more specific control for an individual database.
For Azure SQL Managed Instance, auditing is configured for the managed instance and can capture activity across the databases hosted by that instance.
Azure SQL Database Auditing
Azure SQL Database auditing records database events for databases hosted on an Azure SQL logical server.
Auditing can be enabled through the Azure portal, Azure PowerShell, Azure CLI, REST APIs, or infrastructure-as-code tools.
At a high level, configuring auditing involves:
- Selecting the SQL server or database.
- Opening the auditing configuration.
- Enabling auditing.
- Selecting an audit destination.
- Configuring retention and related settings.
- Saving the configuration.
- Reviewing the generated audit records.
Auditing can be configured at the server level so that databases inherit the server’s auditing configuration. A database-level configuration can be used when a particular database requires different auditing behavior.
Azure SQL Managed Instance Auditing
Azure SQL Managed Instance provides auditing for database activity across the managed instance.
Because a managed instance can host multiple databases, auditing at the managed-instance level is useful when an organization wants consistent auditing across its database environment.
Auditing can help record activity such as:
- Database connections
- Queries and stored procedure execution
- Data access
- Data changes
- Permission changes
- Schema changes
- Security-related operations
The audit configuration should be reviewed carefully to ensure that the selected events meet the organization’s security and compliance requirements without generating unnecessary volumes of data.
Audit Destinations
Azure SQL auditing supports several destinations. The appropriate destination depends on the organization’s retention, analysis, and monitoring requirements.
Azure Storage
Audit logs can be written to an Azure Storage account.
Azure Storage is useful when an organization needs:
- Long-term retention
- Centralized storage
- Low-cost archival
- Integration with other data-processing tools
- Storage-based compliance evidence
When using Azure Storage, consider:
- Storage account security
- Access control
- Network restrictions
- Encryption
- Retention policies
- Immutability requirements
- Lifecycle management
Audit logs should not be stored in a location where unauthorized users can modify or delete them.
For stronger protection, organizations can use storage security features such as restricted access, role-based access control, and immutable storage where appropriate.
Log Analytics Workspace
Audit logs can be sent to a Log Analytics workspace.
This destination is useful when security teams need to:
- Query audit records
- Correlate database events with other Azure activity
- Build dashboards
- Create alerts
- Investigate incidents
- Use Microsoft Sentinel for security monitoring
Log Analytics is often the most useful destination for operational security monitoring because audit data can be queried using Kusto Query Language.
For example, security teams might use audit data to investigate:
- Access to sensitive databases
- Changes to database permissions
- Unusual administrative activity
- Repeated failed connections
- Unexpected data modification
Event Hubs
Audit logs can also be sent to Azure Event Hubs.
Event Hubs is useful when audit data must be streamed to another system, such as:
- A security information and event management platform
- A security analytics platform
- A custom monitoring application
- A third-party compliance or monitoring solution
Event Hubs is designed for high-throughput event ingestion and streaming rather than long-term log storage by itself.
Choosing a Destination
| Requirement | Suitable destination |
|---|---|
| Long-term archival | Azure Storage |
| Interactive investigation and queries | Log Analytics workspace |
| Streaming audit data to another system | Event Hubs |
| Security analytics and alerting | Log Analytics and Microsoft Sentinel |
| Compliance retention | Azure Storage, often with additional retention controls |
An organization may use more than one destination when it needs both operational monitoring and long-term retention.
Types of Activity That Can Be Audited
The exact audit events available depend on the Azure SQL service and configuration, but auditing can capture several important categories of activity.
Authentication and connection activity
Examples include:
- Successful database connections
- Failed connection attempts
- Authentication-related events
- Connection information
These events can help identify brute-force attempts, misconfigured applications, or unexpected access.
Data access
Examples include:
- Reading data
- Selecting data from sensitive tables
- Executing stored procedures
- Accessing specific database objects
Data-access auditing is particularly important for databases containing:
- Personally identifiable information
- Financial information
- Healthcare information
- Customer records
- Confidential business data
Data changes
Examples include:
- Insert operations
- Update operations
- Delete operations
- Bulk data changes
Auditing data changes can help determine whether records were modified or removed.
Schema changes
Examples include:
- Creating tables
- Altering tables
- Dropping tables
- Creating or modifying stored procedures
- Changing database objects
Schema auditing is useful because unauthorized schema changes can create security vulnerabilities or affect application behavior.
Permission and role changes
Examples include:
- Granting permissions
- Revoking permissions
- Adding users to database roles
- Removing users from database roles
- Changing ownership or security-related settings
These events are important for detecting privilege escalation.
Administrative activity
Examples include:
- Changes to auditing configuration
- Changes to database settings
- Changes to security configuration
- Administrative commands
Administrative auditing helps establish accountability for privileged operations.
Auditing Versus Microsoft Defender for SQL
Azure SQL auditing and Microsoft Defender for SQL serve related but different purposes.
Azure SQL auditing
Auditing primarily records database activity for:
- Investigation
- Compliance
- Accountability
- Historical analysis
- Security monitoring
It answers questions such as:
What activity occurred in the database?
Microsoft Defender for SQL
Microsoft Defender for SQL provides additional security capabilities, such as:
- Threat detection
- Security alerts
- Vulnerability assessment
- Security recommendations
- Identification of suspicious database activity
It answers questions such as:
Does this activity appear suspicious or represent a security risk?
Auditing and Defender for SQL can be used together. Auditing provides detailed activity records, while Defender for SQL can identify and alert on potentially malicious behavior.
Auditing and Microsoft Sentinel
Audit logs can be integrated with Microsoft Sentinel to support centralized security monitoring.
A typical workflow is:
- Enable auditing on Azure SQL Database or Azure SQL Managed Instance.
- Send audit logs to a Log Analytics workspace.
- Connect the workspace to Microsoft Sentinel.
- Create queries and analytics rules.
- Configure alerts and incidents.
- Investigate related activity across Azure and other environments.
For example, Microsoft Sentinel could correlate:
- A suspicious Microsoft Entra sign-in
- A database permission change
- Access to a sensitive table
- Activity from an unusual IP address
- A subsequent data export
This correlation provides more context than reviewing database logs alone.
Retention and Log Management
Audit logs should be retained according to:
- Regulatory requirements
- Organizational policies
- Incident-response requirements
- Legal and contractual obligations
- Storage costs
- Data sensitivity
Retention should be long enough to support investigations and compliance audits.
Important considerations include:
- How long logs are retained
- Whether logs can be deleted by ordinary administrators
- Whether logs are protected from modification
- Whether access to logs is itself audited
- Whether archived logs can be searched or restored
- Whether retention policies apply consistently across databases
Audit logs may contain sensitive information, so they should be protected using appropriate access controls and encryption.
Securing Audit Logs
Audit logs are security evidence and should be protected as carefully as the database itself.
Restrict access
Only authorized personnel should be able to read, export, or delete audit logs.
Use least-privilege access through Microsoft Entra ID and Azure RBAC where supported.
Protect against deletion or modification
Consider:
- Storage immutability
- Resource locks where appropriate
- Restricted administrative access
- Separate security or compliance ownership
- Monitoring of changes to audit configuration
A log that can be easily deleted by the person being investigated provides limited forensic value.
Encrypt audit data
Audit data should be protected using encryption at rest and secure transport.
Azure services generally provide encryption at rest, but organizations must still configure access and key-management controls appropriately.
Monitor auditing configuration
Security teams should monitor changes to:
- Whether auditing is enabled
- Audit destinations
- Retention settings
- Audit policies
- Database-level overrides
- Permissions to audit destinations
An attacker who disables auditing may be attempting to conceal activity.
Configuring Auditing in the Azure Portal
The following is a conceptual configuration process. The exact portal labels may vary as Azure services evolve.
Azure SQL Database
- Open the Azure portal.
- Navigate to the Azure SQL logical server or database.
- Select Auditing under the security-related settings.
- Enable auditing.
- Choose one or more supported destinations.
- Configure the destination details.
- Configure retention or related settings.
- Save the configuration.
- Generate or perform test activity.
- Verify that audit records are being delivered.
When configuring auditing at the server level, review whether individual databases inherit the configuration or override it.
Azure SQL Managed Instance
- Open the Azure portal.
- Navigate to the managed instance.
- Select the auditing configuration.
- Enable auditing.
- Select the destination.
- Configure retention and related settings.
- Save the configuration.
- Verify that activity from the managed instance’s databases is being recorded.
Common Exam Considerations
Server-level versus database-level configuration
A server-level auditing configuration can provide centralized coverage, while a database-level configuration can provide more specific control.
When troubleshooting, determine whether:
- Auditing is enabled at the server level
- The database has its own auditing configuration
- A database-level setting overrides the inherited configuration
- The selected destination is correctly configured
Auditing does not grant access
Enabling auditing does not allow a user to connect to a database or read data.
Authentication and authorization must still be configured separately.
Auditing does not block activity
Auditing records activity. It does not, by itself, prevent a user from executing a query or changing data.
To prevent activity, use controls such as:
- Microsoft Entra authentication
- Azure RBAC
- Database roles and permissions
- Network access controls
- Microsoft Defender for SQL
- Azure Policy
- Microsoft Purview or other data-governance controls
Auditing is not the same as diagnostic logging
Diagnostic settings are used to route platform logs and metrics to destinations such as Log Analytics, Storage, or Event Hubs.
Azure SQL auditing is a database-specific auditing capability. Diagnostic settings may be involved in routing or collecting related logs, but they do not replace the need to configure database auditing appropriately.
Do not collect more data than necessary
Auditing should be designed to meet security and compliance objectives while controlling:
- Storage costs
- Query volume
- Log noise
- Sensitive information exposure
- Operational overhead
A useful audit policy focuses on meaningful events and protects the resulting records.
Best Practices
- Enable auditing for production databases.
- Use Log Analytics when interactive investigation and alerting are required.
- Use Azure Storage for long-term retention and archival.
- Send relevant audit data to Microsoft Sentinel for centralized security monitoring.
- Protect audit destinations with least-privilege access.
- Use retention policies that meet regulatory and organizational requirements.
- Protect logs against unauthorized deletion or modification.
- Monitor changes to auditing configuration.
- Review audit records regularly.
- Correlate audit activity with identity, network, and application logs.
- Use Microsoft Defender for SQL for threat detection in addition to auditing.
- Test auditing after configuration changes.
- Document which events are audited and why.
- Avoid relying on auditing as a substitute for authorization.
- Ensure that audit logs themselves are treated as sensitive data.
Practice Exam Questions
Question 1
An organization needs to record activity performed against an Azure SQL Database so that security analysts can investigate suspicious queries and create alerts. Which destination is the most appropriate?
A. Azure Key Vault
B. Azure Storage only
C. Log Analytics workspace
D. Azure Resource Graph
Correct answer: C
Explanation: A Log Analytics workspace is designed for querying and analyzing log data. It can also be used with Microsoft Sentinel to create alerts and investigate security incidents. Azure Storage is better suited to archival and long-term retention.
Question 2
A company must retain Azure SQL audit records for several years at a relatively low cost. The records must also be protected from unauthorized modification. Which approach is most appropriate?
A. Store the records only in the SQL database being audited
B. Send the records to Azure Storage and configure appropriate retention and immutability controls
C. Send the records only to Azure Event Hubs without any downstream storage
D. Disable auditing after exporting the records once per year
Correct answer: B
Explanation: Azure Storage is appropriate for long-term retention. Additional controls, such as retention policies and immutable storage, can help protect audit records from deletion or modification.
Question 3
Which statement best describes the purpose of Azure SQL auditing?
A. It records database activity for investigation, accountability, and compliance
B. It automatically grants users permission to access database objects
C. It replaces Microsoft Entra authentication
D. It prevents all unauthorized queries from executing
Correct answer: A
Explanation: Auditing records activity that occurs in the database. It does not grant permissions, replace authentication, or automatically block queries.
Question 4
An administrator enables auditing for an Azure SQL Database but users still cannot connect to the database. What is the most likely explanation?
A. Auditing can only be enabled after all users are assigned the Owner role
B. Auditing automatically blocks connections until Microsoft Sentinel is configured
C. Auditing records activity but does not provide authentication or authorization
D. Auditing requires Azure Storage to be configured before any user can connect
Correct answer: C
Explanation: Authentication and authorization are separate from auditing. A user must still have a valid authentication method and sufficient database permissions.
Question 5
A security team wants to correlate Azure SQL activity with Microsoft Entra sign-ins, virtual machine alerts, and other cloud security events. Which solution is most appropriate?
A. Azure Files
B. Microsoft Sentinel connected to a Log Analytics workspace
C. Azure DNS
D. Azure Resource Manager locks only
Correct answer: B
Explanation: Microsoft Sentinel can use Log Analytics data to correlate database audit events with identity, infrastructure, and other security events.
Question 6
An organization wants to investigate whether a privileged administrator changed database permissions. Which type of audit activity is most relevant?
A. Permission and role changes
B. Storage account replication events
C. Virtual network route changes only
D. Azure billing events only
Correct answer: A
Explanation: Permission and role changes can reveal privilege escalation or unauthorized changes to database access.
Question 7
A company configures auditing at the Azure SQL logical server level. One database has different auditing requirements and must use a separate configuration. What should the administrator investigate?
A. Whether the database can override or use a database-level auditing configuration
B. Whether auditing can only be configured at the subscription level
C. Whether the database must be moved to Azure Cosmos DB
D. Whether auditing requires a dedicated virtual machine
Correct answer: A
Explanation: Azure SQL Database auditing can be configured at the server or database level. The administrator should determine whether the database-level configuration provides the required override or separate behavior.
Question 8
Which statement correctly compares Azure SQL auditing and Microsoft Defender for SQL?
A. Auditing blocks threats, while Defender for SQL only stores logs
B. Auditing and Defender for SQL are identical features
C. Auditing records database activity, while Defender for SQL provides additional threat detection and security recommendations
D. Defender for SQL is required before auditing can be enabled
Correct answer: C
Explanation: Auditing provides activity records for investigation and compliance. Microsoft Defender for SQL adds security capabilities such as threat detection, alerts, and vulnerability-related recommendations.
Question 9
An organization sends Azure SQL audit records to Event Hubs. What is the primary reason for selecting Event Hubs?
A. To stream audit events to another monitoring or security system
B. To replace database authentication
C. To provide database table-level permissions
D. To encrypt database columns automatically
Correct answer: A
Explanation: Event Hubs is designed for high-throughput event ingestion and streaming. It can forward audit events to downstream monitoring or security systems.
Question 10
A security team notices that audit records are missing after an administrator changed the auditing configuration. Which action should be performed first?
A. Delete the database and recreate it
B. Disable Microsoft Entra authentication
C. Confirm that auditing is still enabled and verify the configured destination and delivery settings
D. Assign the Security Reader role to every database user
Correct answer: C
Explanation: The first troubleshooting step is to verify the auditing configuration, including whether auditing remains enabled and whether the destination is correctly configured. The team should also verify that the destination is receiving records and that no configuration change disabled or redirected auditing.
Final Exam Point
The key exam distinction is that auditing records database activity, while authentication, authorization, network controls, and threat-detection services determine whether activity should be allowed or considered suspicious.
Go to the SC-500 Exam Prep Hub main page
