This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.
Azure SQL DB Auditing allows Users and organizations to track specific database events like (DDL, DML, TCL, DQL,DCL ,Errors etc.) rather than tracking all of the Database default events. This helps organizations to keep audit imposed only on specific users with predefined Audit actions along with reducing the exponential storage consumptions. The process below provides step by step guide to configure Auditing for specific Events and user lists.
1) Open Azure Cloud shell and Connect to your subscription by using the below PowerShell Cmdlet
set-Azcontext -Subscription <Your subscription ID>
2) Copy and execute the below PowerShell Script, This script will create a PS function “SET-AUDITING” in the current session.
Function Set-Auditing
{
Param
(
[parameter(Mandatory=$true)][string]$ResourceGroup,
[parameter(Mandatory=$true)][string]$SQLServerName,
[parameter(Mandatory=$true)][string]$databasename,
[parameter(Mandatory=$true)][string]$Storageaccount,
[parameter(Mandatory=$true)][INT]$RetentionPeriod
)
##Get Storage resource Id
$StorageId = Get-AzResource -ResourceGroupName $ResourceGroup -Name $Storageaccount |select -expandproperty 'ResourceId'
##Get Actions and users to Audit
[string[]] $_actionlist= @()
[string[]] $_userlist= @()
$_actionlist = READ-HOST "ENTER LIST OF ACTIONS TO AUDIT(atleast One)"
$_userlist =READ-HOST "ENTER LIST OF USERS TO AUDIT (Enter A to track all users)"
$preaction= $_actionlist.Split(',').Split(' ')
$statements = ForEach($rs in $preaction)
{
" "+"statement"+ " " + "like" +" "+"'"+$rs+"%"+"'"+" "
}
$FinalactionStatement = $statements -join("OR")
if($_userlist -eq 'A')
{
$finalstatement = $FinalactionStatement
}
else
{
$preuser= $_userlist.Split(',').Split(' ')
$userstatements = ForEach($rsp in $preuser)
{
" "+"database_principal_name ="+" "+"'"+$rsp+"'"+" "
}
$finaluserstatements = $userstatements -join("OR")
$finalstatement = "("+$FinalactionStatement+")"+" "+"AND"+" "+"("+$finaluserstatements+")"
}
#SET DB AUDITING
Set-AzSqlDatabaseAudit -ResourceGroupName $ResourceGroup -ServerName $SQLServerName -DatabaseName $databasename -BlobStorageTargetState Enabled -StorageAccountResourceId $StorageId -RetentionInDay $RetentionPeriod -PredicateExpression "$finalstatement"
#GET AUDIT STATUS
$ST = get-AzSqlDatabaseAudit -ResourceGroupName $ResourceGroup -ServerName $SQLServerName -DatabaseName $databasename|Select DatabaseName,PredicateExpression,StorageAccountResourceId,RetentionInDays,BlobStorageTargetState
return $ST
}
3) Now Execute the function (SET-AUDITING) by using the below Cmdlet in the same PowerShell Session and provide the parameter needed to set auditing along with retention period. (Example below)
Set-Auditing
Note:
** Azure retention period = 0 , Cities the audit file will remain forever in the storage account
** If you want to track specific action event (Eg: DDL) for all the user , Please type A when it asks to put user lists to audit in the PS execution
**Separate multiple input with just a comma(,) as mentioned below in example ,avoid spaces.
Thank you for helping me testing the script in multiple test cases , Thanks for guidance.
