Creating an ARM template that will enable SQL Auditing
I am going to show you how to retro fit your SQL Server ARM template to add SQL Auditing.
You start off by adding the following code to the “Parameters” (the first part of your ARM template) section of your existing ARM template for your SQL Server.
"LogAnalyticsWorkspaceName": {
"type": "string",
"defaultValue": "Log Analytics name here"
},
"isMSDevOpsAuditEnabled": {
"type": "bool",
"defaultValue": false
},
"LogAnalyticsResourceGroup": {
"type": "string",
"defaultValue": "Your RG here"
},
"EnableDiagnostics":{
"type": "bool",
"defaultValue": true
}
The next thing is putting a variable in your “Variable” area. Now this part is not totally necessary, but it may spark some ideas for you going forward for using it in other ways
"variables":{
"diagnosticSettingsName": "SQLSecurityAuditEvents22_somerandomnamehere"
}
You will use this varialbe in the next secion of code.
Adding code to the main Resource section
Usually, if you are familiar with ARM templates you know that each block in the “Resource” section is usually preceded by “type”. What you need to do is look out for the
“type”: “Microsoft.Sql/servers”,
As I am so kind, I have put the whole code here for the SQL Server part of this template (which sits inside the main Resource section of the template).
Remember this still need to be nested inside the FIRST Resource section. You will have two Resource sections. One main one, then one nested inside the SQL Server bit of code
{
"type": "Microsoft.Sql/servers",
"apiVersion": "2021-08-01-preview",
"name": "[parameters('sqlServerName')]",
"location": "[parameters('location')]",
"properties": {
"administratorLogin": "[parameters('administratorLogin')]",
"administratorLoginPassword": "[parameters('administratorLoginPassword')]"
},
"identity": {
"type": "SystemAssigned"
},
"resources": [
{
"type": "databases",
"apiVersion": "2017-03-01-preview",
"location": "[parameters('location')]",
"dependsOn": [
"[parameters('sqlServerName')]"
],
"name": "master",
"properties": {}
},
{
"condition": "[parameters('EnableDiagnostics')]",
"type": "databases/providers/diagnosticSettings",
"name": "[concat('master/microsoft.insights/',variables('diagnosticSettingsName'))]",
"dependsOn": [
"[parameters('sqlServerName')]",
"[resourceId('Microsoft.Sql/servers/databases', parameters('sqlServerName'), 'master')]"
],
"apiVersion": "2017-05-01-preview",
"properties": {
"name": "[variables('diagnosticSettingsName')]",
"workspaceId": "[resourceId(parameters('SubscriptionID'), parameters('LogAnalyticsResourceGroup'), 'Microsoft.OperationalInsights/workspaces',parameters('LogAnalyticsWorkspaceName'))]",
"logs": [
{
"category": "SQLSecurityAuditEvents",
"enabled": true,
"retentionPolicy": {
"days": 0,
"enabled": false
}
},
{
"condition": "[parameters('isMSDevOpsAuditEnabled')]",
"category": "DevOpsOperationsAudit",
"enabled": true,
"retentionPolicy": {
"days": 0,
"enabled": false
}
}
]
}
},
{
"apiVersion": "2017-03-01-preview",
"type": "auditingSettings",
"name": "DefaultAuditingSettings",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers/', parameters('sqlServerName'))]"
],
"properties": {
"State": "Enabled",
"isAzureMonitorTargetEnabled": true
}
},
{
"condition": "[parameters('isMSDevOpsAuditEnabled')]",
"type": "devOpsAuditingSettings",
"apiVersion": "2020-08-01-preview",
"name": "Default",
"dependsOn": [ "[parameters('sqlServerName')]" ],
"properties": {
"State": "Enabled",
"isAzureMonitorTargetEnabled": true
}
}
]
},Conclusion
And that is it! Simple. But remember you must have an existing Log Analytics workspace. This is not an ARM template that will deploy a Log Analytics resource, it’s just part of the ARM template for your SQL Server.
When it comes to SQL dedicated pools its slightly different. More to come on this in a later blog.
Keep cool. Clint