Deploy-Sql-vulnerabilityAssessments definition vulnerabilityAssessmentsEmail parameter type should be a list

[krowlandson]

---

Issue copied from: Azure/terraform-azurerm-caf-enterprise-scale/issues/130

Originally raised by: @evaldaseb

I believe this policy needs further investigation to ensure the evaluation criteria is being properly assessed when performing a compliance scan.

It would also be good to support multiple email addresses for the vulnerabilityAssessmentsEmail parameter, as supported by the emails attribute under the Microsoft.Sql/servers/databases/vulnerabilityAssessments resource type created by this policy.

---

Describe the bug

/modules/archetypes/lib/policydefinitions/policydefinitionesdeploysqlvulnerabilityassessments.json policy definition has a "vulnerabilityAssessmentsEmail" parameter:

    "parameters": {
      "vulnerabilityAssessmentsEmail": {
        "type": "String",
        "metadata": {
          "description": "The email address to send alerts",
          "displayName": "The email address to send alerts"
        }
      }

Created an assignment, specified parameters:

 "parameters": {
      "vulnerabilityAssessmentsEmail": {
        "value": "[email protected]"
      },
      "vulnerabilityAssessmentsStorageID": {
        "value": "storageaccountid"
      }
    },

and a remediation but the policy is still non-compliant.

Compliance details:

Current value ["[email protected]"] Target value "[email protected]"

It looks like it is expecting a list of strings not a single string.

Just to confirm this we can go to the Azure portal - subscription - resource group - SQL server blade - Security - Security Center - Azure Defender for SQL: Enabled at the subscription-level (Configure) - click on configure - hover with mouse over 'i' next to Send scan reports to:

expects a list.

If I specify my parameter in brackets:

parameters": {
      "vulnerabilityAssessmentsEmail": {
        "value": ["[email protected]"]
      }

executing code comes up with an error complaining about wrong parameter type.

[mrajess]

I have created PR #693 to resolve this issue.

However, I do feel like this Policy and control as a whole may need to be looked at further. In the UX for SQL DB you're actually setting vulnerability scanning settings at the server level, and not at the database level. All current documentation seems to indicate that this setting should be getting set at the server level now, but I'm unsure. Is setting this at DB level still the best practice? Additionally, should we expand this Policy to encompass Managed Instance as well?

[krowlandson]

Following extensive further testing, I've observed the following regarding this policy:

1. The template deployed by this policy appears to always get stuck in a loop with an InternalServerError response from the Microsoft.Sql resource provider:

{
    "status": "Failed",
    "error": {
        "code": "ServerClosedRequest",
        "message": "An internal server error was encountered while processing request for 'Microsoft.Sql'. Please try your request again later."
    }
}

2. Upon further investigation, this appears to be caused by the storage configuration settings but with no clear reason as to why.
3. Running a custom template deployment with the following settings succeeds:

{
    "$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {},
    "variables": {},
    "resources": [
        {
            "type": "Microsoft.Sql/servers/databases/vulnerabilityAssessments",
            "apiVersion": "2021-08-01-preview",
            "name": "sql-sec-test/sql-sec-test/default",
            "properties": {
                "recurringScans": {
                    "emails": [
                        "[email protected]",
                        "[email protected]"
                    ],
                    "emailSubscriptionAdmins": false,
                    "isEnabled": true
                }
            }
        }
    ],
    "outputs": {}
}

4. However, no changes are observed on the resource configuration:
5. The above continues to be the case regardless of API version used, or number of email addresses provided in the request. This has also been tested with valid (real) email addresses rather than the placeholders used in this comment.

Based on the above, it appears we are currently unable to implement these settings and will escalate via a support ticket to see whether a resolution can be provided.

[jtracey93]

Trigger ADO Sync 1

[jtracey93]

Trigger ADO Sync 2

[4ppli3d]

Pull request is in for this:

https://github.com/Azure/Enterprise-Scale/pull/1167

Additional notes in the PR for remediation and further action to be taken

[4ppli3d]

PR has been merged this can now be closed