Azure SQL Database - Can't deploy Auditing using ARM Template

Question

I'm crafting an ARM Template for my Azure SQL Server deployments. One part that I am including is configuring Auditing to a Storage Account. I followed the template on GitHub to use as my starting point. I also configure Vulnerability Assessments to a second Storage Account.

Each Storage Account has had the System Identity for the SQL Server added as "Storage Blob Contributor" as per the guidance.

When I deploy the template, the Vulnerability Assessment is fully deployed without issues, but the Audit consistently fails with the error:

{
    "status": "Failed",
    "error": {
        "code": "ResourceDeploymentFailure",
        "message": "The resource operation completed with terminal provisioning state 'Failed'.",
        "details": [
            {
                "code": "BlobAuditingInsufficientStorageAccountPermissions",
                "message": "Insufficient read or write permissions on storage account 'mystorageaccount'. Add permissions to the server Identity to the storage account."
            }
        ]
    }
}

I have repeated this in different regions and different subscriptions and even a different tenant. Always the same issue.

If I retry using the Portal - it adds the Storage Account without error.

If I use Set-AzSqlServerAudit -ResourceGroupName MyRG -ServerName $sqlServer -BlobStorageTargetState Enabled -StorageAccountResourceId $(Get-AzStorageAccount -ResourceGroupName MyRG -Name $auditStorageName).Id -RetentionInDays 180 then this succeeds.

Oddly, for the Vulnerability Assessments, if I use Enable-AzSqlServerAdvancedDataSecurity then this fails.

Answer 1

I have a similar issue. I have 4-5 resources, one of them is SQL DB. I created a template ARM file. it has a property called "vulnerabilityAssessments_Default_storageContainerPath" that is used in the below section of template. When I want to deploy. I am asked for this, but I didn't enable VA for existing DB, and don't want to enable VA for my next DB(resulted from the deployment]. I tried to remove both the parameter , and the corresponding section. Still got error and couldn't deploy the template.

       {
            "type": "Microsoft.Sql/servers/vulnerabilityAssessments",
            "apiVersion": "2024-05-01-preview",
            "name": "[concat(parameters('servers_server_2_name'), '/Default')]",
            "dependsOn": [
                "[resourceId('Microsoft.Sql/servers', parameters('servers_server_2_name'))]"
            ],
            "properties": {
                "recurringScans": {
                    "isEnabled": false,
                    "emailSubscriptionAdmins": true
                },
                "storageContainerPath": "[parameters('vulnerabilityAssessments_Default_storageContainerPath')]"
            }
        }, 

Answer 2

Hi @AnuragSharma-MSFT I am facing the same issue as Martin had mentioned and in my case the Storage account is already provisioned and is inside Vnet with Azure services access allowed. Exact samething is happening basically thru ARM template I cant set Auditing settings for the SQL server but thru portal it works. I get same error as mentioned by Martin, following is the ARMT template, I am using, appreciate your help:

{
    "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {
        "serverName": {
            "type": "string",
            "metadata": {
                "description": "The name of the new database server to create."
            }
        },
        "location": {
            "type": "string",
            "metadata": {
                "description": "The location of the database server."
            }
        },
        "administratorLogin": {
            "type": "string",
            "metadata": {
                "description": "The account name to use for the database server administrator."
            }
        },
        "administratorLoginPassword": {
            "type": "securestring",
            "metadata": {
                "description": "The password to use for the database server administrator."
            }
        },
        "storageAccountName": {
            "type": "string",
            "metadata": {
                "description": "The name of the new storage account to create."
            }
        },
        "emailAddresses": {
            "type": "string",
            "defaultValue": "",
            "metadata": {
                "description": "Email address for alerts."
            }
        },
        "privateEndpointName": {
            "type": "string"
        },
        "vnetName": {
            "type": "string"
        },
        "vnetRg": {
            "type": "string",
            "metadata": {
                "description": "Resource Group Name of VNet"
            }
        },
        "subscriptionId": {
            "type": "string",
            "metadata": {
                "description": "Subscription ID for your deployment."
            }
        },
        "subnet1Name": {
            "type": "string"
        },
        "dbName": {
            "type": "string",
            "metadata": {
                "description": "The name of the database to create."
            }
        },
        "skuName": {
            "type": "string"
        },
        "skuTier": {
            "type": "string"
        },
        "collation": {
            "type": "string"
        },
        "maxSizeBytes": {
            "type": "int"
        },
        "zoneRedundant": {
            "type": "bool"
        },
        "licenseType": {
            "type": "string"
        },
        "readScaleOut": {
            "type": "string"
        },
        "requestedBackupStorageRedundancy": {
            "type": "string"
        },
        "enableSqlLedger": {
            "type": "bool"
        },
        "workspaceName": {
            "type": "string",
            "defaultValue": ""
        },
        "workspaceRgName": {
            "type": "string",
            "defaultValue": ""
        },
        "storageAccountRgName": {
            "type": "string",
            "defaultValue": ""
        },
        "dsStorageAccountName": {
            "type": "string",
            "defaultValue": ""
        },
        "serviceBusRuleId": {
            "type": "string",
            "defaultValue": ""
        },
        "eventHubAuthorizationRuleId": {
            "type": "string",
            "defaultValue": ""
        },
        "eventHubName": {
            "type": "string",
            "defaultValue": ""
        },
        "diagnosticSettingsEnabled": {
            "type": "bool",
            "defaultValue": true
        },
        "BUSINESS-OWNER": {
            "type": "string"
        },
        "COST-CENTER": {
            "type": "int"
        },
        "LIFECYCLE": {
            "type": "string"
        },
        "APPLICATION": {
            "type": "string"
        },
        "PROJECT-CODE": {
            "type": "string"
        },
        "TECHNICAL-OWNER": {
            "type": "string"
        },
        "GL-CODE": {
            "type": "string"
        }
    },
    "variables": {
        "tags": {
            "BUSINESS-OWNER": "[parameters('BUSINESS-OWNER')]",
            "COST-CENTER": "[parameters('COST-CENTER')]",
            "LIFECYCLE": "[parameters('LIFECYCLE')]",
            "APPLICATION": "[parameters('APPLICATION')]",
            "PROJECT-CODE": "[parameters('PROJECT-CODE')]",
            "TECHNICAL-OWNER": "[parameters('TECHNICAL-OWNER')]",
            "GL-CODE": "[parameters('GL-CODE')]"
        },
        "StorageBlobContributor":"[subscriptionResourceId('Microsoft.Authorization/roleDefinitions', 'ba92f5b4-2d11-453d-a403-e96b0029c9fe')]",
        "uniqueRoleGuid":"[guid(resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName')), variables('storageBlobContributor'), resourceId('Microsoft.Sql/servers', parameters('serverName')))]",
        "databaseName": "[concat(parameters('serverName'),'/',parameters('dbName'))]",
        "workspaceId": "[resourceid(parameters('workspaceRgName'), 'Microsoft.OperationalInsights/workspaces/', parameters('workspaceName'))]",
        "maintenanceConfigurationId"  : "[concat('/subscriptions/', parameters('subscriptionId'), '/providers/Microsoft.Maintenance/publicMaintenanceConfigurations/SQL_Default')]",        
        "dsStorageAccountId": "[resourceId(parameters('storageAccountRgName'),'Microsoft.Storage/storageAccounts/', parameters('storageAccountName'))]"
    },
    "resources": [
        {
            "type": "Microsoft.Sql/servers",
            "apiVersion": "2020-02-02-preview",
            "name": "[parameters('serverName')]",
            "location": "[parameters('location')]",
            "tags": "[variables('tags')]",
            "kind": "v12.0",
            "identity": {
            "type": "SystemAssigned"
            },
            "properties": {
                "administratorLogin": "[parameters('administratorLogin')]",
                "administratorLoginPassword": "[parameters('administratorLoginPassword')]",
                "version": "12.0",
                "minimalTlsVersion": "1.2",
                "publicNetworkAccess": "Disabled"
            },
            "resources": [
                {
                    "type": "Microsoft.Sql/servers/databases",
                    "apiVersion": "2020-02-02-preview",
                    "name": "[variables('databaseName')]",
                    "location": "[parameters('location')]",
                    "dependsOn": [
                        "[resourceId('Microsoft.Sql/servers', parameters('serverName'))]"
                    ],
                    "tags": "[variables('tags')]",
                    "sku": {
                        "name": "[parameters('skuName')]",
                        "tier": "[parameters('skuTier')]"
                    },
                    "properties": {
                        "collation": "[parameters('collation')]",
                        "maxSizeBytes": "[parameters('maxSizeBytes')]",
                        "zoneRedundant": "[parameters('zoneRedundant')]",
                        "licenseType": "[parameters('licenseType')]",
                        "readScale": "[parameters('readScaleOut')]",
                        "requestedBackupStorageRedundancy": "[parameters('requestedBackupStorageRedundancy')]",
                        "isLedgerOn": "[parameters('enableSqlLedger')]",
                        "maintenanceConfigurationId": "[variables('maintenanceConfigurationId')]"
                    }
                },
                {
                    "type": "Microsoft.Network/privateEndpoints",
                    "apiVersion": "2020-06-01",
                    "name": "[parameters('privateEndpointName')]",
                    "location": "[parameters('location')]",
                    "dependsOn": [
                        "[resourceId('Microsoft.Sql/servers', parameters('serverName'))]"
                    ],
                    "tags": "[variables('tags')]",
                    "properties": {
                        "subnet": {
                            "id": "[resourceId(parameters('vnetRg'), 'Microsoft.Network/virtualNetworks/subnets', parameters('vnetName'), parameters('subnet1Name'))]"
                        },
                        "privateLinkServiceConnections": [
                            {
                                "name": "[parameters('privateEndpointName')]",
                                "properties": {
                                    "privateLinkServiceId": "[resourceId('Microsoft.Sql/servers',parameters('serverName'))]",
                                    "groupIds": [
                                        "sqlServer"
                                    ]
                                }
                            }
                        ]
                    }
                },
                {
                    "type":"Microsoft.Storage/storageAccounts/providers/roleAssignments",
                    "apiVersion":"2020-03-01-preview",
                    "name":"[concat(parameters('storageAccountName'), '/Microsoft.Authorization/', variables('uniqueRoleGuid'))]",
                    "dependsOn":[
                        "[resourceId('Microsoft.Sql/servers', parameters('serverName'))]"

                    ],
                    "properties":{
                        "roleDefinitionId":"[variables('StorageBlobContributor')]",
                        "principalId":"[reference(resourceId('Microsoft.Sql/servers', parameters('serverName')), '2020-08-01-preview', 'Full').identity.principalId]",
                        "scope":"[resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName'))]",
                        "principalType":"ServicePrincipal"
                    }
                },
                {
                    "name": "Default",
                    "type": "auditingSettings",
                    "apiVersion": "2021-02-01-preview",
                    "location": "[parameters('location')]",
                    "dependsOn": [
                        "[resourceId('Microsoft.Sql/servers', parameters('serverName'))]",
                        "[extensionResourceId(resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName')), 'Microsoft.Authorization/roleAssignments/', variables('uniqueRoleGuid'))]"

                    ],
                    "properties": {
                        "State": "Enabled",
                        "storageEndpoint":"[reference(resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName')), '2019-06-01').PrimaryEndpoints.Blob]",
                        "storageAccountAccessKey": "[listKeys(resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName')), '2019-06-01').keys[0].value]",
                        "retentionDays": 365,
                        "auditActionsAndGroups": [
                            "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP",
                            "FAILED_DATABASE_AUTHENTICATION_GROUP",
                            "BATCH_COMPLETED_GROUP"
                        ],
                        "storageAccountSubscriptionId": "[subscription().subscriptionId]",
                        "isStorageSecondaryKeyInUse": false,
                        "isAzureMonitorTargetEnabled": false
                    }
                },
                {
                    "name": "DefaultSAP",
                    "type": "securityAlertPolicies",
                    "apiVersion": "2017-03-01-preview",
                    "dependsOn": [
                        "[resourceId('Microsoft.Sql/servers', parameters('serverName'))]",
                        "[concat('Microsoft.Sql/servers/', parameters('serverName'), '/auditingSettings/Default')]"
                    ],
                    "properties": {
                        "state": "Enabled",
                        "disabledAlerts": null,
                        "emailAddresses": "[array(parameters('emailAddresses'))]",
                        "emailAccountAdmins": true,
                        "storageEndpoint": "[concat('https://',parameters('storageAccountName'),'.blob.core.windows.net')]",
                        "storageAccountAccessKey": "[listKeys(resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName')), providers('Microsoft.Storage', 'storageAccounts').apiVersions[0]).keys[0].value]",
                        "retentionDays": 365
                    }
                }
            ]
        },
        {
            "condition": "[equals(parameters('diagnosticSettingsEnabled'), true())]",
            "type": "Microsoft.Sql/servers/databases/providers/diagnosticsettings",
            "apiVersion": "2017-05-01-preview",
            "name": "[concat(variables('databaseName'), '/Microsoft.Insights/', parameters('serverName'), '-diagnosticSettings')]",
            "tags": "[variables('tags')]",
            "dependsOn": [
                "[resourceId('Microsoft.Sql/servers', parameters('serverName'))]"
            ],
            "properties": {
                "workspaceId": "[if(empty(parameters('workspaceName')), null(), variables('workspaceId'))]",
                "storageAccountId": "[if(empty(parameters('dsStorageAccountName')), null(), variables('dsStorageAccountId'))]",
                "serviceBusRuleId": "[if(empty(parameters('serviceBusRuleId')), null(), parameters('serviceBusRuleId'))]",
                "eventHubAuthorizationRuleId": "[if(empty(parameters('eventHubAuthorizationRuleId')), null(), parameters('eventHubAuthorizationRuleId'))]",
                "eventHubName": "[if(empty(parameters('eventHubName')), null(), parameters('eventHubName'))]",
                "logs": [
                    {
                        "category": "SQLInsights",
                        "enabled": true
                    },
                    {
                        "category": "AutomaticTuning",
                        "enabled": true
                    },
                    {
                        "category": "QueryStoreRuntimeStatistics",
                        "enabled": true
                    },
                    {
                        "category": "QueryStoreWaitStatistics",
                        "enabled": true
                    },
                    {
                        "category": "Errors",
                        "enabled": true
                    },
                    {
                        "category": "DatabaseWaitStatistics",
                        "enabled": true
                    },
                    {
                        "category": "Timeouts",
                        "enabled": true
                    },
                    {
                        "category": "Blocks",
                        "enabled": true
                    },
                    {
                        "category": "Deadlocks",
                        "enabled": true
                    }

                ],
                "metrics": [
                    {
                        "category": "Basic",
                        "enabled": true
                    },
                    {
                        "category": "InstanceAndAppAdvanced",
                        "enabled": true
                    },
                    {
                        "category": "WorkloadManagement",
                        "enabled": true
                    }

                ]
            }
        }
    ],
    "outputs": {
        "PvtendpointNicId": {
            "type": "string",
            "value": "[reference(resourceId('Microsoft.Network/privateEndpoints', concat(parameters('privateEndpointName'))), '2019-11-01').networkInterfaces[0].id]"
        }
    }
}