Sample - Audit SQL DB level audit setting

This policy audits SQL database audit settings if those settings do not match a specified setting. You specify a value that indicates whether audit settings should be enabled or disabled.

If you don't have an Azure subscription, create a free account before you begin.

Sample template

{
   "properties": {
      "displayName": "Audit SQL DB Level Audit Setting",
      "description": "Audit DB level audit setting for SQL databases",
      "parameters": {
         "setting": {
            "type": "String",
            "allowedValues": [
               "enabled",
               "disabled"
            ],
            "metadata": {
               "displayName": "Audit Setting"
            }
         }
      },
      "policyRule": {
         "if": {
            "allOf": [
               {
                  "field": "type",
                  "equals": "Microsoft.SQL/servers/databases"
               },
               {
                  "field": "name",
                  "notEquals": "master"
               }
            ]
         },
         "then": {
            "effect": "auditIfNotExists",
            "details": {
               "type": "Microsoft.SQL/servers/databases/auditingSettings",
               "name": "default",
               "existenceCondition": {
                  "field": "Microsoft.Sql/auditingSettings.state",
                  "equals": "[parameters('setting')]"
               }
            }
         }
      }
   }
}

You can deploy this template using the Azure portal, with PowerShell or with the Azure CLI.

Deploy with the portal

Deploy the Policy sample to Azure

Deploy with PowerShell

This sample requires Azure PowerShell. Run Get-Module -ListAvailable Az to find the version. If you need to install or upgrade, see Install Azure PowerShell module.

Run the Connect-AzAccount cmdlet to connect to Azure.

$definition = New-AzPolicyDefinition -Name "audit-sql-db-auditing" -DisplayName "Audit SQL DB Level Audit Setting" -description "Audit DB level audit setting for SQL databases" -Policy 'https://raw.githubusercontent.com/Azure/azure-policy/master/samples/SQL/audit-sql-db-auditing/azurepolicy.rules.json' -Parameter 'https://raw.githubusercontent.com/Azure/azure-policy/master/samples/SQL/audit-sql-db-auditing/azurepolicy.parameters.json' -Mode All
$definition
$assignment = New-AzPolicyAssignment -Name <assignmentname> -Scope <scope>  -setting <Audit Setting> -PolicyDefinition $definition
$assignment

Clean up PowerShell deployment

Run the following command to remove the resource group, VM, and all related resources.

Remove-AzResourceGroup -Name myResourceGroup

Deploy with Azure CLI

To run this sample, install the latest version of the Azure CLI. To start, run az login to create a connection with Azure.

Samples for the Azure CLI are written for the bash shell. To run this sample in Windows PowerShell or Command Prompt, you may need to change elements of the script.

az policy definition create --name 'audit-sql-db-auditing' --display-name 'Audit SQL DB Level Audit Setting' --description 'Audit DB level audit setting for SQL databases' --rules 'https://raw.githubusercontent.com/Azure/azure-policy/master/samples/SQL/audit-sql-db-auditing/azurepolicy.rules.json' --params 'https://raw.githubusercontent.com/Azure/azure-policy/master/samples/SQL/audit-sql-db-auditing/azurepolicy.parameters.json' --mode All

az policy assignment create --name <assignmentname> --scope <scope> --policy "audit-sql-db-auditing"

Clean up Azure CLI deployment

Run the following command to remove the resource group, VM, and all related resources.

az group delete --name myResourceGroup --yes

Next steps