Audit Specific Actions and Users in Azure SQL DB

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

 

 ppp.png

 

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.

 

ggg.png

 

Thank you    for helping me testing the script in multiple test cases , Thanks   for guidance. 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.