Audit Specific Actions and Users in Azure SQL DB

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 @Deepjoy   for helping me testing the script in multiple test cases , Thanks @Yochanan_MSFT  for guidance. 

 

Posted at https://sl.advdat.com/3mSRB2Y