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

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 https://docs.microsoft.com/en-us/azure/sql-database/sql-database-auditing-get-started and https://docs.microsoft.com/en-us/azure/sql-database/sql-database-threat-detection-get-started 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: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-auditing#subheading-8

{

"$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