![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(198.4, 0%, 29%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDM%3C/text%3E%3C/svg%3E)
Azure SQL Database
An Azure relational database service.
5,600 questions
The quickest way to create a custom azure policy definition is to use the 'duplicate definition' button in the Azure Portal when you find a built-in policy to use as a 'starting point '.
Here's a built-in that matches your use case described above: Configure SQL servers to have auditing enabled
{
"displayName": "Configure SQL servers to have auditing enabled",
"policyType": "BuiltIn",
"mode": "Indexed",aa
"description": "To ensure the operations performed against your SQL assets are captured, SQL servers should have auditing enabled. This is sometimes required for compliance with regulatory standards.",
"metadata": { "version": "3.0.0", "category": "SQL" },
"parameters": {
"effect": {
"type": "String",
"metadata": {
"displayName": "Effect",
"description": "Enable or disable the execution of the policy"
},
"allowedValues": ["DeployIfNotExists", "Disabled"],
"defaultValue": "DeployIfNotExists"
},
"retentionDays": {
"type": "String",
"metadata": {
"description": "The value in days of the retention period (0 indicates unlimited retention)",
"displayName": "Retention days (optional, 180 days if unspecified)"
},
"defaultValue": "180"
},
"storageAccountsResourceGroup": {
"type": "String",
"metadata": {
"displayName": "Resource group name for storage accounts",
"description": "Auditing writes database events to an audit log in your Azure Storage account (a storage account will be created in each region where a SQL Server is created that will be shared by all servers in that region). Important - for proper operation of Auditing do not delete or rename the resource group or the storage accounts.",
"strongType": "existingResourceGroups"
}
}
},
"policyRule": {
"if": { "field": "type", "equals": "Microsoft.Sql/servers" },
"then": {
"effect": "[parameters('effect')]",
"details": {
"type": "Microsoft.Sql/servers/auditingSettings",
"name": "Default",
"existenceCondition": {
"field": "Microsoft.Sql/auditingSettings.state",
"equals": "Enabled"
},
"roleDefinitionIds": [
"/providers/microsoft.authorization/roleDefinitions/056cd41c-7e88-42e1-933e-88ba6a50c9c3",
"/providers/microsoft.authorization/roleDefinitions/17d1049b-9a84-46fb-8f53-869881c3d3ab"
],
"deployment": {
"properties": {
"mode": "incremental",
"template": {
"$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"serverName": { "type": "string" },
"auditRetentionDays": { "type": "string" },
"storageAccountsResourceGroup": { "type": "string" },
"location": { "type": "string" }
},
"variables": {
"retentionDays": "[int(parameters('auditRetentionDays'))]",
"subscriptionId": "[subscription().subscriptionId]",
"uniqueStorage": "[uniqueString(variables('subscriptionId'), parameters('location'), parameters('storageAccountsResourceGroup'))]",
"locationCode": "[substring(parameters('location'), 0, 3)]",
"storageName": "[tolower(concat('sqlaudit', variables('locationCode'), variables('uniqueStorage')))]",
"createStorageAccountDeploymentName": "[concat('sqlServerAuditingStorageAccount-', uniqueString(variables('locationCode'), parameters('serverName')))]",
"serverResourceGroup": "[resourceGroup().name]",
"auditDeployName": "[uniqueString(variables('subscriptionId'), parameters('location'), resourceGroup().name, parameters('serverName'), deployment().name)]"
},
"resources": [
{
"apiVersion": "2017-05-10",
"name": "[variables('createStorageAccountDeploymentName')]",
"type": "Microsoft.Resources/deployments",
"resourceGroup": "[parameters('storageAccountsResourceGroup')]",
"properties": {
"mode": "Incremental",
"expressionEvaluationOptions": { "scope": "inner" },
"parameters": {
"location": { "value": "[parameters('location')]" },
"storageName": { "value": "[variables('storageName')]" },
"auditDeployName": {
"value": "[variables('auditDeployName')]"
},
"serverResourceGroup": {
"value": "[variables('serverResourceGroup')]"
},
"createStorageAccountDeploymentName": {
"value": "[variables('createStorageAccountDeploymentName')]"
},
"retentionDays": {
"value": "[variables('retentionDays')]"
},
"storageAccountsResourceGroup": {
"value": "[parameters('storageAccountsResourceGroup')]"
},
"serverName": { "value": "[parameters('serverName')]" }
},
"template": {
"$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"location": { "type": "string" },
"storageName": { "type": "string" },
"auditDeployName": { "type": "string" },
"serverResourceGroup": { "type": "string" },
"createStorageAccountDeploymentName": {
"type": "string"
},
"retentionDays": { "type": "int" },
"storageAccountsResourceGroup": { "type": "string" },
"serverName": { "type": "string" }
},
"resources": [
{
"type": "Microsoft.Storage/storageAccounts",
"apiVersion": "2021-04-01",
"name": "[parameters('storageName')]",
"location": "[parameters('location')]",
"sku": { "name": "Standard_LRS" },
"kind": "BlobStorage",
"tags": {
"createdBy": "Azure Policy - Configure SQL servers to have auditing enabled"
},
"properties": {
"accessTier": "Hot",
"supportsHttpsTrafficOnly": true,
"allowBlobPublicAccess": false
}
},
{
"apiVersion": "2017-05-10",
"dependsOn": [
"[resourceId(parameters('storageAccountsResourceGroup'), 'Microsoft.Storage/storageAccounts', parameters('storageName'))]"
],
"name": "[parameters('auditDeployName')]",
"type": "Microsoft.Resources/deployments",
"resourceGroup": "[parameters('serverResourceGroup')]",
"properties": {
"mode": "Incremental",
"template": {
"$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"resources": [
{
"name": "[concat(parameters('serverName'), '/Default')]",
"type": "Microsoft.Sql/servers/auditingSettings",
"apiVersion": "2017-03-01-preview",
"properties": {
"state": "Enabled",
"storageEndpoint": "[reference(resourceId(parameters('storageAccountsResourceGroup'),'Microsoft.Storage/storageAccounts', parameters('storageName'))).primaryEndpoints.blob]",
"storageAccountAccessKey": "[listKeys(resourceId(parameters('storageAccountsResourceGroup'), 'Microsoft.Storage/storageAccounts', parameters('storageName')), '2017-06-01').keys[0].value]",
"retentionDays": "[parameters('retentionDays')]",
"storageAccountSubscriptionId": "[subscription().subscriptionId]",
"isStorageSecondaryKeyInUse": false
}
}
]
}
}
}
]
}
}
}
]
},
"parameters": {
"serverName": { "value": "[field('name')]" },
"auditRetentionDays": {
"value": "[parameters('retentionDays')]"
},
"storageAccountsResourceGroup": {
"value": "[parameters('storageAccountsResourceGroup')]"
},
"location": { "value": "[field('location')]" }
}
}
}
}
}
}
}