Blog Post

Azure Database Support Blog
2 MIN READ

Audit Specific Actions and Users in Azure SQL DB

Swabhiman_Das's avatar
Swabhiman_Das
Former Employee
Nov 08, 2021

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_Rachamim  for guidance. 

 

Updated Nov 08, 2021
Version 1.0

6 Comments

  • grameshnaik's avatar
    grameshnaik
    Copper Contributor

    I trying to change this code , instead of "the list of users to audit", I want "list of users not to audit". your help is appreciated!

    $_userlist =READ-HOST "ENTER LIST OF USERS NOT TO AUDIT
  • Hello Tarell01  , . There is actually no restriction in selecting a storage account from a different resource group but the code flow is written as such it automatically tries to look the storage account in the RG defined in the default variable . May be I can add a new variable to define resource group for Storage account . Thanks so much for your valuable feedback , Really appreciate it

  • Tarell01's avatar
    Tarell01
    Copper Contributor

    Nice, thanks!   

    Just a note.  The Storage account and SQL Server have to be in the same resource group.   I am new to using Powershell functions in Azure and that was something I did not think about.   It gave me an error during execution, saying the storage account id could not be found.