Azure-quickstart-templates: vulnerabilityAssessments failing within the SQL Server template

[andyofengland]

101-sql-logical-server
https://github.com/Azure/azure-quickstart-templates/blob/master/101-sql-logical-server/azuredeploy.json

Issue Details

When creating a SQL Server and adding the vulnerabilityAssessments element, the ARM execution reports an error:

New-AzResourceGroupDeployment : 09:33:08 - Resource Microsoft.Sql/servers/vulnerabilityAssessments 'resourceGroupName_rg/vulnerabilityAssessments' failed with message '{
  "error": {
    "code": "VulnerabilityAssessmentADSIsDisabled",
    "message": "Advanced Data Security should be enabled in order to use Vulnerability Assessment."
  }
}'

Another user has logged this on StackOverflow
https://stackoverflow.com/questions/57056770/vulnerability-assessment-enablement-on-azure-sql-server-through-arm-template

For completeness, the ARM code I'm using is:

{
          "name": "vulnerabilityAssessments",
          "type": "vulnerabilityAssessments",
          "apiVersion": "2018-06-01-preview",
          "dependsOn": [
            "[parameters('database_server_name')]",
            "[variables('advancedLoggingStorageName')]"
          ],
          "properties": {
            "storageContainerPath": "[concat(reference(resourceId('Microsoft.Storage/storageAccounts', variables('advancedLoggingStorageName'))).primaryEndpoints.blob, 'vulnerability-assessment')]",
            "storageAccountAccessKey": "[listKeys(resourceId('Microsoft.Storage/storageAccounts', variables('advancedLoggingStorageName')), '2018-02-01').keys[0].value]",
            "recurringScans": {
              "isEnabled": true,
              "emailSubscriptionAdmins": "true"
            }
          }
 }

[andyofengland]

As per the SO link, there seems to be a workaround - by moving the resource into a "top level" one rather than nesting it within the SQL Server creation block - although on my ARM template, I have tried this but am still getting the same error.

[luizreis]

I'm having the same issue. Currently - as a workaround - I'm triggering the vulnerabilityAssessments resource deployment to occur after creating a database, which seems to buy enough time for Advanced Data Security to be enabled in the server.

[andyofengland]

Curiously, I've tried the workaround and I'm still getting the same error

[talhers]

Hi,

The issue you are having is caused by deploying an ARM template with Vulnerability Assessment, but without enabling Advanced Data Security first.
You will have to add a dependency in the Vulnerability Assessment block, so it will only be deployed after Advanced Data Security is deployed.

This is a bug in the template that will be fixed soon.

In the meantime, here is the Vulnerability Assessment part with the fix:
(Added dependency for storage creation and ADS deployment)

          {
              "condition": "[parameters('enableADS')]",
              "type": "vulnerabilityAssessments",
              "name": "Default",
              "apiVersion": "2018-06-01-preview",
              "dependsOn": [
                  "[resourceId('Microsoft.Sql/servers/', parameters('serverName'))]",
                  "[concat('Microsoft.Storage/storageAccounts/', variables('storageName'))]",
                  "[concat('Microsoft.Sql/servers/', parameters('serverName'), '/securityAlertPolicies/Default')]"
              ],
              "properties": {
                  "storageContainerPath": "[if(parameters('enableADS'), concat(reference(resourceId('Microsoft.Storage/storageAccounts', variables('storageName')), '2018-07-01').primaryEndpoints.blob, 'vulnerability-assessment'), '')]",
                  "storageAccountAccessKey": "[if(parameters('enableADS'), listKeys(resourceId('Microsoft.Storage/storageAccounts', variables('storageName')), '2018-02-01').keys[0].value, '')]",
                  "recurringScans": {
                      "isEnabled": true,
                      "emailSubscriptionAdmins": true
                  }
              }
          },

[bergmeister]

@talhers Your fix does not seem to be enough, with it I get now the not very useful and not actionable (!) error message:

##[error]BadRequest: {
  "code": "BadRequest",
  "message": "An error occurred while processing this request.",
  "target": null,
  "details": [],
  "innererror": []
}

I don't know what you define as 'soon' but it's been 1.5 months since your comment! Such bugs can only occur due to a lack of an automated test process or maintenance.

[talhers]

Hi @bergmeister ,
You are right. This has been delayed. Thanks for bringing this back to our attention. We will investigate. I can't promise an ETA however.

[rwsanders]

I just tried the suggestion above, adding "[concat('Microsoft.Sql/servers/', parameters('serverName'), '/securityAlertPolicies/Default')]" to "dependsOn". This is working for me, maybe it has been patched in the last few days. Thanks!

[bergmeister]

The BadRequest error was due to something else on my side and adding the dependsOn works now for me as well. Still very frustrating to see such bugs and information-less error messages, which complicated this case unnecessarily.

[acarlstein]

The BadRequest error was due to something else on my side and adding the dependsOn works now for me as well. Still very frustrating to see such bugs and information-less error messages, which complicated this case unnecessarily.

I must second @bergmeister on this. It would be great to find a way to indicate Azure to provide a more verbose log when running into a problem when working with Blueprints and ARM Templates.