Updated ARM Template to deploy server with Auditing and Threat Detection turned ON

[Azure-DB-Support-Team]

First published on MSDN on Jun 26, 2018
[2018-07-31] Note: new template version is now available. It includes 3 changes:

• There is no reason to enable both server policy and database policy. It is enough to enable only server policy.


• No need to supply storage account for Threat Detection policy. It is needed only for auditing


• Use the updated API version for auditing and Threat Detection (2017-03-01-preview)

I would like to share an example of a template that can be used to deploy server with multiple databases and to turn ON Auditing and Threat Detection at server and individual database levels.  Please be aware that when server - level auditing is enabled, it is applied to all databases on this server. You can also enable database - level auditing, for example, if different storage account or retention period should be used for a specific database. For more details about server and database - level auditing policy please refer to the following article: Define server-level vs. database-level auditing policy

{

"$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",



"contentVersion": "1.0.0.0",



"parameters": {



"databaseserver": {



"type": "string"

},

"databaselist": {



"type": "array",



"metadata": {

}

},

"firewallruleList": {



"type": "array",



"metadata": {

}

},

"sqladminpassword": {



"type": "securestring"

},

"emailaddresses": {



"type": "array",



"metadata": {

}

}

},

"variables": {



"databaseServerName": "[toLower(parameters('databaseServer'))]",



"databaseServerLocation": "West US",



"defaultSecondaryLocation": "East US",



"databaseServerAdminLogin": "Standard",



"databaseServerAdminLoginPassword": "[parameters('sqlAdminPassword')]",



"storageAccountName": "[toLower(parameters('databaseServer'))]"

},

"resources": [

{

"type": "Microsoft.Storage/storageAccounts",



"name": "[variables('storageAccountName')]",



"apiVersion": "2016-01-01",



"location": "[resourceGroup().location]",



"sku": {



"name": "Standard_LRS"

},

"kind": "Storage",



"properties": {

}

},

{

"name": "[variables('databaseServerName')]",



"type": "Microsoft.Sql/servers",



"location": "[variables('databaseServerLocation')]",



"apiVersion": "2014-04-01-preview",



"dependsOn": [ ],



"tags": {



"DisplayName": "[variables('databaseServerName')]"

},

"properties": {



"administratorLogin": "[variables('databaseServerAdminLogin')]",



"administratorLoginPassword": "[variables('databaseServerAdminLoginPassword')]",



"version": "12.0"

},

"resources": [

{

"apiVersion": "2017-03-01-preview",



"type": "auditingSettings",



"name": "DefaultAuditingSettings",



"dependsOn": [



"[variables('databaseServerName')]",



"[concat('Microsoft.Storage/storageAccounts/', variables('storageAccountName'))]",



"DatabaseLoop"

],

"properties": {



"State": "Enabled",



"storageEndpoint": "[concat('https://', variables ('storageAccountName'), '.blob.core.windows.net/')]",



"storageAccountAccessKey": "[listKeys(resourceId('Microsoft.Storage/storageAccounts', variables('storageAccountName')), providers('Microsoft.Storage', 'storageAccounts').apiVersions[0]).keys[0].value]",



"storageAccountSubscriptionId": "[subscription().subscriptionId]",



"retentionDays": 0,



"auditActionsAndGroups": null,



"isStorageSecondaryKeyInUse": false

}

},

{

"apiVersion": "2017-03-01-preview",



"type": "securityAlertPolicies",



"name": "DefaultSecurityAlert",



"dependsOn": [



"[variables('databaseServerName')]"

],

"properties": {



"state": "Enabled",



"disabledAlerts": [],



"emailAddresses": "[parameters('emailaddresses')]",



"emailAccountAdmins": true

}

}

]

},

{

"type": "Microsoft.Sql/servers/firewallrules",



"name": "[concat(variables('databaseServerName'), '/', parameters('firewallRuleList')[copyIndex()].name)]",



"apiVersion": "2014-04-01-preview",



"location": "[variables('databaseServerLocation')]",



"properties": {



"startIpAddress": "[parameters('firewallRuleList')[copyIndex()].startIpAddress]",



"endIpAddress": "[parameters('firewallRuleList')[copyIndex()].endIpAddress]"

},

"resources": [ ],



"dependsOn": [



"[concat('Microsoft.Sql/servers/', variables('databaseServerName'))]"

],

"copy": {



"name": "FirewallLoop",



"count": "[length(parameters('firewallRuleList'))]"

}

},

{

"apiVersion": "2014-04-01-preview",



"type": "Microsoft.Sql/servers/databases",



"copy": {



"name": "DatabaseLoop",



"count": "[length(parameters('databaseList'))]"

},

"dependsOn": [



"[concat('Microsoft.Sql/servers/', variables('databaseServerName'))]"

],

"location": "[variables('databaseServerLocation')]",



"name": "[concat(variables('databaseServerName'), '/', string(parameters('databaseList')[copyIndex()].databaseName))]",



"properties": {



"collation": "[parameters('databaseList')[copyIndex()].collation]",



"edition": "[parameters('databaseList')[copyIndex()].databaseEdition]",



"maxSizeBytes": "[parameters('databaseList')[copyIndex()].maxSizeBytes]"

},

"tags": {



"DisplayName": "[variables('databaseServerName')]"

},

"resources": [

{

"name": "current",



"type": "transparentDataEncryption",



"dependsOn": [



"[parameters('databaseList')[copyIndex()].databaseName]"

],

"location": null,



"apiVersion": "2014-04-01-preview",



"properties": {



"status": "Enabled"

}

}

]

}

],

"outputs": { }

}



Have a nice day!

Olga