You can use Azure SQL’s native auditing capabilities to track database events and writes to the audit logs. The steps below guide you through the process:
- In the Azure portal, navigate to the SQL servers page, select your server, and find the “Auditing” tab under “Security.”
- Set the “Auditing” status to “On.”
- In the “Target” box, select “Log Analytics Workspace” and choose the desired workspace.
- Save the changes.
With these settings, server events are now tracked and stored. The Audit logs contain useful information like who performed the operation, the date and time of the operation, and more.
Part 2: Configuring Database Audits
Database auditing involves tracking and monitoring events on your database. Azure SQL Database auditing is simple to enable:
- Navigate to your Azure SQL Database in the Azure portal, and under “Security,” find the “Auditing” tab.
- Turn the auditing status “On,” and select your preferred storage account or Log Analytics workspace where the logs will be retained.
- Save the changes.
Now, all the activities carried out on the database will be logged and stored in your specified location.
Part 3: Configuring Audit Actions
To demonstrate, let’s pretend that you’d like to monitor UPDATE actions against a specific table in your database. You can follow these steps:
- In the Microsoft SQL Server Management Studio, connect to your database, and expand Security.
- Right-click on Audits and select ‘New Audit…’
- Set the Audit name, Queue delay, Audit destination, and File path.
- Right-click on the Audit you just created, and select ‘New Audit Specification.’
- Set the Specification name, Audit action type (SELECT, UPDATE, DELETE, EXECUTE), target object and Object schema.
- Enable the Audit Specification.
Here is an example code snippet:
SQL Server Audit Specification Setting:
CREATE SERVER AUDIT SPECIFICATION [Audit-Spec] FOR SERVER AUDIT [ServerAudit] ADD (DATABASE_OBJECT_CHANGE_GROUP), ADD (DATABASE_PERMISSION_CHANGE_GROUP), ADD (FAILED_LOGIN_GROUP); ALTER SERVER AUDIT SPECIFICATION [Audit-Spec] WITH (STATE = ON);
SQL Database Audit Specification Setting:
CREATE DATABASE AUDIT SPECIFICATION [Audit-Spec] FOR SERVER AUDIT [ServerAudit] ADD (SELECT, UPDATE ON SCHEMA::[dbo] BY [public]); ALTER DATABASE AUDIT SPECIFICATION [Audit-Spec] WITH (STATE = ON);
Now, your server and database audits are fully set up. The audit logs will contain valuable insights that can be used for threat detection, ensuring compliance, and identifying anomalies.
Properly configuring your server and database audits forms a significant part of administering Microsoft Azure SQL solutions. Understanding and implementing these steps is an important prerequisite for the DP-300 Exam. Further, optimizing these audits will help you maintain a higher level of security and performance on your databases and servers.
Practice Test
True/False: Configuring server and database audits is essential to understand the happenings on a server and database system.
- True
- False
Answer: True
Explanation: Audits provide information about server and database operations, pinpoint issues, and help in maintaining security and compliance.
Which of the following is not a part of the Azure SQL Database auditing logs?
- a) Database Events
- b) Database Name
- c) Failed Login Attempts
- d) User ID
Answer: d) User ID
Explanation: User ID is not part of the SQL Database auditing logs.
Multiple select: Which of the following are the main ways to configure audits in Azure SQL Database?
- a) Configuration Manager
- b) Azure Portal
- c) SQL Server Management Studio
- d) Transact-SQL
Answer: b) Azure Portal, c) SQL Server Management Studio, d) Transact-SQL.
Explanation: Configuring audits can be done through Azure Portal, SQL Server Management Studio and Transact-SQL. Azure Configuration Manager is not used for this purpose.
True/False: The server audit specification is created by default when you enable Azure SQL Database Auditing.
- True
- False
Answer: False
Explanation: In Azure SQL, a database audit specification must be explicitly created and enabled. Otherwise, no audit will be captured.
Single select: Which data protection standard’s compliance can be achieved by using Azure SQL Server Auditing?
- a) GDPR
- b) ISO 27001
- c) Both
- d) Neither
Answer: c) Both
Explanation: Azure SQL Server Auditing helps achieve compliance with a broad range of regulatory standards such as GDPR and ISO
True/False: The standard tier of Blob storage supports the 180-day retention period for Azure SQL database audit logs.
- True
- False
Answer: False
Explanation: Blob storage does not support the 180-day retention period. It can be retained indefinitely or for a user-specified period.
Single select: Which cloud service is used by Azure SQL Auditing to stream the audit logs in real-time?
- a) Azure DevOps
- b) Azure Event Hub
- c) Azure Logic Apps
- d) Azure Data Factory
Answer: b) Azure Event Hub
Explanation: Azure SQL Auditing can use Azure Event Hubs to stream the audit logs in real-time.
Multiple select: What actions can be audited in Azure SQL database?
- a) SQL statements being executed
- b) Logons and logoffs
- c) Changes to the schema
- d) Changes to the hardware configuration
Answer: a) SQL statements being executed, b) Logons and logoffs, c) Changes to the schema
Explanation: Azure SQL database can audit SQL statements being run, logons/logoffs, and changes to the schema. Changes to hardware configuration are not audited at the SQL level.
True/False: Azure SQL Auditing service retains the logs for an unlimited duration.
- True
- False
Answer: False
Explanation: The duration of log retention in Azure SQL Auditing service depends on the pricing tier.
Single select: Where can you store your audit logs using Azure SQL Auditing?
- a) Log Analytics
- b) Azure Storage Account
- c) Event Hubs
- d) All of the above
Answer: d) All of the above
Explanation: Azure SQL Auditing allows the storage of audit logs in Log Analytics, Azure Storage Account, and Event Hubs.
Interview Questions
What is the Azure SQL Database Audit?
Azure SQL Database Audit is used to track database events and write them to an audit log in Azure Storage Account, Log Analytics workspace, or Event Hubs.
How is Server-level auditing different from Database-level auditing in Azure SQL?
Server-level auditing tracks database events for the whole server, and applies to all databases on the server. Database-level auditing applies to a specific database and can track database specific actions.
Which protocol is used to write audit logs in Azure SQL Database?
The HTTPS protocol is used to write audit logs in Azure SQL Database.
What types of actions can be audited in Azure SQL Database?
Auditable actions in Azure SQL Database include Data Manipulation Language (DML) operations such as SELECT, INSERT, UPDATE, DELETE and Data Definition Language (DDL) operations like CREATE, ALTER, DROP, etc.
How can you set up Azure SQL Database Auditing?
Azure SQL Database Auditing can be set up using Azure portal, PowerShell script, or T-SQL commands.
What is the purpose of the Azure SQL Server Auditing policy?
Azure SQL Server Auditing policy defines the actions and events that will be logged by the audit. It determines which user activities and errors will be documented.
What should you do if you want to retain your Azure SQL Database Audit logs for a longer period?
You can increase the retention period for the audit logs in the Storage Account settings. The retention period can be up to 365 days.
How can you integrate Azure SQL Database Auditing with Azure Monitor logs?
You can route the audit logs to a Log Analytics workspace by setting the “Data route storage” option in the Azure portal.
Can you disable Azure SQL database auditing?
Yes, Azure SQL Database Auditing can be disabled by finding the database in the Azure portal, selecting “Auditing” under “Security”, and switching the Auditing toggle to off.
Is it possible to export the Azure SQL Database Auditing settings for use on another server?
Yes, you can export the auditing settings as a script and use it to configure auditing on another Azure SQL server.
Can Azure SQL Database Audit track changes made to the database schema?
Yes, Azure SQL Database Audit can track changes made to the database schema by auditing the DDL operations.
What is Blob Auditing in Azure SQL database?
Blob Auditing is an older Azure SQL database auditing feature which writes the database events to an Azure Storage Account as text files.
How to enable auditing at the server level in Azure SQL?
You can enable server-level auditing in the Azure portal by selecting the SQL server resource, then go to “Auditing” under “Security”, and enable it.
What are the prerequisites for setting up Azure SQL Database Auditing?
You must have the necessary permissions for the Azure SQL Database and the storage account, Log Analytics workspace, or Event Hub where the audit logs will be written.
Is it possible to search and analyze Azure SQL Database audit logs?
Yes, you can use Log Analytics (part of Azure Monitor) to search and analyze the audit logs.
extutorials.com