Task: Configure Audit for an Azure SQL Database

Video: Data Auditing in Azure

Estimated time: 15 minutes

Goal

In this task you will provision an Azure SQL database and server. You will configure auditing at the server and database level and confirm that activity is audited.

Pre-requisites

Azure subscription

Requirements

Provision an Azure SQL Database and server
Configure auditing for the database and server
Interact with the database
Verify that activity is audited

Requirement 1: Provision an Azure SQL Database and server

For this requirement you must provision an Azure sql database in a new resource group named task-sqlaudit. You will also provision a storage account to hold the audit data.

Use the following settings for the Azure SQL database:

Setting	Value
Resource group	<New -> task-sqlaudit>
Database name	Production
Server	<New -> unique name>
Admin login	student
Password	<strong password>
Allow Azure services...	Checked
Compute + Storage	Basic

Provision a storage account in the task-sqlaudit resource group.

Requirement 2: Configure auditing for the database and server

For this requirement you will configure auditing for the Production database and its server. You will configure auditing to be written to the storage account provisioned in requirement 1. You will set retention to 10 days.

Requirement 3: Interact with the database

For this requirement you will log into the SQL server and interact with the database using the sqlcmd command line tool. You will create a table, add data and drop a table.

Use cloud shell to establish a sqlcmd session with the Production database and its server:

sqlcmd -S <your server FQDN> -d Production -U student -P <your password>

Within sqlcmd create a table

CREATE TABLE dbo.test(id INT, content VARCHAR(20));
go

Within sqlcmd add data to the table

INSERT INTO dbo.test(id, content) VALUES (1,'One');
INSERT INTO dbo.test(id, content) VALUES (2,'Two');
go

Within sqlcmd drop the table

DROP TABLE dbo.test;
go

Exit sqlcmd:

exit

Requirement 4: Verify that activity is audited

For this requirement you will return to the Azure portal and verify that the activity from requirement 3 has been logged for both the database and the server.

Hint: Return to the Audit view of the database blade to view the audit logs.

Clean up

Delete the task-sqlaudit resource group.

Solution

Having trouble completing this task? View the demonstration video to see how to do it.